Hi there, Yes, you can directly use above dummy payload. You can also refer to this thread "Trouble uploading a document through multipart/form-data via Oracle's UTL_HTTP" for reference. Duo

Hi Tejaswi, Do you want to send HTTP POST request directly from PL/SQL? To better understand the POST request structure, refer to our OneSpan Sign Developer: Upload Multiple Documents with REST blog. For JSON content, you don't need to specify sender, below is a hardcoded sample payload with two signers, one document and one signature for each signer:

{
   "roles":[
      {
         "id":"Role1",
         "signers":[
            {
               "email":"[email protected]",
               "firstName":"1.firstname",
               "lastName":"1.lastname",
               "company":"OneSpan Sign"
            }
         ]
      },
      {
         "id":"Role2",
         "signers":[
            {
               "email":"[email protected]",
               "firstName":"2.firstname",
               "lastName":"2.lastname",
               "company":"OneSpan Sign"
            }
         ]
      }
   ],
   "documents":[
      {
         "approvals":[
            {
               "role":"Role1",
               "fields":[
                  {
                     "page":0,
                     "top":100,
                     "subtype":"FULLNAME",
                     "height":50,
                     "left":100,
                     "width":200,
                     "type":"SIGNATURE"
                  }
               ]
            },
            {
               "role":"Role2",
               "fields":[
                  {
                     "page":0,
                     "top":300,
                     "subtype":"FULLNAME",
                     "height":50,
                     "left":100,
                     "width":200,
                     "type":"SIGNATURE"
                  }
               ]
            }
         ],
         "name":"Test Document"
      }
   ],
   "name":"Example Package",
   "type":"PACKAGE",
   "language":"en",
   "emailMessage":"",
   "description":"New Package",
   "autocomplete":true,
   "status":"SENT"
}

At the bottom of Creating and Sending a Package guide, we also have a JSON Properties table tells you all optional attributes for payload. Tell me what else I can help! :) Duo

Thank you Duo for such a quick reply. Yes, I want to send HTTP POST request directly from PL/SQL. Can I use the above json content in PLSQL?

Hi Duo, How to check whether the details in json content are reflecting on my account? I am executing below package in PL/SQL and its getting successfully compiled. ------------------------------------------------------ CREATE OR REPLACE PROCEDURE create_package /*( p_errbuf OUT VARCHAR2 ,p_retcode OUT NUMBER )*/ AS l_param_list VARCHAR2(5120); l_http_request utl_http.req; l_http_response utl_http.resp; l_response_text VARCHAR2(32767); NAME VARCHAR2(256); l_header VARCHAR2(512); VALUE VARCHAR2(1024); inst_name VARCHAR2(1024); l_cert_path VARCHAR2(240) := 'xxxx-xxxx-xxxx-xxxx'; l_endpoint_prod_url VARCHAR2(240) := 'https://sandbox.esignlive.com'; l_actions VARCHAR2(240) := 'push_members'; l_api_key VARCHAR2(240) := 'xxxx-xxxx-xxxx-xxxx'; BEGIN SELECT NAME INTO inst_name FROM v$database; utl_http.set_wallet(l_cert_path, 'xxxx'); -- preparing Request...EBSDEV dbms_output.put_line('preparing request for ebsdev'); l_http_request := utl_http.begin_request(l_endpoint_prod_url, 'POST', 'HTTP/1.1'); --dbms_output.put_line ('Error '||SQLERRM ); utl_http.set_header(l_http_request, 'Authorization', l_api_key); -- utl_http.set_header(l_http_request, 'user-agent', 'mozilla/4.0'); utl_http.set_header(l_http_request, 'cache-control', 'no-cache'); utl_http.set_header(l_http_request, 'content-type', 'application/json'); -- sample payload with two signers, one document and one signature for each signer: l_param_list := '{ "action":"push_members", "payload":[ { "roles":[ { "id":"Role1", "signers":[ { "email":"[email protected]", "firstName":"Abcdef", "lastName":"qwerty", "company":"ABC" } ] }, { "id":"Role2", "signers":[ { "email":"[email protected]", "firstName":"Xyzukgtj", "lastName":"asdfgh", "company":"XYZ" } ] } ], "documents":[ { "approvals":[ { "role":"Role1", "fields":[ { "page":0, "top":100, "subtype":"Abcdef qwerty", "height":50, "left":100, "width":200, "type":"SIGNATURE" } ] }, { "role":"Role2", "fields":[ { "page":0, "top":300, "subtype":"Xyzukgtj asdfgh", "height":50, "left":100, "width":200, "type":"SIGNATURE" } ] } ], "name":"Sql script" } ], "name":"Example Package", "type":"PACKAGE", "language":"en", "emailMessage":"", "description":"Test Package", "autocomplete":true, "status":"SENT" } ], "key":"l_api_key"}'; utl_http.set_header(l_http_request, 'Content-Length', length(l_param_list)); -- ...set input parameters utl_http.write_text(l_http_request, l_param_list); -- get Response and obtain received value l_http_response := utl_http.get_response(l_http_request); LOOP utl_http.read_text(l_http_response, l_response_text); dbms_output.put_line(l_response_text); END LOOP; -- user_hook_debug (l_response_text); utl_http.end_response(l_http_response); EXCEPTION WHEN utl_http.end_of_body THEN -- user_hook_debug ('Exceptions'); utl_http.end_response(l_http_response); END create_package; ----------------------------------------------------------- Output: preparing request for ebsdev

