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.
<soapenv:Header/>
<soapenv:Body>
<odi:OdiStartScenRequest>
<Credentials>
<OdiUser>SUPERVISOR</OdiUser>
<OdiPassword>SUNOPSIS</
<WorkRepository>WORKREP91</
</Credentials>
<Request>
<ScenarioName>PKG_SCOTT</
<ScenarioVersion>001</
<Context>GLOBAL</Context>
<!–Optional:–>
<Synchronous>false</
<!–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:/
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.
<S:Body>
<ns2:OdiStartScenResponse xmlns:ns2=”xmlns.oracle.com/
<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
12 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 ?
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.
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:ServerResponse 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;
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?
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.