Refat Eid: How to call webservice from PLSQL

The object of this article is to show how to call webservice from PLSQL
and thanks for Oracle to provide such a thing

I'm using a local webservice

http://localhost/PAAET.asmx

I have a collection of functions inside this webservice. one of them is GetStudentInfo
when we request the service function on the web browser

http://localhost/PAAET.asmx?op=GetStudentInfo

we will use the service response and request over SOAP 1.2
Request:

<soap12:envelope xmlns:soap12="http://www.w3.org/2003/05/soap-envelope" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap12:body>
<getstudentinfo xmlns="http://127.0.0.1:8080/">
<wvcivno>string</wvcivno>
</getstudentinfo>
</soap12:body>
</soap12:envelope>

Response :

<soap12:envelope xmlns:soap12="http://www.w3.org/2003/05/soap-envelope" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap12:body>
<GetStudentInfoResponse xmlns="http://127.0.0.1:8080/">
<GetStudentInfoResult>
<pv_arab_name_1>string</pv_arab_name_1>
</getstudentinforesult>
</getstudentinforesponse>
</soap12:body>
</soap12:envelope>

Now let's take about what you will do inside the PLSQL

login to DB as sysdba this is a must.
/* Formatted on 9/26/2018 1:26:58 AM (QP5 v5.163.1008.3004) */ BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (ACL => 'paci.xml',
DESCRIPTION => 'Test Sample ACL',
PRINCIPAL => 'IT_REG_DBA', -- user to access the service it must be capital letters
IS_GRANT => TRUE,
PRIVILEGE => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (ACL => 'paci.xml',
PRINCIPAL => 'IT_REG_DBA', IS_GRANT => TRUE,
PRIVILEGE => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (ACL => 'paci.xml',
HOST => '127.0.0.1'); -- ip address of the service
END;

COMMIT;
make sure the file has been added to the database

SELECT any_path FROM resource_view WHERE any_path like '%.xml';
Grant Execution for the user

GRANT EXECUTE ON UTL_HTTP TO IT_REG_DBA;
Now execute this PLSQL Code

/* Formatted on 9/26/2018 1:46:49 AM (QP5 v5.163.1008.3004) */
DECLARE
L_HTTP_REQUEST UTL_HTTP.REQ;

L_HTTP_RESPONSE UTL_HTTP.RESP;

L_BUFFER_SIZE NUMBER (10) := 512;

L_LINE_SIZE NUMBER (10) := 50;

L_LINES_COUNT NUMBER (10) := 20;

L_STRING_REQUEST VARCHAR2 (512);

L_LINE VARCHAR2 (128);

L_SUBSTRING_MSG VARCHAR2 (512);

L_RAW_DATA RAW (512);

L_CLOB_RESPONSE CLOB;

L_HOST_NAME VARCHAR2 (128) := 'http://localhost'; -- the service url

L_CIVILID VARCHAR2 (12) := '283030306556'; -- the service parameter

L_RESP_XML XMLTYPE;

L_RESULT_XML_NODE VARCHAR2 (128);

L_NAMESPACE_SOAP VARCHAR2 (128)
:= 'xmlns="http://www.w3.org/2003/05/soap-envelope"';

L_RESPONSE_FIRSTNAME VARCHAR2 (128); -- first name

L_RESPONSE_SECONDNAME VARCHAR2 (128); -- second name

L_RESPONSE_THIRDNAME VARCHAR2 (128); -- third name

L_RESPONSE_LASTNAME VARCHAR2 (128); -- last name
BEGIN
L_STRING_REQUEST :=
'<?xml version="1.0" encoding="utf-8"?>

<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">

<soap12:Body>

<GetStudentInfo xmlns="http://127.0.0.1:8080/">

<WVCIVNO>'
|| L_CIVILID
|| '</WVCIVNO>

</GetStudentInfo>

</soap12:Body>

</soap12:Envelope>';

UTL_HTTP.SET_TRANSFER_TIMEOUT (10);

L_HTTP_REQUEST :=
UTL_HTTP.BEGIN_REQUEST (URL => 'http://localhost/PAAET.asmx' -- COMPLETE SERVICE URL
, METHOD => 'POST', HTTP_VERSION => 'HTTP/1.1');

UTL_HTTP.SET_HEADER (L_HTTP_REQUEST, 'User-Agent', 'Mozilla/4.0');

UTL_HTTP.SET_HEADER (L_HTTP_REQUEST, 'Connection', 'close');

UTL_HTTP.SET_HEADER (L_HTTP_REQUEST,
'Content-Type',
'application/soap+xml; charset=utf-8');

UTL_HTTP.SET_HEADER (L_HTTP_REQUEST,
'Content-Length',
LENGTH (L_STRING_REQUEST));

<<REQUEST_LOOP>>
FOR I IN 0 .. CEIL (LENGTH (L_STRING_REQUEST) / L_BUFFER_SIZE) - 1
LOOP
L_SUBSTRING_MSG :=
SUBSTR (L_STRING_REQUEST, I * L_BUFFER_SIZE + 1, L_BUFFER_SIZE);

BEGIN
L_RAW_DATA := UTL_RAW.CAST_TO_RAW (L_SUBSTRING_MSG);

UTL_HTTP.WRITE_RAW (R => L_HTTP_REQUEST, DATA => L_RAW_DATA);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT REQUEST_LOOP;
END;
END LOOP REQUEST_LOOP;

L_HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE (L_HTTP_REQUEST);

DBMS_OUTPUT.PUT_LINE (
'Response> status_code: "' || L_HTTP_RESPONSE.STATUS_CODE || '"');

DBMS_OUTPUT.PUT_LINE (
'Response> reason_phrase: "' || L_HTTP_RESPONSE.REASON_PHRASE || '"');

DBMS_OUTPUT.PUT_LINE (
'Response> http_version: "' || L_HTTP_RESPONSE.HTTP_VERSION || '"');

BEGIN
<<RESPONSE_LOOP>>
LOOP
UTL_HTTP.READ_RAW (L_HTTP_RESPONSE, L_RAW_DATA, L_BUFFER_SIZE);

L_CLOB_RESPONSE :=
L_CLOB_RESPONSE || UTL_RAW.CAST_TO_VARCHAR2 (L_RAW_DATA);
END LOOP RESPONSE_LOOP;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN
UTL_HTTP.END_RESPONSE (L_HTTP_RESPONSE);
END;

IF (L_HTTP_RESPONSE.STATUS_CODE = 200)
THEN
-- Create XML type from response text

L_RESP_XML := XMLTYPE.CREATEXML (L_CLOB_RESPONSE);

-- Clean SOAP header

SELECT EXTRACT (L_RESP_XML, 'Envelope/Body/node()', L_NAMESPACE_SOAP)
INTO L_RESP_XML
FROM DUAL;

-- Extract Culture Info value

DBMS_OUTPUT.PUT_LINE ('Response from PACI webservices:');

L_RESULT_XML_NODE :=
'/GetStudentInfoResponse/GetStudentInfoResult/PV_ARAB_NAME_1';

SELECT EXTRACTVALUE (L_RESP_XML,
L_RESULT_XML_NODE,
'xmlns="http://127.0.0.1:8080/"')
INTO L_RESPONSE_FIRSTNAME
FROM DUAL;
END IF;

DBMS_OUTPUT.PUT_LINE ('Civil Id > ' || L_CIVILID);

DBMS_OUTPUT.PUT_LINE (
'Full Name > '
|| L_RESPONSE_FIRSTNAME);

IF L_HTTP_REQUEST.PRIVATE_HNDL IS NOT NULL
THEN
UTL_HTTP.END_REQUEST (L_HTTP_REQUEST);
END IF;

IF L_HTTP_RESPONSE.PRIVATE_HNDL IS NOT NULL
THEN
UTL_HTTP.END_RESPONSE (L_HTTP_RESPONSE);
END IF;

COMMIT;
END;

Now the result of the service will print the first name of the given ssn number into the screen

Refat Eid

How to call webservice from PLSQL

filter attribute that checks whether current connection is secured

Total Pageviews

Report Abuse