Call ODI Scenario from PLSQL

Call ODI Scenario from PLSQL:

I was discussing with one of my friend on this without calling a batch file from PLSQL. Since webservice is a best method to invoke odi scenario outside odistudio, lets use the soap envelop to kick the scenario from plsql program. Here is the code.

DECLARE

 req_envelop VARCHAR2(4000) :=  ‘<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:odi=”xmlns.oracle.com/odi/OdiInvoke/“>
<soapenv:Header/>
<soapenv:Body>
<odi:OdiStartScenRequest>
<Credentials>
<OdiUser>SUPERVISOR</OdiUser>
<OdiPassword>SUNOPSIS</OdiPassword>
<WorkRepository>WORKREP91</WorkRepository>
</Credentials>
<Request>
<ScenarioName>PKG_SCOTT</ScenarioName>
<ScenarioVersion>001</ScenarioVersion>
<Context>GLOBAL</Context>
<!–Optional:–>
<Synchronous>false</Synchronous>
<!–Optional:–>
<LogLevel>5</LogLevel>
</Request>
</odi:OdiStartScenRequest>
</soapenv:Body>
</soapenv:Envelope>’;
req utl_http.req;
res utl_http.resp;
res_envelop VARCHAR2(32767);
BEGIN
req := utl_http.begin_request(‘http://localhost:20910/oraclediagent/OdiInvoke?wsdl‘,‘POST’,’HTTP/1.1′);
utl_http.set_header(req, ‘Content-Type’, ‘text/xml’);
utl_http.set_header(req, ‘Content-Length’, length(req_envelop));
utl_http.set_header(req, ‘SOAPAction’, ‘invokeStartScen’);
utl_http.write_text(req, req_envelop);
res := utl_http.get_response(req);
utl_http.read_text(res, res_envelop);
utl_http.end_response(res);
dbms_output.put_line(res_envelop);
END;
 Now execute above codes with proper parameters and you will find out the response as given below.
<?xml version=”1.0″ ?>
 <S:Envelope xmlns:S=”http://schemas.xmlsoap.org/soap/envelope/“>
   <S:Body>
     <ns2:OdiStartScenResponse xmlns:ns2=”xmlns.oracle.com/odi/OdiInvoke/“>
       <Session>2518091</Session>
    </ns2:OdiStartScenResponse>
  </S:Body>
 </S:Envelope>
Now Its Done. Check your operator for the session 2518091.
See You in the next post.
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.

16 Comments

  1. Hi Bhabani,

    Very nice simple solution. I tried this. I am using ODI 11.1.1.5. Problem is: ODI webservice accepts only one parameter. If I pass more than one it takes in consideration only last one. Is there some workaround for that?

    Best regards,
    Ed

      • Thanks for the post.
        The above code is working fine. need some suggestions on the below points.
        1 : How to capture the ODI hard fails in PL/SQL procedure ?
        2 : How to capture the ODI logs or sequence of steps in PL/SQL procedure ?

  2. i tried executing this procedure with necessary parameter.It got executed but i couldn’t see the output…I mean data didn’t got loaded to the target table…
    i have few doubts…
    1. where to run this procedure…in source schema or target schema..
    2. while running this procedure i was getting a message popped up saying undefined bind variable envelope…Can u please help me out..

  3. Great ODI SOAP example. Do you know how to call a Load Plan using a SOAP call? I’ve tried the same syntax but no luck.
    Thanks in advance.

  4. Hi Bhabani,
    Greetings!!!!

    I am a newbie when it comes to execute an ODI scenario through PL/SQL.

    I reffered to your piece of code and passed below mentioned values.

    OdiUser
    OdiPassword
    WorkRepository
    ScenarioName
    ScenarioVersion

    When I executed the Pl/Sql block sql developer prompting me for Enter Binds. Do I need to change any other parameter value and values passed to Bind variables?

    I am facing error after execution. Missing IN or OUT parameter at index:: 9.

    It will be really grateful if you can help me with issue.
    Looking forward to your reply.

    Thanks In advance.

  5. Thanks for the code. I have pretty much the same code – see below , but I get this error

    ns0:Server

    Response Status Code: 500
    Response Reason: Internal Server Error
    Response Version: HTTP/1.1

    pl/sql—–
    CREATE OR REPLACE PROCEDURE jeg_labor_soa_prc_2 IS
    req_envelop VARCHAR2(6000) := ‘

    SUPERVISOR
    soadev2
    WORKREP

    COPY_OF_JEG_LABOR_SOA_PKG
    001
    GLOBAL
    false
    6

    ‘;
    req utl_http.req;
    res utl_http.resp;
    res_envelop VARCHAR2(32767);
    value VARCHAR2(1024);
    name VARCHAR2(256);
    BEGIN
    req := utl_http.begin_request(‘http://orxsoaap801.jacobs.com:15101/oraclediagent/OdiInvoke?wsdl’,’POST’,’HTTP/1.1′);
    utl_http.set_header(req, ‘Content-Type’, ‘text/xml’);
    utl_http.set_header(req, ‘Content-Length’, length(req_envelop));
    utl_http.set_header(req, ‘SOAPAction’, ‘invokeStartScen’);
    utl_http.write_text(req, req_envelop);
    res := utl_http.get_response(req);
    utl_http.read_text(res, res_envelop);
    utl_http.end_response(res);
    EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(res);
    END;

  6. Hi,

    I want to use Oracle Wallet for the SOAP call, so i used the following syntax

    DECLARE
    req UTL_HTTP.req;
    BEGIN
    UTL_HTTP.SET_WALLET(path => ‘file:/eeeee/oracle/wallets ‘);
    req :=
    UTL_HTTP.begin_request (v_odi_invoke_url, ‘POST’, ‘HTTP/1.1’);
    UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET(req, ‘odi-wallet-acesss’);

    END;

    AND MY req_envelop had the following values without providing user name and password of ODI,

    req_envelop :=’


    QUESTION: Using the above code, when we tried to execute the pl/sql package , the soap call returns with an error , reading that the credentials are not mentioned. surely there is something wrong with the syntax. could you please suggest?

  7. I tried to execute above code but getting below XML output and ODI job not submitted. Can you please share your email id or contact number so that I can all directly.

    ———
    S:Serverjavax.xml.bind.UnmarshalException
    – with linked exception:
    [javax.xml.stream.XMLStreamException: ParseError at [row,col]:[14,3]
    Message: Invalid byte 1 of 1-byte UTF-8 sequence.]javax.xml.bind.UnmarshalException
    – with linked exception:
    [javax.xml.stream.XMLStreamException: ParseError at [row,col]:[14,3]
    Message: Invalid byte 1 of 1-byte UTF-8 sequence.]ParseError at [row,col]:[14,3]
    Message: Invalid byte 1 of 1-byte UTF-8 sequence.

  8. Thanks for the post.
    The above code is working fine for invoking the scenario from PL/SQL procedure. If possible could someone suggest on the below points.
    1 : How to capture the sequence of ODI executed steps in PL/SQL procedure?
    2: How to capture the ODI Hard fails in PL/SQL procedure ?

  9. We are trying to call a ODI scenario from PL/SQL using the above code but we are not able to get it work.
    We are using following oracle products:
    Our ODI is from market place Private IP Address:172.16.21.235 Image:ODI Marketplace V12.2.1.4.200618
    Database:cloud database DB System Version:18.9.0.0.200114
    I really appreciate , if any one of you can explain the steps you have done to make this work.

Leave a Reply

Your email address will not be published.


*