How to call a Web Soap Service or API from PLSQL

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.

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, '&lt;', '<');
    X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '&gt;', '>');
    ...
    */
    
    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;

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;

Comments (9)

Rudi says:

2021-03-23 at 15:07

hallo,
can you give me an example for call a webservice with this function ?

1. Mustafa says:
  
  2021-03-23 at 15:16
  
  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 => ‘
  
  1. Rudi says:
    
    2021-03-23 at 15:51
    
    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
    
Name says:

2021-11-09 at 13:36

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).

1. Mustafa says:
  
  2021-11-09 at 14:45
  
  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.
  
kumar says:

2023-01-12 at 20:28

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.

1. Mustafa says:
  
  2023-01-20 at 10:36
  
  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
  
2. ww says:
  
  2023-05-04 at 12:45
  
  plz help me
  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
  
  examlpe plz
  
  1. Mustafa says:
    
    2023-05-11 at 11:05
    
    Hi Kumar, parameter name in the code is not P_URL it is P_WEBSERVICE_URL. so you should use p_webservice_url => ‘https://api….’

Comments (9)

Leave a Reply Cancel reply