Skip to Main Content

Calling webservice from PLSQL

848964May 11 2012 — edited May 1 2013

I am having a scenario where .Net web service will need be called from the PLSQL. Data will pass from Oracle e business to .Net application.
I have made the following code ready by taking help from google, other OTN forums, Metalink.

In this there are 2 inputs, employee number as varchar and amount as number and output it should return is the approver employee number (varchar2).

The function code is mentioned below :

CREATE OR REPLACE FUNCTION apps.xx_createadv_fnc (
empnumber IN VARCHAR2,
amount IN NUMBER
l_service SYS.UTL_DBWS.service;
l_wsdl_url VARCHAR2 (32767);
l_namespace VARCHAR2 (32767);
l_service_qname SYS.UTL_DBWS.qname;
l_port_qname SYS.UTL_DBWS.qname;
l_operation_qname SYS.UTL_DBWS.qname;
l_xmltype_in SYS.XMLTYPE;
l_xmltype_out SYS.XMLTYPE;
l_return VARCHAR2 (32767);
l_wsdl_url := '';
l_namespace := '';
l_service_qname :=
SYS.UTL_DBWS.to_qname (l_namespace, 'CashAdvanceDataService');
l_port_qname := SYS.UTL_DBWS.to_qname (l_namespace, '4949');
l_operation_qname :=
SYS.UTL_DBWS.to_qname (l_namespace, 'GetEmployeeApprover()');
l_service :=
(wsdl_document_location => urifactory.geturi
service_name => l_service_qname
l_call :=
SYS.UTL_DBWS.create_call (service_handle => l_service,
port_name => l_port_qname,
operation_name => l_operation_qname
l_xmltype_in :=
( '<?xml version="1.0" encoding="utf-8"?>
<ws_add xmlns="'
|| l_namespace
|| '">
|| empnumber
|| '</int1>
|| amount
|| '</int2>
l_xmltype_out :=
SYS.UTL_DBWS.invoke (call_handle => l_call,
request => l_xmltype_in);
SYS.UTL_DBWS.release_call (call_handle => l_call);
SYS.UTL_DBWS.release_service (service_handle => l_service);
l_return := l_xmltype_out.EXTRACT ('//return/text()').getstringval ();
RETURN l_return;

This function compiled sucessfully. But while executing this function its showing some error related to UTL_DBWS package but this actually is not part of actual question. Since this is something which DBA will get it installed.

My actual questions is that if my code is correct with respect to my requirement where input is passed as employee number as varchar and amount as number and output will be the approver employee number (varchar). This logic is written in a plsql procedure GetEmployeeApprover(This name is mentioned in the above code also

l_operation_qname := SYS.UTL_DBWS.to_qname (l_namespace, 'GetEmployeeApprover()');

My actual questions are:
1) Does my code looks fine with respect to my requirement (2 inputs and 1 OUT parameter)

2) If say I have another procedure where I am having 2 inputs and 2 ouputs (as OUT Parameter)
say employee - number type , amount type are inputs
and out parameters are assignment number(varchar2), last name(varchar2). what changes will be there in the existing code

3) If there is 1 OUT parameter as Ref cursor. how the above code will change ?

4) If there is 1OUT parameter as object type. how the above code will change ?

This is little urgent. I would appreciate any suggestions on this post.


Post Details
Added on May 11 2012