OneSpan Sign Developer: Integrate with Oracle PL/SQL – Part 1

OneSpan Sign provides RESTful web services that can be easily integrated with your application regardless which programming language or technology you are using. Oracle PL/SQL (Procedural Language extensions to SQL), on the other hand, allows you to write code in a procedural manner and interact with other programs through APIs. Directly invoking API Calls through PL/SQL helps you interact with the database more natively and intuitively.

In this blog, we will explore how you can leverage both technologies as well as investigate the most basic use case to create a OneSpan Sign transaction through PL/SQL.

Access Control Lists (ACL)

To begin, make sure you have granted your user the appropriate permissions to connect to the OneSpan Sign service. This information is stored in your Access Control Lists. These permissions restrict the remote hosts’ ability to connect to the Oracle database. 

In the example below, I first dropped the existing ACL and then created a new one while granting my user the permission to ping all hosts from the database. You can check Oracle’s Official Documentation for more detailed specifications regarding ACL configurations.

begin
    dbms_network_acl_admin.drop_acl('www.xml');
	dbms_network_acl_admin.create_acl(
		 acl => 'www.xml',
		 description => 'WWW ACL',
		 principal => 'SCOTT',
		 is_grant => true,
		 privilege => 'connect'
	 );
	 dbms_network_acl_admin.assign_acl(
		 acl => 'www.xml',
		 host => '*'
	 );
end;
/

The documentation for “ENVIRONMENT URLS & IP ADDRESSES” is a good reference for all the host URLs or IP addresses from OneSpan Sign that you would potentially whitelist in your ACL. 

After creating an ACL, the records are stored at “DBA_NETWORK_ACL_PRIVILEGES” table. Use the command below to double check the result:

SELECT * FROM dba_network_acl_privileges where principal='SCOTT';

Oracle Wallet

Without further TLS configuration, you will see a “Certificate validation failure”, if you try and ping the OneSpan site, as shown below:

The solution here is to download the certificate of your target resource, add this trusted certificate in a wallet, and assign the specific wallet to your HTL_HTTP component. This will allow your database to know which resource to trust when connecting a URL with SSL/TLS. You can either download the certificate of the site or the certificate of its Certificate Authority (CA).

Take OneSpan Sign site for example, the OSS certificate is issued by “GlobalSign Root CA –R1” which you can download from your browser following below steps:
-    Head up to your web portal
-    Click the lock icon beside the address bar
-    Click the certificate of the site
-    Find CA’s certificate at the root node and from the Certification Path tab. 
-    Export the certificate in either “.crt” or “.cer” format.

The next step is to create a wallet and add the downloaded certificate. You can achieve this through two methods. The first is through the Oracle Wallet Manager like below:

Alternatively, you can go through the system console using the commands below:

orapki wallet create -wallet C:\...\wallet -pwd WalletPasswd123 -auto_login
orapki wallet add -wallet C:\...\wallet -trusted_cert -cert "C:\...\ossroot.cer" -pwd WalletPasswd123

Where we registered the OneSpan Sign root certification to the wallet. Make sure you have the “read” permissions on the wallet folder. I found the easiest way to avoid the “failure to open file” error due to a lack of permissions is to copy the folder to another location so that the new folder is accessible to any of the system’s users.

To query all certifications fired in a wallet, use below command line:

orapki wallet display -wallet C:\...\wallet

Now, let’s ping the OneSpan Sign site again:

declare
    req utl_http.req;
begin
    UTL_HTTP.set_wallet('file:C:\...\wallet', NULL);
    req := utl_http.begin_request('https://sandbox.esignlive.com');
end;
/

Note:
-    “file:” should be included in the wallet path
-    Because I set “-auto_login” when creating the wallet, I just need to put “NULL” as a wallet credential

If you see the message, “PL/SQL procedure successfully completed”, displayed in the console, it means you can successfully reach out to the OneSpan Sign site.

Create Transaction through UTL_HTTP

After all prerequisites are met, you are ready to code! In this section, we’ll showcase you how to use UTL_HTTP component to send out a POST request with JSON payload. See below:

create or replace procedure create_package
as
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'https://sandbox.esignlive.com/api/packages';
  buffer varchar2(4000); 
  content varchar2(4000) := '{"name":"package created from oracle"}';
 
begin
  req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Authorization', 'Basic {your_api_key}'); 
  utl_http.set_header(req, 'Content-Length', length(content));
 
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
end create_package;
/

In the procedure to “create_package”, an HTTP POST called (/api/packages) was invoked to create a minimal transaction using only the recipient’s name. Run the script below, and you will see the package ID returned to you if the procedure was successfully executed:

SET SERVEROUTPUT ON
EXEC create_package();

Looking Ahead to Future Installments of the Series

In this article, we’ve successfully connected to the OneSpan Sign server through Oracle PL/SQL and invoked an API Call to create a basic transaction. Upcoming articles in this blog series will provide more code samples to address the most popular use cases. 

If you have any questions regarding this blog or anything else concerning integrating OneSpan Sign into your application, visit the Developer Community Forums. Your feedback matters to us!

OneSpan Developer Community

Join the OneSpan Developer Community! Forums, blogs, documentation, SDK downloads, and more.

Join Today