Calling API's from PL/SQL using UTL_HTTP

As Big data is becoming more and more important, also the data analytics tools get more and more popular and important. Such as Eloqua, Radian6(Salesforce), Omniture and Bit.ly. On the internet, lots of examples can be found how to call the API's, but mostly it concerns only Java, PHP or .Net. In this blogpost I will show you how to call the API's from PL/SQL.

There are several things you should pay attention to. As the API call mostly runs over HTTPS, you need to setup a wallet. Also, if you work on 11g or higher, you should setup an Access Control List (ACL). And if you are working behind a firewall, you should set a proxy in your PL/SQL code.

Set up a wallet

To setup a wallet, you need to do two things: download a certificate and setup the wallet. To download the certificate, you can use Google Chrome. In the url bar, enter the url for the API, for example https://login.eloqua.com.

Right-click on the green lock next to the url. A popup appears.

Click on the connection tab and click on the Certificate information link. Another popup appears.

Click on the middle path (Verisign Class 3 Extended Validation SSL CA) and click on the "View Certificate" button.

Click on the details tab and then click on the "Copy to file" button. Click next.

Select "Base-64 encoded" and click next.

Enter a file location and a filename. Click next, click finish.


Now that you have the certificate, you can setup the wallet. Go to a dos-box or a terminal session and create a directory, for example eloqua_wallet.

Enter the following commands:

orapki wallet create -wallet d:\eloqua_wallet -pwd <your_password> -auto_login

orapki wallet add -wallet d:\eloqua_wallet -trusted_cert -cert "d:\certificates\eloqua.cer" -pwd <your_password>

Where <your_password> is a password that you can make up yourself. This password will later on be used in the PL/SQL code. This way, you tell Oracle that there is a certificate held in a wallet and this certificate ensures that the site is safe to be called by the API. The reference to this wallet in the PL/SQL code:

utl_http.set_wallet('file:d:\eloqua_wallet', '<your_password>');

Set up ACL

When working with Oracle 11g or 12c, you should setup an ACL to tell Oracle that it is safe to connect to the API. With the following code, you could setup an ACL. You should run this script as the SYS user.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'eloqua_req.xml',
    description  => 'Permissions to access http://secure.eloqua.com',
    principal    => '<username>',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'eloqua_req.xml',               
    principal    => '<username>',
    is_grant     => TRUE,
    privilege    => 'connect',
    position     => null);
   COMMIT;
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'eloqua_req.xml',               
    host         => 'secure.eloqua.com');
   COMMIT;
END;
/

Refer to a proxyserver

If you are behind a firewall, your Oracle database might not have a direct connection to the internet. With utl_http.set_proxy, you can set the proxy that leads the database to the outside world:

utl_http.set_proxy(proxy => '10.0.0.99:8888');

Authentication

The authentication differs from API to API. The Radian6 API requires that you first authenticate. After successful authentication, you will get a token back that you can use in the following API call. Without the token, you cannot call the API. Eloqua expects a Base-64 encoded token that is calculated from the user's site name, concatenated with the userid and password.

The actual call

After the wallet and the proxy, the actual call begins. A call consists of a request to a certain url and some extra required information. This extra information is passed as header info. Examples of header info are the user-agent, user credentials, type of input (json or xml) or length of the header info. The call to the url is done using the url_http.begin_request command:

declare
  l_request utl_http.req;
  l_response utl_http.resp;
  l_value varchar2(2000);
begin
  l_request := utl_http.begin_request('https://secure.eloqua.com/api/bulk/1.0/contact/id','GET','HTTP/1.1');
end;

In this example, a GET request was done. In case of a POST, the post information is required, together with the length of the header information:

utl_http.set_header (r      =>  l_request,

                     name   =>  'Content-Type',  

                     value  =>  'application/json');

--

utl_http.set_header (r      =>  l_request,

                     name   =>  'Content-Length',  

                     value  =>  length(l_rq_body));

utl_http.write_text(l_request,l_rq_body);

