Display contents of Oracle table into HTML format mail using tSendmail

Highlighted
Five Stars

Display contents of Oracle table into HTML format mail using tSendmail

Hi,

 

I have been trying with the below approach but not getting the data in proper email format . Could you please help !!

 

Design.JPG

 

tOracleDBInput:

SELECT
'<tr><td>'||TABLE_NAME.KEY_NUMBER||'</td>'
||'<td>'||TABLE_NAME.TBL_NAME||'</td>'
||'<td>'||TABLE_NAME.SCHEMA_1_COUNT||'</td>'
||'<td>'||TABLE_NAME.SCHEMA_2_COUNT||'</td>'
||'<td>'||TABLE_NAME.COUNT_DIFFERENCE||'</td>'
||'<td>'||TABLE_NAME.UPDATED_TIME||'</td></tr>'
FROM TABLE_NAME;

 

tJavaRow:

context.email = context.email + "<tr><td>" + input_row.KEY_NUMBER + "</td><td>" + input_row.TBL_NAME + "</td><td>" + input_row.SCHEMA_1_COUNT+ "</td><td>" + input_row.SHEMA_2_COUNT + "</td><td>" + input_row.COUNT_DIFFERENCE + "</td><td>" + input_row.UPDATED_TIME+"" + "</td></tr>" ;

 

tSendmail:

 

"<head><meta http-equiv=Content-Type content=text/html;
charset=iso-8859-15><style type=text/css>table,TD, TH{
white-space: nowrap;
border:1px solid CornflowerGreen ;
font-size:12px;
font-family: Arial;
}
th{
white-space:nowrap;
background-color:CornflowerGreen;
color:Black;
}
<style><head>Hi Team<p><table><th>KEY_NUMBER</th><th>
<TBL_NAME/th><th>SCHEMA_1_COUNT</th><th>SCHEMA_2_COUNT</th><th>COUNT_DIFFERENCE</th><th>UPDATED_TIME</th><tr>"+context.email+"</tr></table>"

 

I am getting only the Headers in the table. Could anyone guide me to get the table data as well.

 

Email Format:

HTML_Mail.JPG

Table Format

tABLE_fORMAT.JPG

I will have more than 50 rows in table and the count will vary. What would be the best possible solution to get the data in HTML format thru tSendmail ? 

Ten Stars

Re: Display contents of Oracle table into HTML format mail using tSendmail

Im not entirely sure on the tSendmail component... it clearly reads the body as an entire string or it auto-escape the string ... so maybe escape quotes using multiple double quotes or slashes to force/trick it ... look at the code view in stead of design view to see whats going on (select the tSendMail componet first before changing the view) ?

Also Suggest use a global var and maybe a tJavaFlex where you set a global var in the 'post/after' section in the component: globalMap.put("tableBody", tableRows).
in the Main section you build up your rows in var tableRows.
in the tSendMail get it like : (String)globalMap.get("tableBody")
Five Stars

Re: Display contents of Oracle table into HTML format mail using tSendmail

Hi, you need to use tFlowtoIterate before tJava_row. For all record in input you need to iterate and write the current record into you context.message. i Hope this can help you.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch