Send mail in ODI using Gmail Credentials

Send mail in ODI using Gmail Credentials:

Are you facing problem in sending mail from ODI ??? Don’t you have any dedicated mail host ?? Are you getting bunch of errors while using authenticated smtp server in ODI ?? Well no need to bother on this now. Just copy paste below codes in to your odi procedure and you are ready send a mail using gmail credentials. No need to put any specific jar files. I have given three possible solutions and its up to you to chose the suitable one for your requirement.

Updates for ODI 12C: Most of the people have found a default logical schema (ubnorc002) is causing error. This has been removed now. If you are using ODI12c then download from this link.

Solution 1 (for ODI 11g): Download the procedure (SSL & TLS) from this link and import it into your package. Provide the value as given in below image. (tested in windows 7)

Updates: Most of the people have found a default logical schema (ubnorc002) is causing error. This has been removed now.

Send mail in ODI using Gmail Credentials

Please ensure that when you are providing the folder path, it  shouldn’t contain space in it.

Solution 2: Using Jython
Here is the code

import smtplib
import string
BODY = string.join((
“From: %s” % ‘admin@odi.com’,
“To: %s” % ‘support@dwteam.in’,
“Subject: %s” % ‘Mail From ODI’ ,
“”,
‘This is a mail from ODi Studio. Thank You. keep visiting www.DwTeam.in’
), “\r\n”)
sender = smtplib.SMTP(‘smtp.gmail.com’,587)
sender.set_debuglevel(1)
sender.ehlo()
sender.starttls()
sender.ehlo()
sender.login(‘GMAIL_USER_NAME’, ‘PASSWORD’)
sender.sendmail(‘admin@odi.com’,[‘support@dwteam.in’],BODY)
sender.close()

 

Send mail in ODI using Gmail Credentials

Here is the mail

Send mail in ODI using Gmail Credentials
Send mail in ODI using Gmail Credentials

Solution 3: Using Jython Procedure

Below is the new codes with attachment facility. Dont forget to create the option in your procedure with text type. Provide the value for Filepath with forward slash and for Filename with extension.

You can download the procedure from here.

 

import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.MIMEBase import MIMEBase
from email import Encoders

USER='<%=snpRef.getOption(“User“)%>’
PASSWORD='<%=snpRef.getOption(“Password“)%>’
TO='<%=snpRef.getOption(“To“)%>’
SUBJECT ='<%=snpRef.getOption(“Subject“)%>’
FILEPATH='<%=snpRef.getOption(“Filepath“)%>’
FILENAME='<%=snpRef.getOption(“Filename“)%>’
FILEPATH=FILEPATH+”/”
FILEPATH=FILEPATH+FILENAME

msg = MIMEMultipart()
msg[‘Subject’] = SUBJECT
msg[‘To’] =TO

part = MIMEBase(‘application’, “octet-stream”)
part.set_payload(open(FILEPATH, “rb”).read())
Encoders.encode_base64(part)

part.add_header(‘Content-Disposition’, ‘attachment; filename=’+FILENAME)

msg.attach(part)

s = smtplib.SMTP()
s.connect(“smtp.gmail.com”,587)
s.set_debuglevel(1)
s.ehlo()
s.starttls()
s.ehlo()
s.login(USER,PASSWORD)
s.sendmail(USER,TO, msg.as_string())
s.close()

 

Send mail in ODI using Gmail Credentials

 

Enjoy!!! 😀

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