302 Found

---

nginx

Hi Tejaswi, For the first glance, could you add "/api/packages" route to your API Url like below?

l_endpoint_prod_url VARCHAR2(240) := 'https://sandbox.esignlive.com/api/packages';

At the same time, I will try the same code at my side. Duo

I have replaced the url with 'https://sandbox.esignlive.com/api/packages' . While executing create_package, it executes successfully but shows the following message in output: "messageKey":"error.unauthorised.noSession","message":"Failed to retrieve Session","code":401,"name":"Unauthorized"}

Hi Tejaswi, 401 error usually refers to the mismatch of your Instance URL and API Key, could we double check them? API Key should be input like "Basic {xxx-xxxx-xxx}", seems you need to add "Basic " as a prefix. Hope this could help! Duo

Yeah. You were right. I added Baisc and now the output is : {"id":"oMfJsKCvZSYwU3U8bffg0-aW5ws="}. Can you please tell me what is the meaning of this ? Also, the file sent via rest api, it goes as encrypted file ? Do we need to add some kind of encryption to ensure that the file sent is safe?

Hi Tejaswi, The "id" attribute in the response refers to the package ID. You probably want to store the package ID at your side for further process. In terms of file encryption, please don't add any encoding and keep it plain binary byte. Because OneSpan Sign system can only recognize files without any encryption for the time being. Duo

I wanted to ask whether the data and file sent via rest api, does it goes as encrypted data or not? Like the above data which i am sending in json. And also needed to know about the file. esign does not recognize encrypted file that is entirely different scenario if at all there is a need to add encryption.

Hi Tejaswi, Sorry for the misunderstand of your question, I apologize for that. If you are talking about the transport layer encryption, all connections between remote endpoints and OneSpan Sign server requires TLS 1.1 or above. All REST Calls below TLS 1.1 will be rejected by OSS and returns an error message. Since you can successfully created a package, which means your HTTP_URL component has already enabled TLS. Here's a documentation describing "dropping support for TLS 1.0 by OSS". Hope this could help! Duo

Hi Duo, Thanks for clarifying the doubts about TLS. Earlier you said that you will be checking the above code at your end. So any update regarding the same? Also, is there any api document available for esign?

Hey there, From your code, it seems your request content type is not multipart/form-data, below is some code I created for you to send the PDF as well as the payload:

set serveroutput on;
create or replace procedure create_package2
as
  oss_api_url constant varchar2(256) := 'https://sandbox.esignlive.com/api';
  oss_api_key constant varchar2(256) := 'your_api_key';

  l_newline varchar2(50) := chr(13) || chr(10);
  lco_boundary constant varchar2(30) := 'gc0p4Jq0M2Yt08jU534c0p';
 
  l_http_request utl_http.req;
  l_request_body clob;
  l_request_body_length number;
 
  l_http_response utl_http.resp;
  l_response_header_name varchar2(256);
  l_response_header_value varchar2(1024);
  l_response_body varchar2(32767);
 
  l_offset number := 1;
  l_amount number := 2000;
  l_buffer varchar2(2000);

  v_blob      blob;
  v_buffer    raw(32767);
  v_file      bfile := bfilename('MYDIR', 'SampleDoc.pdf');
  v_length    integer;
  v_offset    number(15) := 1;
  v_amount    number(15) := 32767;
  
begin

  l_http_request := utl_http.begin_request(
                      url => oss_api_url || '/packages',
                      method => 'POST',
                      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); 
  utl_http.set_header(l_http_request, 'Content-Type', 'multipart/form-data; boundary="' || lco_boundary || '"');
  utl_http.set_header(l_http_request, 'Transfer-Encoding', 'chunked');

  l_request_body := l_newline