Output in JSON or XML

To get the response from the API, you can use utl_http.get_response. If the response is too big, it will be chunked. You have to write a loop to fetch all the chunks into you program:

l_response := UTL_HTTP.GET_RESPONSE(l_request);
loop
    UTL_HTTP.READ_LINE(l_response, l_value, TRUE);
    DBMS_OUTPUT.PUT_LINE(l_value);
end loop;
  UTL_HTTP.END_RESPONSE(l_response);
exception
  when UTL_HTTP.END_OF_BODY
  then
    UTL_HTTP.END_RESPONSE(l_response);


l_value can also be of type CLOB or RAW.

Now that we have all necessary pieces of code, we can make a script to call the API. As an example we call the Eloqua API to get data from a contact with id 1234:

declare
  l_site_name    varchar2(50) := '<companysitename>';
  l_username     varchar2(50) := '<youruserid>';
  l_password     varchar2(50) := '<yourpassword>';
  l_request      utl_http.req;
  l_response     utl_http.resp;
  l_value        clob;
  l_basic_base64 varchar2(2000);
begin
  l_basic_base64 := replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(l_site_name||'\'||l_username||':'||l_password))),chr(13)||chr(10),'');
  --
  utl_http.set_wallet('file:d:\eloqua_wallet', '<yourwalletpassword>');
  utl_http.set_proxy(proxy => '10.0.0.99:8888');
  l_request := utl_http.begin_request('https://secure.eloqua.com/api/Rest/1.0/data/contact/1234','GET','HTTP/1.1');
  utl_http.set_header(l_request, 'User-Agent', 'Mozilla/4.0');
  utl_http.set_header (r      =>  l_request,
                       name   =>  'Authorization',
                       value  =>  'Basic '||l_basic_base64);
  --
  l_response := utl_http.get_response(l_request);
  begin
    loop
      utl_http.read_text(l_response, l_raw, 2000);
      l_value := l_value || l_raw;
      utl_http.read_text(l_response, l_value, 2000);
      dbms_output.put_line('value: '||l_value);
    end loop;
    utl_http.end_response(l_response);
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_response);
  end;
end;
/

If you have APEX 4.0 or higher installed, you can also use APEX_WEB_SERVICE:

declare
  l_site_name    varchar2(50) := '<yourcompanysitename>';
  l_username     varchar2(50) := '<youruserid>';
  l_password     varchar2(50) := '<yourpassword>';
  l_basic_base64 varchar2(2000);
  l_clob         clob;
  l_buffer       varchar2(32767);
  l_amount       number;
  l_offset       number;
begin
  l_basic_base64 := replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(l_site_name||'\'||l_username||':'||l_password))),chr(13)||chr(10),'');
  --
  apex_web_service.g_request_headers(1).name := 'User-Agent';
  apex_web_service.g_request_headers(1).value := 'Mozilla';
  --
  apex_web_service.g_request_headers(2).name := 'Authorization';
  apex_web_service.g_request_headers(2).value := 'Basic '||l_basic_base64;
  --
  l_clob := apex_web_service.make_rest_request(p_url => 'https://secure.eloqua.com/api/Rest/1.0/data/contact/1234',
                                               p_http_method => 'GET',
                                               p_wallet_path => 'file:d:\eloqua_wallet',
                                               p_wallet_pwd => '<yourwalletpassword>' );
  --
  l_amount := 32000;
  l_offset := 1;
  begin
    loop
      dbms_lob.read( l_clob, l_amount, l_offset, l_buffer );
      dbms_output.put_line(l_buffer);
      l_offset := l_offset + l_amount;
      l_amount := 32000;
    end loop;
  exception
    when no_data_found then
      dbms_output.put_line('Nothing found: '||l_buffer);
  end;
end;
/
Note that the interaction with network services is by default disabled in Oracle 11g. Refer to the "Enable networking services in Oracle database 11g" section in the Oracle Application Express Installation Guide.