Hello Friends,

We have shown you in the past how to write SQL using Jython to file , SQL in Operator and Rows counts into File and various other technique but some time we don’t want to send as an attachment but rather as a direct Email.

Here is a very simple Technique ,which can be done in one to two steps.

Step 1.  Java Codes

Command on Source – Database Technology and Schema

Command on Target , Technology- Java Bean Shell.

and paste the following codes and just change the Query  string , if required delimiter.

<@

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

conn=odiRef.getJDBCConnection("SRC");
Statement stmt=conn.createStatement();
String result="";

char delimiter=(char)44;
// Please change the delimiter here,i.e just change the number
// TAB - 9
// COMMA 44
// GET THE CODING LIST AT http://www.zytrax.com/tech/codes.htm

my_query="SELECT * FROM ODI_TEMP.PERIOD_YR ";

// Either provide the columns or select * from all columns

ResultSet rs=stmt.executeQuery(my_query);
ResultSetMetaData md=rs.getMetaData();
int numColumns =md.getColumnCount();

// Fetch column names
for (int i=1; i<numColumns+1; i++) {
 String columnName = md.getColumnName(i)+ delimiter;
 result+=columnName;
 }

result=result.substring(0,result.length()-1);
result+=(char)13;

int times=result.length();

for (int i=1; i<times ;i++){
 result+="-";
 }

result+=(char)13;

// Fetching Rows

result=result.substring(0,result.length()-1);
result+=(char)13;

while (rs.next()) {
 for (int i=1; i<numColumns+1; i++) {
 String  output=rs.getString(md.getColumnName(i))+ delimiter;
 result+=output;
 }
result=result.substring(0,result.length()-1);   
result+=(char)13;
 }

// Close Connection

stmt.close();
conn.close();
@>

Step 2.  Email using OdiSendMail

Command on Target  – Sunopsis API

and paste the following codes.

OdiSendMail -MAILHOST=<mail_host> -FROM=<from_user> -TO=<address_list>

<@=result@>

(or)

You can also call the OdiOutFile etc in the package too and in the message call <@=result@>, but make sure you call the above java procedure before it.

Simple and Easy .

Sample Output

Look for more tips and tricks from odiexperts.com 🙂 . Also keep looking for more ODI 11g   posts.