February 2019 – Keep Learning Keep Living

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;

Month: February 2019

How to call a Web Soap Service or API from PLSQL

Useful 12c new features for developers