Forum Stats

  • 3,851,489 Users
  • 2,263,988 Discussions
  • 7,904,739 Comments

Discussions

Rest API call PL/SQL

User_A8XLF
User_A8XLF Member Posts: 10 Employee
edited Jan 29, 2021 2:50PM in SQL & PL/SQL

Hi,

I am new to PL/SQL and i have to make Rest API call using it. I have to send an argument with the request and the API will give the data as per the argument. Can you please suggest how i can do this. Any help will be appreciated.

PS: If possible please post a working code snippet.

Thanks & Regards

Shubham Joshi

Tagged:

Best Answer

Answers

  • Birthe Gebhardt
    Birthe Gebhardt Member Posts: 23 Red Ribbon

    Hi Shubham,

    depend if you have APEX installed on your database or not.

    If APEX is installed, you can use APEX.WEB_SERVICE.make_request. Otherwise you can use UTL_HTTP.

    Further you need access to the URL from inside the database. Please check your ACE/L configuration. For this check you need the PLS/SQL packages DBMS_NETWORK_ACL_ADMIN.


    For you REST call you need to clarify which kind of authentification is used. Befoer I transfer the REST call into the database I ceck the connection and the calls using POSTMAN.

    To post a working snippet is difficulty, because of the missing information:

    • Database Version
    • APEX Installation
    • REST authentification
    • Wallets

    Here is an example for an REST call: https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6121/index-en.html


    Bye,

    Birthe

    User_A8XLF
  • Bilal
    Bilal Member Posts: 507 Bronze Badge

    Hi Shubham,

    Try out the following PLSQL code. Pls, note it is for calling HTTP request to make it simple but not HTTPS which will require you to do some extra work. Also to call the URL you need to register it DBMS_NETWORK_ACL_ADMIN as suggested above.

    procedure call_rest_from_plsql_with_params(p$company_name varchar2) as
     l$request utl_http.req;
     l$response utl_http.resp;
     l$crawler_url varchar2(4000) := 'http://localhost:5000/get_company_data'; --Change to suit yours
     l$buffer varchar2(4000); 
     l$response_text varchar2(4000);
     l$params varchar2(4000) := '{"companyname":"'||p$company_name||'"}'; -- Pass your parameter here  as JSON
     begin
     -- Setting up the request and response objects
      l$request := utl_http.begin_request(l$crawler_url, 'GET',' HTTP/1.1');
      utl_http.set_header(l$request, 'user-agent', 'mozilla/4.0'); 
      utl_http.set_header(l$request, 'Content-Type', 'application/json'); 
      utl_http.set_header(l$request, 'Content-Length', length(l$params));
         -- Passing parameteres to GET call
      utl_http.write_text(l$request, l$params);
      -- Getting the response object
      l$response := utl_http.get_response(l$request);
      -- Reading the data from response object
      begin
        loop
         utl_http.read_line(l$response, l$buffer);  
         l$response_text := l$response_text || l$buffer;
        end loop;
        utl_http.end_response(l$response);
    
      exception
        when utl_http.end_of_body then
         utl_http.end_response(l$response);
      end;
      -- Print the l$response or do whatever you want to using Oracle SQL JSON functions
    end;
    

    I hope this will be useful for what you seek.

    Wish you the best of luck.

    Take care and best Regards

    Bilal

    User_A8XLF
  • User_A8XLF
    User_A8XLF Member Posts: 10 Employee

    Thanks you for the response. I have to use utl_http package. Is it possible to use utl_http in case some user authentication is required. If yes how we do that?

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,860 Red Diamond

    UTL_HTTP supports Basic Authentication.

    Other types of authentication like NTLM needs to be implemented by the developer.