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

Duo Liang,

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.

		 acl => 'www.xml',
		 description => 'WWW ACL',
		 principal => 'SCOTT',
		 is_grant => true,
		 privilege => 'connect'
		 acl => 'www.xml',
		 host => '*'

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:8-14-1

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.8-14-2

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:

    req utl_http.req;
    UTL_HTTP.set_wallet('file:C:\...\wallet', NULL);
    req := utl_http.begin_request('');

-    “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.8-14-4

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
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := '';
  buffer varchar2(4000); 
  content varchar2(4000) := '{"name":"package created from oracle"}';
  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
      utl_http.read_line(res, buffer);
    end loop;
    when utl_http.end_of_body 
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:

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

OneSpan Developer Community

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

Join Today

Duo Liang is a Technical Evangelist and Partner Integrations Developer at OneSpan where he creates and maintains integration guides and code shares, helps customers and partners integrate OneSpan products into their applications, and builds integrations within third party platforms.