Hello Everyone,
I would like to share one of my codes which allows you to call a web service (soap service) or API through PLSQL. I modified it time to time whenever something new comes up and I still do. I hope it helps you too.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
Create Or Replace Function Sf_Call_Web_Service(P_Webservis_Url Varchar2, P_Request_Xml Clob, P_Soap_Action Varchar2 := '', P_Service_Type Varchar2 := 'WEBSERVICE', P_Username Varchar2 := '', P_Password Varchar2 := '', P_Charset Varchar2 := '', P_Request_Content_Type Varchar2 := '') Return Xmltype As Req Utl_Http.Req; Resp Utl_Http.Resp; X_Result_Text Varchar2(32767); X_Result_Clob Clob; X_Result Xmltype; X_Content_Length Number := 0; Begin /* SET YOUR WALLET IF YOU NEED: Utl_Http.Set_Wallet('file:YOUR_WALLET_DIRECTORY_HERE', 'YOUR_WALLET_PASSWORD'); */ Utl_Http.Set_Transfer_Timeout(500); Req := Utl_Http.Begin_Request(P_Webservis_Url, 'POST', Utl_Http.Http_Version_1_1); If P_Username Is Not Null Then Utl_Http.Set_Authentication(Req,P_Username,P_Password); End If; If P_Service_Type = 'WEBSERVICE' Then Utl_Http.Set_Header (Req, 'User-Agent', 'Mozilla/4.0'); Utl_Http.Set_Header (Req, 'Content-Type',Nvl(P_Request_Content_Type, 'text/xml')|| Case When P_Charset Is Not Null Then ';charset='||P_Charset End); Utl_Http.Set_Body_Charset('UTF8'); Utl_Http.Set_Header (Req, 'SOAPAction', P_Soap_Action); Utl_Http.Set_Header ( Req, 'Transfer-Encoding', 'chunked' ); Elsif P_Service_Type = 'API' Then Utl_Http.Set_Header (Req, 'Keep-Alive', '900'); Utl_Http.Set_Header (Req, 'Connection', 'keep-alive'); Utl_Http.Set_Header (Req, 'Content-Type',Nvl(P_Request_Content_Type, 'application/x-www-form-urlencoded')); End If; -- Get Raw Request Length to support special chars like: ş Ğ æ etc. Declare X_Length Pls_Integer := Dbms_Lob.Getlength(P_Request_Xml); X_Data Varchar2(8000); X_Start_Pos Pls_Integer := 1; Begin While X_Start_Pos <= X_Length Loop X_Data := Dbms_Lob.Substr(P_Request_Xml, 4000, X_Start_Pos); X_Content_Length := X_Content_Length + -- my local charset is WE8ISO8859P9, so you can change it Utl_Raw.Length(Utl_Raw.Convert(Utl_Raw.Cast_To_Raw(X_Data),'american_america.al32utf8','AMERICAN_AMERICA.WE8ISO8859P9')); X_Start_Pos := X_Start_Pos + 4000; End Loop; End; Utl_Http.Set_Header (Req, 'Content-Length', X_Content_Length); -- Send Request By Chunks in case request is bigger than 32767 bytes Declare X_Length Pls_Integer := Dbms_Lob.Getlength(P_Request_Xml); X_Data Varchar2(32000); X_Start_Pos Pls_Integer := 1; Begin While X_Start_Pos <= X_Length Loop X_Data := Dbms_Lob.Substr(P_Request_Xml, 32000, X_Start_Pos); -- again my local charset is WE8ISO8859P9, so you can change it Utl_Http.Write_Raw(Req, Utl_Raw.Convert(Utl_Raw.Cast_To_Raw(X_Data), 'american_america.al32utf8', 'AMERICAN_AMERICA.WE8ISO8859P9')); X_Start_Pos := X_Start_Pos + 32000; End Loop; End; -- Get Response Resp := Utl_Http.Get_Response (Req); Dbms_Lob.Createtemporary(X_Result_Clob, True); X_Result_Text := Null; Begin Loop Utl_Http.Read_Text(Resp, X_Result_Text, 32767); Dbms_Lob.Writeappend(X_Result_Clob, Length(X_Result_Text), X_Result_Text); End Loop; Exception When Utl_Http.End_Of_Body Then Utl_Http.End_Response(Resp); End; X_Result_Clob := Ltrim(Ltrim(X_Result_Clob, Chr(10)), Chr(13)); /* just in case if somehow your response contains html codes instead of < or > you can replace them in here like: X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '<', '<'); X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '>', '>'); ... */ X_Result := Xmltype.Createxml(X_Result_Clob); Return X_Result; Exception When Others Then -- some logging procedure can be called here Raise; End Sf_Call_Web_Service; |
hallo,
can you give me an example for call a webservice with this function ?
Hi Rudi,
it could be very easy based on the web service that you want to call.
select Sf_Call_Web_Service(p_url => ‘www.myWebServiceURL.com/MyService’, p_request => ‘
thank you for your quick answer.
but sorry, i get an error message
select Sf_Call_Web_Service(p_url => ‘https://api.predic8.de/shop/products/’, p_request => ‘>’) from dual;
ORA-06553: PLS-306: wrong number or type
Use this:
SELECT UPPER ( (SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE parameter = ‘NLS_LANGUAGE’)
|| ‘_’
|| (SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE parameter = ‘NLS_TERRITORY’)
|| ‘.’
|| (SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE parameter = ‘NLS_CHARACTERSET’))
INTO l_current_charset
FROM DUAL;
Instead of ‘AMERICAN_AMERICA.WE8ISO8859P9’.
Also do not set ‘Content-Length’ when you are using ‘chunked’ (doing that might give you 400 bad request responses).
Hi,
thanks for your comment. I was a little bit lazy about charset, as you pointed out it could be read from db. I am not sure about Content-length, I never got a bad request error and I put them for a reason but this is an old code maybe I should review it.
thank you for the procedure you have published.
Will UTL_HTTP.BEGIN_REQUEST works with WCF service (.SVC ), we have the webservice in .svc just wanted to confirm if that works.
Hi Kumar,
yes of course it will work, every web service technology might require additional steps but there are examples for almost everything these days. here is an example:
https://community.oracle.com/tech/developers/discussion/2605959/pl-sql-calling-web-svc-service