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.