|| '--' || lco_boundary || l_newline
|| 'Content-Disposition: form-data; name="file"; filename="file.pdf"' || l_newline
|| 'Content-Type: application/pdf' || l_newline
|| l_newline;

  l_request_body_length := dbms_lob.getlength(l_request_body);

  while l_offset  l_request_body_length loop
    dbms_lob.read(l_request_body, l_amount, l_offset, l_buffer);
    utl_http.write_raw(l_http_request, utl_raw.cast_to_raw(l_buffer));
    l_offset := l_offset + l_amount;
  end loop;

  dbms_lob.createtemporary(v_blob, true, dbms_lob.call);
  dbms_lob.open(v_file, dbms_lob.lob_readonly);
  dbms_lob.open(v_blob, dbms_lob.lob_readwrite);
  v_length := dbms_lob.getlength(v_file);
  dbms_lob.loadfromfile(v_blob, v_file, v_length);


  while v_offset  v_length loop
      dbms_lob.read(v_blob, v_amount, v_offset, v_buffer);
      utl_http.write_raw(l_http_request, v_buffer);
      v_offset := v_offset + v_amount;
  end loop;

  dbms_lob.close(v_file);
  dbms_lob.close(v_blob);

 l_request_body := 
 l_newline
 || l_newline
|| '--' || lco_boundary || l_newline
|| 'Content-Disposition: form-data; name="payload"' || l_newline
|| l_newline
|| '{"name":"transaction created from Oracle","documents":[{"name":"document1"}]}' || l_newline
|| '--' || lco_boundary || '--';
 
  l_request_body_length := dbms_lob.getlength(l_request_body);
  l_offset := 1;

  while l_offset  l_request_body_length loop
    dbms_lob.read(l_request_body, l_amount, l_offset, l_buffer);
    utl_http.write_raw(l_http_request, utl_raw.cast_to_raw(l_buffer));
    l_offset := l_offset + l_amount;
  end loop;
 
  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);
 
  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;
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 create_package2;
/

And BTW, the API reference site is up recently, go check there for REST API reference and JSON schema. Hope this could help! Duo

Hi Duo, Thank you so much for providing the code. What is lco_boundary variable? How to find the value for lco_boundary ? The one which is given in the above code , can i use the same? Also, what about the json content which was there in the code provided by me.

Hi Tejaswi, lco_boundary is for the separator in HTTP request body, official documentation here. You can regenerate a random one or use the same one in my example. In terms of the JSON content provided by you, except the subtype of your signature/field should within "FULLNAME / INITIALS / CAPTURE / MOBILE_CAPTURE / LABEL / TEXTFIELD / TEXTAREA / CHECKBOX / DATE / RADIO / LIST", everything else works fine with the sample code I provided above. Duo

Hi Duo, While sending payload through rest api, my name is being added as recipient. But I have not given any details about me in the JSON. Below is the json content: "name": "Test Package REST", "roles": [ { "signers": [ { "name": "ABC XYZ", "created": "2019-08-10T07:20:18.506Z", "id": "Signer1", "language": "en", "auth": { "scheme": "NONE", "challenges": [] }, "company": "TCS", "updated": "2019-08-10T07:20:18.508Z", "firstName": "ABC", "lastName": "XYZ", "email": "[email protected]", "signerType": "ACCOUNT_SENDER", "timezoneId": "timezoneId" } ], "name": "", "type": "SIGNER", "index": 0, "id": "Role1", "attachmentRequirements": [] } ], "status": "DRAFT", "created": "2019-08-06T07:20:18.508Z", "description": "New Package", "language": "en", "autocomplete": true, "type": "PACKAGE", "due": "2019-08-10T07:20:18.509Z", "visibility": "ACCOUNT", "trashed": false, "notarized": true, "documents": [ { "fields": [ { "name": "", "type": "SIGNATURE", "id": "Filename", "page": 0, "extract": true, "subtype": "" } ], "name": "Filename", "description": "Text file", "index": 0, "extract": true, "tagged": true, "approvals": [ { "name": "Example signature Id", "id": "Signature1", "role": "Role1" } ] } ], "emailMessage": "PFA", "updated": "2019-08-10T07:20:18.510Z", "completed": "2019-08-10T07:20:18.510Z", "bulkSendable": true }

Hi Tejaswi, If you meant that "your sender was added to the "roles" array even if you haven't explicitly mentioned yourself", it's an expected behavior that sender will be automatically added to the recipient list, and there's no method to disable this. If it doesn't affect your workflow, you could leave it as is. Hope this could help! Duo

Yeah. The client has no problem with that. Also, the package create_package2 provided is working perfectly. Thank you so much for helping :-). Can you please explain the code for file sending in create_package2? I have asked this earlier about the lco_boundary variable. But i just needed to know that how did you derived the value for it?

For two questions: (1)elaboration of the file sending: - first I created a v_file object by assigning the path

  v_file      bfile := bfilename('MYDIR', 'SampleDoc.pdf');

- then loaded the file to a blob:

    dbms_lob.createtemporary(v_blob, true, dbms_lob.call);
    dbms_lob.open(v_file, dbms_lob.lob_readonly);
    dbms_lob.open(v_blob, dbms_lob.lob_readwrite);
    v_length := dbms_lob.getlength(v_file);
    dbms_lob.loadfromfile(v_blob, v_file, v_length);

