Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

How to call a Web Soap Service or API from PLSQL

Mustafa, 2019-02-272019-02-27

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.

Oracle PL/SQL
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, '&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;

 

12c Development SQL / PLSQL Useful Scripts apicallplsqlweb_service

Post navigation

Previous post
Next post

Comments (9)

  1. Rudi says:
    2021-03-23 at 15:07

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

    Reply
    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 => ‘

      Reply
      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

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

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

      Reply
  3. 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.

    Reply
    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

      Reply
    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

      Reply
      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….’

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes