Consuming SAP PI webservice in oracle PL/SQL stored procedure

[Former Member]

Hi  Experts,

We have a requirement of consuming sap pi webservice in a oracle stored procedure. Once the PI webservice is generated how can we consume this webservice in a oracle stored procedure. I am using UTL_DBWS and UTL_HTTP package in oracle to consume the PI webservice.

Please see the sample oracle stored procedure for consuming PI  webservice.

create or replace PROCEDURE P_WEBSERVICE AS

  req   utl_http.req;

  resp  utl_http.resp;

  value VARCHAR2(1024);

BEGIN

  req := utl_http.begin_request('http://piprd:70000/dir/wsdl?p=sa/7ac366e8a93a3dca97dca07cf339a612');

  IF (username IS NOT NULL) THEN

    utl_http.set_authentication(req, 'userid', 'password'); -- Use HTTP Basic Authen. Scheme

  END IF;

  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

  resp := utl_http.get_response(req);

  utl_http.http_response_error_check(FALSE);

  LOOP

    utl_http.read_line(resp, value, TRUE);

    dbms_output.put_line(value);

  END LOOP;

  utl_http.end_response(resp);

EXCEPTION

  WHEN utl_http.end_of_body THEN

    utl_http.end_response(resp);

END P_WEBSERVICE;

I am unable to call the PI webservice using the above stored procedure.

Please suggest how can we achieve it.

Thanks,

Neeraj

[dipen_pandya]

Hi Neeraj,

We have a same requirement, can you please let me know how you achieved your scenario.

Please give your suggestions and helpful documents.

This will be a great help & appreciated a lot.

Thanks & Regards,

Dipen.

[christian_praus]

Hi Dipen,

did you get your scenario to work? Otherwise I could give you some help.

Regards,

Christian

[dipen_pandya]

Hi,

Thanks for replying.

Not yet, we left that development and continued with File(XML) to XI to RFC scenarios.

We wanted to develop stored procedures to replace file adapters and to consume PI webservices in stored procedures.

Please let me know how exactly to code stored procedures in oracle?

It would be a great help.

Thanks a lot,

Regards,

Dipen.

[christian_praus]

Hi,

check Oracle Documentation for Stored Procedure. Use Oracle SQL Developer as ide. Best practise is to put your code in a package.

Good Book is: "Oracle PL/SQL Programming" by Steven Feuerstein.

A simple Stored Procedure looks like that:

CREATE OR REPLACE PROCEDURE my_proc AS

BEGIN

     -- put your code here

     NULL;

END my_proc;

[dipen_pandya]

Hi,

Thank you so much for valuable inputs.

I will coordinate with oracle team and assist them accordingly as per your suggestion.

I shall revert if any query.

Thanks a lot

Regards,

Dipen.

[former_member184720]

Hi Neeraj - Are you sure you need to give the wsdl URL or the actual soap URL for the begin_request method?

However can you please check the sample procedure and follow the same as i don't see soap action/method in your procedure name. You can find soap action&namespace and method details in WSDL.

http://www.oracle-base.com/articles/9i/consuming-web-services-9i.php

[Former Member]

Hi Neeraj,

Can you explain the scenario briefly like where you need to configure the stored procedure that is sender or receiver side.

Regards

Raj

[Former Member]

HI Raja,

Scenario is I have created a webservice in SAP PI i.e. WSDL which is generated from sender agreement, This webservice needs to be consumed by a stored procedure in oracle database. I am using sender SOAP adapter and xi adapter to exchange message.

I am working on this scenario first time. Same webservice I have consumed using .Net but I am unable to use it in Oracle.

Regards,

Neeraj

[gagandeep_batra]

Hi Neeraj

What error you are getting?

Regards

GB

[Former Member]

Dear Gagandeep,

When I am trying to execute the stored procedure  I am getting this error.

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1130

ORA-12545: Connect failed because target host or object does not exist

ORA-06512: at ".P_WEBSERVICE", line 10

ORA-06512: at line 2

[gagandeep_batra]

Hi Neeraj

Plz check the  whether you are able to ping the service from database server or not?

also check

Calling a web service from within PLSQL | Oracle Forums

Regards

GB

[iaki_vila]

Hi Neeraj,

According with the exception the system in which the database is installed can't reach to the PI. Try to talk with the basis team if the PI has an alternative URL to be acceded from outside, i think is weird to have the port 70000 opened. Try to know if the OS DB system can reach to the PI host, the basis team should to create the IP/host equivalence in the hosts file or to modify the DNS in order to can access to PI from ORACLE.

Regards.