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

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 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.

Similar Posts

10 comments

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

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

webservice

Hello,

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

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

how is res_envelop variable getting its value.

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

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.

Use the payload as given below.
Soap request for loadplan

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.

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;

Leave a replay to ajeet Cancel reply

required*

Are you a human? *