Call ODI Scenario from PLSQL

Posted on January 12, 2013 by Bhabani in ODI / 15 Comment(s)

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>

<OdiUser>SUPERVISOR</OdiUser>

<OdiPassword>SUNOPSIS</OdiPassword>

<WorkRepository>WORKREP91</WorkRepository>

</Credentials>

<ScenarioName>PKG_SCOTT</ScenarioName>

<Context>GLOBAL</Context>

<!–Optional:–>

<Synchronous>false</Synchronous>

<!–Optional:–>

</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/“>

</ns2:OdiStartScenResponse>

</S:Body>

</S:Envelope>

Now Its Done. Check your operator for the session 2518091.

See You in the next post.

Bhabani(http://dwteam.in) - Currently Bhabani is working as Technical Lead Product Development at Harman International. He has good expertise on Oracle, ODI, Pervasive Data Integrator, MSBI, Talend, Cloud Integration (AWS, GCP, Azure) , Map-R and Apache Airflow. He is also contributing in ODI-OTN forum for last 7 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Eduard July 29, 2013 at 12:32 am / Reply

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

Bhabani July 29, 2013 at 8:29 am / Reply

Hi Friend,
Can you post your soap request ? Are you passing multiple variable in below format ?

webservice

DL April 27, 2020 at 6:01 am / Reply

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 ?

ajeet October 6, 2013 at 7:26 am / Reply

Hello,

Can you please let me know how can we trigger the odi job from pl/sql the above code is not working.

Bhabani October 6, 2013 at 9:53 am / Reply

Can you explain your error ? This code is tested and its working without any issues.

sid August 3, 2015 at 6:29 am / Reply

how is res_envelop variable getting its value.

sid August 4, 2015 at 4:59 am / Reply

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..

byarnell September 29, 2016 at 5:54 pm / Reply

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.

Bhabani October 3, 2016 at 3:59 pm / Reply

Use the payload as given below.
Soap request for loadplan

VD December 14, 2016 at 8:52 am / Reply

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.

KEVIN January 19, 2017 at 10:20 pm / Reply

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;

Karthikjayraj May 29, 2018 at 6:35 am / Reply

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?

Prakash May 13, 2019 at 4:48 pm / Reply

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.

Christian January 20, 2020 at 8:40 am / Reply

How can I check that the scenario ist completed?
In a loop call an another Webservice?

DL April 27, 2020 at 5:53 am / Reply

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 ?

Blog

Call ODI Scenario from PLSQL

About the author

Similar Posts

15 comments

Leave a replay to KEVIN Cancel reply