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

During part one, part two, and part three of this blog series, we have covered several examples of integrating OneSpan Sign Restful APIs with Oracle PL/SQL. These examples have included creating an e-transaction as well as downloading signed documents.

In this blog, we will address the two remaining technical challenges of this series. The first is how to parse and extract information from JSON payload, and the second is how to implement error handing in PL/SQL programming. Without further ado, let’s get started!

Parse JSON in PL/SQL

OneSpan Sign provides you with fully Restful API, so in order to process the information enclosed in the message body in the JSON format, it is very necessary for us to investigate how to achieve this in PL/SQL.

In this section, I will show you how to leverage an Oracle built-in class JSON_OBJECT_T to handle with JSON strings. Let’s learn some basic uses first:

--To declare:
l_obj JSON_OBJECT_T;
--To initialize
l_obj := JSON_OBJECT_T('{ "employee_no":9999 }');
--To get an attribute value
oss_package_id_string := l_obj.get_string('id');

With this, you are already able to handle simple JSON structures, such as extracting the package ID from a payload. Next, we will demonstrate a working code to create a transaction and to retrieve the package ID from response. 

create or replace procedure create_package
 is
  l_http_request utl_http.req;
  l_http_response utl_http.resp;
  l_response_text  VARCHAR2(32767);
  l_obj JSON_OBJECT_T;

  oss_api_url varchar2(50) := 'https://sandbox.esignlive.com/api';
  oss_payload varchar2(4000) := '{"name":"package created from oracle"}';
  oss_api_key varchar2(50) := your_api_key';

begin
  l_http_request := utl_http.begin_request(oss_api_url || '/packages', 'POST',' HTTP/1.1');
  utl_http.set_header(l_http_request, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(l_http_request, 'content-type', 'application/json'); 
  utl_http.set_header(l_http_request, 'Authorization', 'Basic ' || oss_api_key); 
  utl_http.set_header(l_http_request, 'Content-Length', length(oss_payload));
 
  utl_http.write_text(l_http_request, oss_payload);
  l_http_response := utl_http.get_response(l_http_request);

  UTL_HTTP.read_text(l_http_response, l_response_text);
  l_obj := JSON_OBJECT_T(l_response_text);
  dbms_output.put_line(l_obj.get_string('id'));

end create_package;
/

After executing the procedure, expect to see the package ID printed out in your console like the picture below:

Based on this code, you can parse JSON payloads from other API calls according to your own use cases and workflow. Refer to this tutorial for more information regarding to JSON support in PL/SQL. 

Error Handling

In terms of the error handling, we will cover two aspects: how to receive the error message from OneSpan Sign when your API call failed and how to generally introduce error trapping mechanism in PL/SQL to help isolate the cause of the issue when troubleshooting.

We will start with how to receive the OneSpan Sign error message. Below is the code we used to retrieve the response payload from an API:

  l_http_response := utl_http.get_response(l_http_request);
  dbms_output.put_line('Response> Status Code: ' || l_http_response.status_code);
  dbms_output.put_line('Response> Reason Phrase: ' || l_http_response.reason_phrase);
  dbms_output.put_line('Response> HTTP Version: ' || l_http_response.http_version);
 
  for i in 1 .. utl_http.get_header_count(l_http_response) loop
    utl_http.get_header(l_http_response, i, l_response_header_name, l_response_header_value);
    dbms_output.put_line('Response> ' || l_response_header_name || ': ' || l_response_header_value);
  end loop;
 
  utl_http.read_text(l_http_response, l_response_body, 32767);
  dbms_output.put_line('Response body>');
  dbms_output.put_line(l_response_body);

In PL/SQL, no matter the response code, the UTL_HTTP instance will always return the HTTP response, so you don’t need to do extra error stream handling. 

With this code, if you made the API call successfully, you will see status “200” with a proper response payload in the message body:

The error message will also be displayed. Combined with first section of JSON handling, you can directly store the JSON string or extract error message from payload and store it in the logs.

The basic block structure in PL/SQL is begin – end -- exception, and each block can be nested in another block. This provides you the ability to handle exceptions separately and to organize the business logic based on the running result of each block. For example, you need to close the HTTP connection and other resources to avoid keeping them open.

begin
  l_http_request := utl_http.begin_request(
                      url => oss_api_url || '/packages',
                      method => 'POST',
                      http_version => 'HTTP/1.1'
                    );
    ……
exception
  when others then
    if l_http_request.private_hndl is not null then
      utl_http.end_request(l_http_request);
    end if;
 
    if l_http_response.private_hndl is not null then
      utl_http.end_response(l_http_response);
    end if;
    raise;
end;

Troubleshooting Issues

If you encounter issues, depending on the error message, remember to check your connection with OneSpan Sign. Then, narrow down the cause of issue with error trapping. If it is due to an API failure, you will be able to catch and log the error message as demonstrated above. If you still can’t find the cause, you are always welcome to make a post on our Developer Community or to create a support ticket!

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