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

In part 1 of this blog series, we explored how to create your first e-transaction through PL/SQL. From there, we covered uploading documents through a multipart POST request in the part 2 blog. 

Once your signer has completed signing, the next step is to download the signed documents and the evidence summary. So, part 3 of this blog series will pick up where the last left off and demonstrate how to invoke the “download document” API and store the files locally. Let’s get started! 

Downloading Document APIs

OneSpan Sign provides you with several APIs to download documents related to your e-signature process, including signed documents, evidence summary, and attachments if available. Check the API Reference below:

1. Download Documents in Zip File

HTTP Request

GET /api/packages/{packageId}/documents/zip

HTTP Headers

Accept: application/zip
Authorization: Basic api_key

2. Download Evidence Summary

HTTP Request

GET /api/packages/{packageId}/evidence/summary

HTTP Headers

Accept: application/pdf
Authorization: Basic api_key

3. Download document by ID

HTTP Request

GET /api/packages/{packageId}/documents/{documentId}/pdf

HTTP Headers

Accept: application/pdf
Authorization: Basic api_key

4. Download Attachments in Zip File

HTTP Request

GET /api/packages/{packageId}/attachment/zip

HTTP Headers

Accept: application/octet-stream
Content-Type: application/octet-stream
Authorization: Basic api_key

Implementing the Function

Here, I’ve prepared the sample code to download signed documents and to store these documents locally. Feel free to use the code in your own procedure function. You can easily apply any of the above APIs to the code with minimal changes.

We will breakdown the code section by section and explain some challenges therein. The first portion of code is used to invoke the POST API call with the corresponding request headers. 

begin
  -- Make a HTTP request and get the response.
  l_http_request := utl_http.begin_request(
                      url => oss_api_url || '/packages/' || oss_package_id || '/documents/zip',
                      method => 'GET',
                      http_version => 'HTTP/1.1'
                    );
  utl_http.set_wallet('file:C:\wallet5', NULL);
  utl_http.set_header(l_http_request, 'Authorization', 'Basic ' || oss_api_key); 

  l_http_response := utl_http.get_response(l_http_request);

Once the response is received, we’ll print out the status code as part of the logging and error trapping. Then, leverage the function UTL_HTTP.READ_RAW(RESP r, RAW data, PLS_INTEGER len) to convert the response content to Raw type and function DBMS_LOB.WRITEAPPEND (BLOB lob_loc, BINARY_INTEGER amount, RAW buffer) to append the Raw data to a BLOB object. BLOB type is the desired type for us to insert into a table column or to store at a local path.

   dbms_output.put_line('Start to Save file: ' || oss_package_id||'.zip' || ' to folder ' || 'MYDIR');

   l_blob_len := DBMS_LOB.getlength (l_blob);

     l_file :=
        UTL_FILE.fopen ('MYDIR',
                        oss_package_id||'.zip',
                        'WB',
                        32767);

     WHILE l_pos < l_blob_len
     LOOP
        DBMS_LOB.read (l_blob,
                       l_amount,
                       l_pos,
                       l_buffer);
        UTL_FILE.put_raw (l_file, l_buffer, TRUE);
        l_pos := l_pos + l_amount;
     END LOOP;

     UTL_FILE.fclose (l_file);
     dbms_output.put_line('Successful to Save file: ' || oss_package_id||'.zip' || ' to folder ' || 'MYDIR');

The basic exception handling has already been included in the code. We printed out the execution results and closed the resources once an exception was thrown. We built the code in a fashion that you can easily add a retry mechanism allowing multiple attempts.

Running the Code

Once you’ve run the code using the command below:

EXECUTE download_package();

You will see the status code of the API call printed out in the console, as well as other logs we added in the code.

If you browse to the specified directory, you will find your downloaded documents.

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