ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Send SQL by Email

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.

11 Comments

  1. I’m sorry but the step 1 is not clear enough. Could you update this entry with snapshots ?

    Thanks

  2. Very well, it´s works.

    • Hi Daniel,

      Did you use OdiSendMail with out authentication or Used any procedure to send email?

      Can you please share. I tried to send email using OdiSendMail. Unable to send. Kindly help.

  3. Ok guys, found a solution. Replace all the (char)13 in the script by (char)10. Worked for me 🙂

  4. Hello,

    Thanks for the great post.
    result’s value has no problem when used in OdiOutFile utility, however when I pass it as Mail Body it prints the first line only, discarding from the first (char)13 to onwards. I tried “\n” instead of (char)13 but it didn’t work either.

    I believe the problem is with OdiSendMail utility but could not come up with a soltuion yet.

    (We use ODI 10g)

    Best regards and thanks in advance,

    Coskan.

    • Hi,

      I am also having this problem. The email body contains only the column names and not the data. Please provide a solution. Thanks.

  5. Hi Kshitiz,
    Will this code work on odi11g + window 7 environment. I tried the same thing keeping Java bean shell as target and oracle as source technology, but its failing with this error:

    org.apache.bsf.BSFException: BeanShell script error: Sourced file: inline evaluation of: “conn=odiRef.getJDBCConnection(“SRC”); Statement stmt=conn.createStatement(); Str . . . ” : Typed variable declaration : Class: Statement not found in namespace : at Line: 2 : in file: inline evaluation of: “conn=odiRef.getJDBCConnection(“SRC”); Statement stmt=conn.createStatement(); Str . . . ” : Statement
    BSF info: writing a file at line: 0 column: columnNo
    at bsh.util.BeanShellBSFEngine.eval(Unknown Source)
    at bsh.util.BeanShellBSFEngine.exec(Unknown Source)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:345)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:169)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2374)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1615)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1580)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2755)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:322)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:246)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:237)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:794)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:619)

    Could you please give the screen shot for the same.
    Thanks

  6. Hi,
    Why doesn’t it print the result set in the body of the email??

    It just gives the column names…

  7. Hi,
    I tried your post on ODI 10.1.3.5.6 How ever the command on target i.e., the email part fails with the below exception Please give your thoughts
    com.sunopsis.sql.SnpsMissingParametersException: Missing parameter
    at com.sunopsis.sql.SnpsQuery.completeHostVariable(SnpsQuery.java)
    at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
    at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrdersIntegratedFunction(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
    at com.sunopsis.dwg.cmd.e.k(e.java)
    at com.sunopsis.dwg.cmd.h.A(h.java)
    at com.sunopsis.dwg.cmd.e.run(e.java)
    at java.lang.Thread.run(Thread.java:595)

  8. com.sunopsis.sql.SnpsMissingParametersException: Missing parameter
    at com.sunopsis.sql.SnpsQuery.completeHostVariable(SnpsQuery.java)
    at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
    at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrdersIntegratedFunction(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
    at com.sunopsis.dwg.cmd.e.k(e.java)
    at com.sunopsis.dwg.cmd.h.A(h.java)
    at com.sunopsis.dwg.cmd.e.run(e.java)
    at java.lang.Thread.run(Thread.java:595)

Leave a Reply

Required fields are marked *.