- when building the request body, use dbms_lob.read() function to write certain length of a blob into a v_buffer, then use utl_http.write_raw() function to output the buffer to a plain text:

  
        while v_offset  v_length loop
            dbms_lob.read(v_blob, v_amount, v_offset, v_buffer);
            utl_http.write_raw(l_http_request, v_buffer);
            v_offset := v_offset + v_amount;
        end loop;
	    
        dbms_lob.close(v_file);
        dbms_lob.close(v_blob);

I used three while loops to build the request, where the second one was used to output the file. (2)how did I derive the value for lco_boundary variable: I simply generated a random string token for the boundary, you can use the online tools like this or invoke the built-in PL SQL functions to do so.

Thanks Duo. I am trying to add signature for the document. I added the json for the same. But the sign filed is not reflecting in the attached document. is the below json sufficient for capturing esign? "documents": [ { "fields": [ { "name": "Tejaswi", "type": "SIGNATURE", "id": "Filename", "page": 1, "extract": true, "subtype": "FULLNAME" } ], "name": "TAJGBPAEXT", "description": "Pdf file", "index": 0, "extract": true, "tagged": true, "approvals": [ { "fields": [ { "value": "Tejaswi", "name": "Tejaswi", "type": "SIGNATURE", "id": "Role1", "page": 0, "left": 0, "extract": true, "subtype": "FULLNAME", "width": 0, "top": 0, "height": 0 } ], "name": "Tejaswi", "id": "Tejaswi", "role": "Role1", "optional": true, "enforceCaptureSignature": true, "accepted": "2019-08-14T10:59:49.438Z", "signed": "2019-08-14T10:59:49.438Z" } ] } ],

Hey Tejaswi, To add a signature, you'd add a new node to "approvals" array instead of directly put under the document level, even though the two signatures may be binded to the same signer, you still have to follow this process and add a separate node, below JSON gives you the idea of a basic approval node:

{
   "role":"Role1",
   "name": "approval2_name",
   "id": "approval2_id",
   "fields":[
        {
          "name": "Tejaswi",
          "type": "SIGNATURE",
          "id": "Filename",
          "page": 1,
          "extract": true,
          "subtype": "FULLNAME"
        }
   ]
}

You can refer to the API Specification site for all available attributes in JSON schema. Duo

BTW, I saw that you didn't specify the x/y coordinates for the signature field, were you intending to use Position Extraction feature or any other automatic injection features? Duo

Initially, I gave value as 1 for fields: page, left, width, height, top. When I ran the, HTTP request it gave me an error something like... object is going out of page margin. That's why I made everything 0. Is it because I gave 0, the sign field was not added to the document?

Hi Tejaswi, Below picture gives you an idea of how to locate a field in OSS system: As you noticed, the x/y coordinates is for the top-left corner of the field box. And you have to set a width and height larger than 0 otherwise it won't be displayed in signing ceremony. If initially you received the "out of page margin" error, kindly check whether any part of your signature field has exceed the boundary. (normal 8.5 * 11 inch Word or PDF page is converted to 796*1030px in OSS system) Duo

Hi Duo, Thanks for the information provided for the position of sign field. I am able to sign the document. But now the problem is , I have hard coded the value for time being. What if the position of signature changes in the document to bottom area. My signature field might get overlapped. How to make the sign field dynamic?

Hi Tejaswi, In that case, OneSpan Sign have automatic extraction methods to dynamically set the field position: (1)Document Extraction: use PDF forms and its name to tell OneSpan Sign the size, type and the location of the field (2)Text Tags Extraction: use plain text to tell OSS the size, type and the location of the field (3)Text Anchors Extraction: use existing text as anchors to identify the field position (4)Position Extraction: using existing PDF forms to identify the field position Duo

I tried the text anchors extraction method , but in this we still have to give the value for "topOffset" , which can change. I also tried the position extraction method., but the sign field does not get inserted in pdf document. The other two extraction methods requires PDF Forms. And my PDF is an oracle report (rdf) output. I am sharing you the screenshot of pdf so as to where I have to add the sign field. As I said earlier, the position of 'VP - Materials' can change.

Hi Tejaswi, Thanks for the sharing! If you meant to keep a fixed relative position of signature box and the text "VP – Materials", text anchor could be your choice, because the "topOffset" refers to the offset from the text anchor. See the parameter I was using and the output screenshot:

"extractAnchor": {
                "text": "VP – Materials.",
                "index": 0,
                "width": 100,
                "height": 45,
                "anchorPoint": "TOPLEFT",
                "characterIndex": 0,
                "leftOffset": -5,
                "topOffset": -50
              }