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