86 Comments

  1. Hi Bhabani,

    Thanks for the post on sendemail using gmail.
    Could you also post any content related to odireademail utility using gmail as pop3 server is possible.
    Thanks so much for all your efforts!

      • HI Bhabani, first thank you for your posting.
        Have you post something on odireadmail from (gmail / imap ssl protocol).

        I need to look into a mail an same attachments into a local path.

        Thank you

        • Hi Bruno,

          You can use python to read the mail instead relying on OdiTool. Here is the sample code to read the first mail in your inbox. Change the filter if you want to read specific one. Note I have put a raise at the end to print it on the operator.

          [code lang=”java”]
          import poplib
          import string, random
          import StringIO, rfc822
          import logging

          SERVER = “pop.gmail.com”
          USER = “XXXXXXXXXXXX@gmail.com”
          PASSWORD = “XXXXXXXXXXXXXXXXXXXXXXXXXXX”

          # connect to server
          logging.debug(‘connecting to ‘ + SERVER)
          server = poplib.POP3_SSL(SERVER)
          #server = poplib.POP3(SERVER)

          # login
          logging.debug(‘logging in’)
          server.user(USER)
          server.pass_(PASSWORD)

          # list items on server
          logging.debug(‘listing emails’)
          resp, items, octets = server.list()

          # download the first message in the list
          id, size = string.split(items[0])
          resp, text, octets = server.retr(id)

          # convert list to Message object
          text = string.join(text, “\n”)
          file = StringIO.StringIO(text)
          message = rfc822.Message(file)

          # output message
          print(message[‘From’]),
          print(message[‘Subject’]),
          print(message[‘Date’]),
          raise(message.fp.read())
          [/code]

          Thanks
          Bhabani

          • Thank you a lot. I have problem. I must use IMAP module over ssl.
            I try to update your code but I have a connection issue.
            Let’s start with the basic

            import imaplib
            mail = imaplib.IMAP4_SSL(‘xxxxx)
            mail.login(”,”)

            after execution I have Caused by: Traceback (most recent call last):
            File “”, line 5, in
            File “E:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\imaplib.py”, line 1128, in __init__
            IMAP4.__init__(self, host, port)
            File “E:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\imaplib.py”, line 163, in __init__
            self.open(host, port)
            File “E:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\imaplib.py”, line 1141, in open
            self.sslobj = socket.ssl(self.sock, self.keyfile, self.certfile)
            File “E:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\socket.py”, line 1479, in ssl
            return _realssl(sock, keyfile, certfile)
            File “E:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\socket.py”, line 1428, in __init__
            raise _map_exception(jlx)
            socket.sslerror: (-1, ‘SSL handshake exception’)

          • Try this one.

            [code lang=”java”]
            import email
            import imaplib
            import re
            mail = imaplib.IMAP4_SSL(‘imap.gmail.com’)
            mail.login(‘xxxxxxxxxxxxx@gmail.com’, ‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxx’)
            mail.list()

            mail.select(“inbox”) # connect to inbox.

            result, data = mail.search(None, “ALL”)

            ids = data[0]
            id_list = ids.split()
            latest_email_id = id_list[-1] # get the latest

            result, data = mail.fetch(latest_email_id, “(RFC822)”)

            raw_email = data[0][1]
            email_message = email.message_from_string(raw_email)

            print email_message[‘To’]
            print email.utils.parseaddr(email_message[‘From’])
            print email_message.items() # print all headers
            raise(email_message[‘To’])
            [/code]

  2. Hi Bhabani,

    Thank you for your post but i am not able to use this and the error i am encountering is.

    org.apache.bsf.BSFException: exception from Jython:
    SyntaxError: (“no viable alternative at character ‘\x93′”, (”, 4, 0, ‘\x93From: %s\x94 % \x91odiadmin@odi.com\x92,\n’))

    and the code i have written in procedure is

    import smtplib
    import string
    BODY = string.join((
    “From: %s” % ‘odiadmin@odi.com’,
    “To: %s” % ‘dmiles@gmail.com’,
    “Subject: %s” % ‘test’ ,
    “”,
    ‘dfdsfdsfdsfdsfds’
    ), “\r\n”)
    sender = smtplib.SMTP(‘smtp.gmail.com’)
    sender.set_debuglevel(1)
    sender.ehlo()
    sender.starttls()
    sender.ehlo()
    sender.login(‘pavankumar@gmail.com’, ‘@india@india’)
    sender.sendmail(‘odiadmin@odi.com’,[‘dmiles@gmail.com’],BODY)
    sender.close()

    please correct me if i am missing any and let me know the issue iam lagging with..

    Thanks in advance
    Pavan Kumar

    • The problem is with single quote. When you are copying the code its changing the actual single quote with other format. So the resolution is after copying the above code , please remove the single quote and provide single quote from your keyboard.
      Thanks

  3. HI
    Thanks for your reply and this is the error i am encountering now after the quotes are changed as per your suggestion

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
    File “”, line 10, in
    File “C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 244, in __init__
    (code, msg) = self.connect(host, port)
    File “C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 310, in connect
    raise socket.error, msg
    socket.error: (62, ‘Connection refused’)

    Thanks
    pavan kumar

    • Please go to this location C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib and open smtplib.py in wordpad. Let me know the default port number you have. Is it 25 ? If yes then are you able to open gmail in any browser in that system ??

      • HI

        i have checked in the respective file and the port number by default is 25 but fyi i cannot open any gmail account from this system.

        Thanks
        pavan kUmar

        • Hi Pavan,
          It means there is some restriction to access gmail in your network..Please communicate with your n/w administrator. There might some proxy setting or some restriction to access gmail..This generally happens in office premises and all social networking sites/gmail/yahoomail etc are blocked.

          • Thank you so much for your reply. I’ve updated my code and now I’m facing the same issue as Pavan. I have checked the port number in the script file and it is 25. I can access gmail from my office location. So, I’m not sure what is causing the problem. I will have to try this at home.And, I will let you know if this works.

          • Hi,
            I am facing the same issue.
            Although there is no restriction to access gmail,I am getting above error.

            Please help.

  4. Hi, this is not working for me. I replaced single quotes and double quotes with what I have from my keyboard. Here’s the error which I’m receiving. Thank you!:

    ODI-1226: Step send_mail_gmail fails after 1 attempt(s).
    ODI-1232: Procedure send_mail_gmail execution fails.
    Caused By: org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
    File “”, line 1, in
    NameError: name ‘string’ is not defined

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: Traceback (most recent call last):
    File “”, line 1, in
    NameError: name ‘string’ is not defined

    at org.python.core.PyException.fillInStackTrace(PyException.java:70)
    at java.lang.Throwable.(Throwable.java:181)
    at java.lang.Exception.(Exception.java:29)
    at java.lang.RuntimeException.(RuntimeException.java:32)
    at org.python.core.PyException.(PyException.java:46)
    at org.python.core.PyException.(PyException.java:43)
    at org.python.core.PyException.(PyException.java:61)
    at org.python.core.Py.NameError(Py.java:220)
    at org.python.core.PyFrame.getname(PyFrame.java:276)
    at org.python.pycode._pyx2.f$0(:15)
    at org.python.pycode._pyx2.call_function()
    at org.python.core.PyTableCode.call(PyTableCode.java:165)
    at org.python.core.PyCode.call(PyCode.java:18)
    at org.python.core.Py.runCode(Py.java:1204)
    at org.python.core.Py.exec(Py.java:1248)
    at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
    … 19 more

  5. Hi Bhabani,

    Thanks for the post on sendemail

    I am trying to send email using ODI
    I have found in oracle support that if we configure TRT_Send_Jython_HTML_Email.xml procedure we can send an email
    I tried doing so, but even though I select the LoadFromFile as no , I am getting the following error

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File “”, line 1, in ?
    File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 483, in sendmail
    File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 352, in rset
    File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 294, in docmd
    File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 271, in getreply
    SMTPServerDisconnected: Connection unexpectedly closed

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    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

    Can anyone pls tell me how to resolve this problem.

    Thanks
    Ashvini

      • Bhabani, could you please help me out with the below error on jython code for sending email with authentication.

        File “”, line 1, in ?
        File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 483, in sendmail
        File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 352, in rset
        File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 294, in docmd
        File “C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\..\..\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 271, in getreply
        SMTPServerDisconnected: Connection unexpectedly closed

          • Bhabani, Please find the Python code that I’ve used for sending mails within ODI. this code works but could you please let me know if this is supported by Oracle, if not, what are the other options that Oracle supports other than ODISENDMAIL. Our server needs authentication. Thanks!

            import smtplib, os
            from email.MIMEMultipart import MIMEMultipart
            from email.MIMEBase import MIMEBase
            from email.MIMEText import MIMEText
            from email.Utils import COMMASPACE, formatdate
            from email import Encoders

            FROM = ‘gig@bcg.com’
            TO = ‘gig@abd.com’# must be a list
            SUBJECT = “xxxx failure”
            TEXT = “This is an automated message; please do not reply to this message.”
            SERVER = “smtp1.xxx”
            PORT = xxx
            USERNAME=”abd_cheuan”
            PASSWORD= “xxxxxxx”
            message = MIMEMultipart()
            message[‘Subject’] = SUBJECT
            message[‘From’] = FROM
            message[‘To’] =TO
            message.attach( MIMEText(TEXT) )
            part = MIMEBase(‘application’, “octet-stream”)
            part.set_payload(open(“//xxxx/oracle/ODI_Files/ODI_Errors/Sample.txt”,”rb”).read())
            Encoders.encode_base64(part)
            part.add_header(‘Content-Disposition’, ‘attachment; filename= “//xxxx/oracle/ODI_Files/ODI_Errors/Sample.txt”‘)
            message.attach(part)

            s = smtplib.SMTP(SERVER,PORT)
            s.login(USERNAME, PASSWORD)
            s.sendmail( FROM,TO,message.as_string())
            s.quit()

  6. Hello Babani,

    Thank you very much for your post,I see the below error in my ODI.Can you please help me debugging this error.

    rg.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File “”, line 12, in ?
    File “/u01/app/oracle/odi1013/oracledi/lib/scripting/Lib/smtplib.py”,
    line 187, in __init__
    File “/u01/app/oracle/odi1013/oracledi/lib/scripting/Lib/smtplib.py”,
    line 225, in connect
    File “/u01/app/oracle/odi1013/oracledi/lib/scripting/Lib/socket.py”,
    line 135, in connect
    java.net.ConnectException: Connection timed out

  7. Hi Bhabani, Thanks for the post. Its working fine for gmail but not in yahoo mail. Can you suggest how to send mail using yahoo id?

    Thanks
    Lotek

  8. Hi Bhabani

    I got the following error
    org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
    File “”, line 34, in
    File “D:\app\obiee11g\Oracle_ODI1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 554, in login
    raise SMTPException(“SMTP AUTH extension not supported by server.”)
    smtplib.SMTPException: SMTP AUTH extension not supported by server.

    Kindly note that SMTP_PORT is 25

        • when I tried to execute the 1st one I got the below error:

          ODI-1222: Session start failure on agent Internal: logical schema ubnorc002 cannot be found in the master repository.
          at com.sunopsis.dwg.dbobj.SnpSessStep.getSrcDataServer(SnpSessStep.java:1422)
          at com.sunopsis.dwg.dbobj.SnpSessStep.createTaskLogs(SnpSessStep.java:674)
          at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:461)
          at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
          at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
          at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
          at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
          at java.lang.Thread.run(Thread.java:662)

          • Ohh,Please open the procedure and go to second step and third step.
            Then go to command on source and select the technology as Undefined.
            Save it. Now it should run if you have proper credentials.

  9. Dear ,

    1st and 2nd procedure are working successfully.

    I need to send and attachment and I have executed the 4th one “Jython Procedure” I got the error :
    org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
    File “”, line 34, in
    File “D:\app\obiee11g\Oracle_ODI1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 554, in login
    raise SMTPException(“SMTP AUTH extension not supported by server.”)
    smtplib.SMTPException: SMTP AUTH extension not supported by server.

    The generated code by Operator is:
    import smtplib
    from email.MIMEMultipart import MIMEMultipart
    from email.MIMEText import MIMEText
    from email.MIMEBase import MIMEBase
    from email import Encoders

    USER=’aahamdan1983@gmail.com’
    PASSWORD=’MyPassword’
    TO=’ahamdan@ats-ware.com’
    SUBJECT =’test’
    FILEPATH=’C:/PARAM’
    FILENAME=’test.txt’
    FILEPATH=FILEPATH+”/”
    FILEPATH=FILEPATH+FILENAME

    msg = MIMEMultipart()
    msg[‘Subject’] = SUBJECT
    msg[‘To’] =TO

    part = MIMEBase(‘application’, “octet-stream”)
    part.set_payload(open(FILEPATH, “rb”).read())
    Encoders.encode_base64(part)

    part.add_header(‘Content-Disposition’, ‘attachment; filename=’+FILENAME)

    msg.attach(part)

    s = smtplib.SMTP()
    s.connect(“smtp.gmail.com”)
    s.set_debuglevel(1)
    s.ehlo()
    s.starttls()
    s.ehlo()
    s.login(USER,PASSWORD)
    s.sendmail(USER,”bhabaniranjan@gmail.com”, msg.as_string())
    s.close()

  10. Am facing some error using solution3

    SyntaxError: (“mismatched input ” expecting EOF”, (”, 10, 1, ” sender = smtplib.SMTP(‘smtp.gmail.com’, 587)\n”))

    here is my code

    import smtplib
    import string
    BODY = string.join((
    “From: %s” % ‘xyz@gmail.com’,
    “To: %s” % ‘xyz@gmail.com’,
    “Subject: %s” % ‘test’ ,
    “”,
    ‘dfdsfdsfdsfdsfds’
    ), “\r\n”)
    sender = smtplib.SMTP(‘smtp.gmail.com’, 587)
    sender.set_debuglevel(1)
    sender.ehlo()
    sender.starttls()
    sender.ehlo()
    sender.login(‘xyz@gmail.com’, ‘xyz’)
    sender.sendmail(‘xyz@gmail.com’,[‘xyz@gmail.com’],BODY)
    sender.close()

    • Please download the procedure from above link ( without attachement )and if you want to send attachment then let me know i will send the latest procedure to your email.

      • Please do send me 3rd and 4th latest procedures to my email mentioned.

        Am facing ‘C:\Program’ is not recognized as an internal or external command,
        operable program or batch file. while using 1st and 2nd solutions

  11. I followed your link. It is working fine for gmail. What about configuring the same for my official id.Can you guide in this regard?

    • Do you have a mail server in your office which does not requires an authentication. If yes use odi sendmail tool available in ODI. If not then try to find out whether any firewall is blocking the specific port or not.

      • Hi,
        I tried to get the SMTP IP by:
        ping mail.xyz.com

        Is it the right way??

        Then I mentioned the IP address in the below code:

        OdiSendMail “-MAILHOST=IP address of SMTP” “-FROM=pmt@xyz.com” “-SUBJECT=ODI: Failure notification” “-TO=pmt@xyz.com”

        When I execute, it is giving the following error.

        javax.mail.MessagingException: Could not connect to SMTP host: 1.2.3.4, port: 25;
        nested exception is:
        java.net.SocketException: Permission denied: connect
        at com.sun.mail.smtp.SMTPTransport.openServer(SMTPTransport.java:1282)
        at com.sun.mail.smtp.SMTPTransport.protocolConnect(SMTPTransport.java:370)
        at javax.mail.Service.connect(Service.java:275)
        at javax.mail.Service.connect(Service.java:156)
        at javax.mail.Service.connect(Service.java:105)
        at javax.mail.Transport.send0(Transport.java:168)
        at javax.mail.Transport.send(Transport.java:98)
        at com.sunopsis.dwg.tools.SendMail.actionExecute(SendMail.java:172)
        at com.sunopsis.dwg.function.SnpsFunctionBase.execute(SnpsFunctionBase.java:276)
        at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:3437)
        at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeOdiCommand(SnpSessTaskSql.java:1509)
        at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:44)
        at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:1)
        at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
        at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
        at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
        at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
        at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
        at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
        at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
        at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
        at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
        at java.lang.Thread.run(Thread.java:722)
        Caused by: java.net.SocketException: Permission denied: connect
        at java.net.DualStackPlainSocketImpl.connect0(Native Method)
        at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:69)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:337)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:198)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:180)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:157)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:391)
        at java.net.Socket.connect(Socket.java:579)
        at java.net.Socket.connect(Socket.java:528)
        at com.sun.mail.util.SocketFetcher.createSocket(SocketFetcher.java:232)
        at com.sun.mail.util.SocketFetcher.getSocket(SocketFetcher.java:189)
        at com.sun.mail.smtp.SMTPTransport.openServer(SMTPTransport.java:1250)
        … 26 more

        • Please ask your network administrator on this. let him know about this error ” Could not connect to SMTP host: 1.2.3.4, port: 25;”. There might be some firewall issues or something for which you are not able to connect to it.

          • Thank you for your response.Yes, they have set certain restrictions.
            Will provide me with the access later.
            Can I use OdiSendMail for gmail also??

          • No Poonam. You cannot do that. OdiSendMail will work if the mail server doesnot require authentication. You can download the codes i have given in my post to send mail using gmail. let me know if you face any issues.

  12. I have done it with the procedures specified by you. It worked successfully. No issues.

    I will have to ask n/w administrator to provide with a mail server with no authentication required.
    Then only it will work for OdiSendMail.

    Thanks.

      • Hey thanks a ton for all your blogs. It has helped a lot for a newbie like me to explore ODI more and more.
        Keep up the good work!!!!

        • Hi Bhabani,
          I am getting the bellow error while using the 3rd solution that you included above, can you please help?:
          org.apache.bsf.BSFException: exception from Jython:
          Traceback (most recent call last):
          File “”, line 4, in
          File “D:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 244, in __init__
          (code, msg) = self.connect(host, port)
          File “D:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 310, in connect
          raise socket.error, msg
          socket.error: (62, ‘Connection refused’)

          at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
          at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
          at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
          at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
          at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
          at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
          at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
          at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
          at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
          at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
          at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
          at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
          at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
          at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
          at java.lang.Thread.run(Thread.java:662)

          • Dear Pierre,
            You might have some restriction to access gmail in your network or might be some firewall issue with the port number. Just verify it and let me know if everything fine from your side.

            Thanks
            Bhabani

          • Hi Bhabani,

            In fact it works now. What i did is that i changed the mail server address into the proxy server address. That was the cause of the ‘connection refused’ error.

            Thanks a lot

          • Hi Pierre,
            Could you please elaborate where exactly you made the changes.
            I am getting the same error and unable to resolve.

            Thanks in advance

  13. I imported the procedure. I added the Command 0 as the first step. I am getting this error. What am I missing?

    ODI-1222: Session start failure on agent Internal: logical schema ubnorc002 cannot be found in the master repository.
    at com.sunopsis.dwg.dbobj.SnpSessStep.getSrcDataServer(SnpSessStep.java:1448)
    at com.sunopsis.dwg.dbobj.SnpSessStep.createTaskLogs(SnpSessStep.java:700)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:465)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:662)

    • Hi Dilan, please open the procedure in ODI. Go to command on source in second step and select the technology as undefined. Do the same for third step. save and execute the package. You are now good go.
      Thanks.

  14. Bhabani. I attempted the fix and it did work although I also had to remove the Command 0 line. It wasn’t working with that line in place. I also had to modify the TLS create java code it was missing these 2 lines.

    props.put(“mail.smtp.socketFactory.port”, “587”);
    props.put(“mail.smtp.socketFactory.class”,”javax.net.tls.TLSSocketFactory”);

    Once I added those it was working like a charm.

  15. Not able to get solution 1 (SSL) work in ODI12c, keep getting error even changed source command technology to Any Technology for step 2 and 3. any one tried this in 12c?
    ODI-1222: Session start failure on agent Internal: logical schema ubnorc002 cannot be found in the master repository.
    at oracle.odi.runtime.agent.execution.AbstractSessionTask.getSrcDataServer(AbstractSessionTask.java:1652)
    at oracle.odi.runtime.agent.execution.interpreter.SessionTaskCodeInterpreter.codeInterpretation(SessionTaskCodeInterpreter.java:132)
    at oracle.odi.runtime.agent.execution.interpreter.SessionTaskCodeInterpreter.codeInterpretation(SessionTaskCodeInterpreter.java:80)
    at oracle.odi.runtime.agent.execution.SessionTask.createTaskLog(SessionTask.java:348)
    at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:813)
    at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2004)
    at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:544)
    at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:709)
    at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:624)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
    at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:789)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:330)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$2700(StartSessRequestProcessor.java:80)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:957)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:177)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:110)
    at java.lang.Thread.run(Thread.java:744)

    Jython solution does not work either ,get following message
    File “C:\Oracle\ODI12c\odi\sdk\lib\Lib\socket.py”, line 583, in _realsocket
    assert family == AF_INET, “Only AF_INET sockets are currently supported on jython”
    AssertionError: Only AF_INET sockets are currently supported on jython

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:357)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:173)

  16. ‘C:\Program’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘C:\Program’ is not recognized as an internal or external command,
    operable program or batch file.
    ‘C:\Program’ is not recognized as an internal or external command,
    operable program or batch file.

    Hi I’m using option 1 : I am getting the error above. Can you please help? thanks,

    • Do not use spaces in your path. For example “Program Files”. This will give you error. Instead you can use underscore in your path name. Also use forward slash to separate the directories.

  17. Hi,Bhabani
    I download the procedure and import them into package.When I execute one of them,there is an error.Besides username and password,no information is modified.Please tell me how to handle the question.Thanks.
    Error details;
    com.sunopsis.dwg.function.SnpsFunctionBaseException: ODI-30038: OS command returned 1。
    at com.sunopsis.dwg.tools.OSCommand.actionExecute(OSCommand.java:294)
    at com.sunopsis.dwg.function.SnpsFunctionBase.execute(SnpsFunctionBase.java:276)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:3437)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeOdiCommand(SnpSessTaskSql.java:1509)
    at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:44)
    at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:662)

      • Dear Bhabani,

        I have tried to implement all the solution given by you,but I am unable to get the options param and value as shown in the image.
        Getting errors if I hard code the “username” and “password” and etc.

        please help me with this issue.

        Thanks In Advance
        Raushan sinha

        • I am not sure what do you mean by not getting option params. Are you able to open properties tab? Can you give a screenshot of how it looks like in your studio?

          • Dear Bhabani,
            Thank you for the quick response,I found the solution for setting up option but I am getting this error even after removing the single quotes and inserting it through keyboard which you have suggested in the comments.

            ERROR:

            ODI-1217: Session send_email (66001) fails with return code 7000.
            ODI-1226: Step send_email fails after 1 attempt(s).
            ODI-1232: Procedure send_email execution fails.
            Caused By: org.apache.bsf.BSFException: exception from Jython:
            SyntaxError: (“no viable alternative at character ‘\xe2′”, (”, 23, 10, ‘s.connect(\xe2\x80\x9csmtp.gmail.com\xe2\x80\x9d,587)\n’))

            at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
            at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
            at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
            at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
            at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
            at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
            at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
            at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
            at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
            at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
            at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
            at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
            at java.lang.Thread.run(Thread.java:745)
            Caused by: SyntaxError: (“no viable alternative at character ‘\xe2′”, (”, 23, 10, ‘s.connect(\xe2\x80\x9csmtp.gmail.com\xe2\x80\x9d,587)\n’))

            at org.python.core.ParserFacade.fixParseError(ParserFacade.java:96)
            at org.python.core.ParserFacade.parse(ParserFacade.java:203)
            at org.python.core.Py.compile_flags(Py.java:1685)
            at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
            at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
            … 19 more

            Thanks In Advance
            Raushan sinha

          • Hi, Download the procedure from the link that I have given in solution 1. Then import it into your project.
            Drag the procedure to the package canvas and give the values as per your system.

  18. Dear Bhabani,
    I got your mail,please resolve the issue which I have posted in the OTN.

    Waiting for your response
    Raushan Sinha

    • Download the zip file. Extract it. Then expand your project. Right click on procedure and and click on import procedure. Select the mode as duplicate.
      Now drag that procedure into your package. Click on option in the properties tab.

  19. Dear Bhabani,

    I very new to ODI, I want send an email after successful completion of mapping. Please send the last code for this task.

    Thanks in advance…

    regards,
    Satish

  20. Hi Bhabani,

    I am getting the below error while to execute your code:

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
    File “”, line 12, in
    File “__pyclasspath__/smtplib.py”, line 605, in starttls
    File “__pyclasspath__/socket.py”, line 1479, in ssl
    File “__pyclasspath__/socket.py”, line 1428, in __init__
    socket.sslerror: (-1, ‘SSL handshake exception’)

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:580)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: Traceback (most recent call last):
    File “”, line 12, in
    File “__pyclasspath__/smtplib.py”, line 605, in starttls
    File “__pyclasspath__/socket.py”, line 1479, in ssl
    File “__pyclasspath__/socket.py”, line 1428, in __init__
    socket.sslerror: (-1, ‘SSL handshake exception’)

    at org.python.core.PyException.fillInStackTrace(PyException.java:70)
    at java.lang.Throwable.(Throwable.java:181)
    at java.lang.Exception.(Exception.java:29)
    at java.lang.RuntimeException.(RuntimeException.java:32)
    at org.python.core.PyException.(PyException.java:46)
    at org.python.core.PyException.doRaise(PyException.java:219)
    at org.python.core.Py.makeException(Py.java:1166)
    at org.python.core.Py.makeException(Py.java:1170)
    at org.python.core.Py.makeException(Py.java:1174)
    at socket$py.__init__$157(__pyclasspath__/socket.py:1428)
    at socket$py.call_function(__pyclasspath__/socket.py)
    at org.python.core.PyTableCode.call(PyTableCode.java:165)
    at org.python.core.PyBaseCode.call(PyBaseCode.java:297)
    at org.python.core.PyBaseCode.call(PyBaseCode.java:191)
    at org.python.core.PyFunction.__call__(PyFunction.java:385)
    at org.python.core.PyFunction.__call__(PyFunction.java:380)
    at org.python.core.PyInstance.__init__(PyInstance.java:120)
    at org.python.core.PyClass.__call__(PyClass.java:194)
    at org.python.core.PyObject.__call__(PyObject.java:432)
    at org.python.core.PyObject.__call__(PyObject.java:436)
    at socket$py.ssl$164(__pyclasspath__/socket.py:1479)
    at socket$py.call_function(__pyclasspath__/socket.py)
    at org.python.core.PyTableCode.call(PyTableCode.java:165)
    at org.python.core.PyBaseCode.call(PyBaseCode.java:163)
    at org.python.core.PyFunction.__call__(PyFunction.java:338)
    at smtplib$py.starttls$45(__pyclasspath__/smtplib.py:616)
    at smtplib$py.call_function(__pyclasspath__/smtplib.py)
    at org.python.core.PyTableCode.call(PyTableCode.java:165)
    at org.python.core.PyBaseCode.call(PyBaseCode.java:297)
    at org.python.core.PyBaseCode.call(PyBaseCode.java:191)
    at org.python.core.PyFunction.__call__(PyFunction.java:385)
    at org.python.core.PyMethod.__call__(PyMethod.java:215)
    at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)
    at org.python.core.PyMethod.__call__(PyMethod.java:206)
    at org.python.core.PyObject.__call__(PyObject.java:381)
    at org.python.core.PyObject.__call__(PyObject.java:385)
    at org.python.pycode._pyx3039.f$0(:16)
    at org.python.pycode._pyx3039.call_function()
    at org.python.core.PyTableCode.call(PyTableCode.java:165)
    at org.python.core.PyCode.call(PyCode.java:18)
    at org.python.core.Py.runCode(Py.java:1204)
    at org.python.core.Py.exec(Py.java:1248)
    at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    … 1 more

    Please advice.

    Regards,
    Naveen

  21. Hi, Is that possible to hide the text which we enter in password? Do we have any option in ODI to set it as a password field? Advance thanks.

  22. Hi,

    I am getting connection refused error in ODI after i am using first Jython code,
    SMTP port : 25 and able to access gmail through browser.
    File “C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 244, in __init__
    (code, msg) = self.connect(host, port)
    File “C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\jdev\extensions\oracle.odi.navigator\scripting\Lib\smtplib.py”, line 310, in connect
    raise socket.error, msg
    socket.error: (62, ‘Connection refused’)

  23. Is it possible to pass mutilple sender email address in the “To” List?

    it works like a charm with single email in the “To”. How to send it to multiple recipients.

    Please advice

Leave a Reply

Your email address will not be published.


*