esign live intergation project with Oracle DB(PL/SQL)

Hi Mathew, Unfortunately, there is no guide on how to integrate esignlive with oracle db. It should be straightforward though, since its a only a question of storing data into a SQL table. If by your question you meant what kind of data you would store into a SQL table, one suggestion I can make is to use the callback event notification listener, register for the package complete event, and store the json payload sent by esignlive. This way, you can query your SQL table for a list of completed packages. It's really up to you on what you wish to store in your SQL table.

Hi Haris, Thanks for your reply. The integration what i mean is to integrate our application with esign line. Our application is running out in Oracle DB and the programming language can be use is pl/sql. Iam totally new on this esignlive and the not aware on the Technical architecture or functional. If you have some idea or do you have some document, how the end to end data flow will work. If we are able to get the flow, its easy to code from the DB side. Regards Mathew

I think this might help you: http://docs.esignlive.com/content/a_esignlive_enterprise/overview_of_esle_6.htm

Hi Haris, I am getting below response after sending the document to the server. I am not sure, whether I am doing the right process or not. {"messageKey":"error.validation.invalidJson","technical":"Unrecognized token 'JVBERi0xLjQNCjEgMCBvYmoNCjw8DQovQ3JlYXRvciAoT3JhY2xlMTFnUjEgQVMgUmVwb3J0cyBTZXJ2aWNlcykNCi9DcmVhdGlvbkRhdGUgKEQ6MjAxNjExMTYxMTE1MDcpDQovTW9kRGF0ZSAoRDoyMDE2MTExNjExMTUwNykNCi9Qcm9kdWNlciAoT3JhY2xlIFBERiBkcml2ZXIpDQovVGl0bGUgKGxvY2FsLnBkZikNCi9BdXRob3IgKE9yYWNsZSBSZXBvcnRzKQ0KPj4NCmVuZG9iag0KNSAwIG9iag0KPDwvTGVuZ3RoIDYgMCBSDQovRmlsdGVyIFsvQVNDSUk4NURlY29kZSAvRmxhdGVEZWNvZGVdDQo': was expecting ('true', 'false' or 'null')\n at [Source: java.io.StringReader@1c33b76d; line: 1, column: 380]","message":"Invalid JSON.","code":400,"name":"Validation Error"} status code: 400 reason phrase: Bad Request regards Mathew

As per the error message, it's expecting a value of true, false, or null. Would you be able to share your exact request?

Dear Haris, Please find below the code which i am trying to do it in my application. 1. p_data_in is the PDF file which i converted to clob 2. I am not sure whether the steps which i am doing is correct or not. Can you please go though and correct me. 3. Do we need to convert the JSON to Encrypted base 64 before submit to http post? Procedure eSignLive_http_post_E ( p_url_in in varchar2 default 'https://sandbox.esignlive.com/api/packages' ,p_data_in in clob ,p_data_type in varchar2 ,p_proxy_in in varchar2 default null ,p_no_proxy_domains_in in varchar2 default null ,p_username_in in varchar2 default null ,p_password_in in varchar2 default null ,v_response out varchar2 ) is l_http_req1 utl_http.req; l_http_resp1 utl_http.resp; l_http_req_pdf utl_http.req; l_http_resp_pdf utl_http.resp; l_my_scheme varchar2(256); l_my_realm varchar2(256); l_my_proxy boolean; vDataIn clob; nStart NUMBER := 1; nEnd NUMBER := 22500; nClobLength NUMBER; vChunkData clob; vChunkData_json clob; nLength NUMBER := 1; l_text VARCHAR2(32767); nSessionId Number; v_json_sign clob; l_basic_base64 varchar2(2000); l_raw RAW(32767); nClobLength_json number; nLength_json number:=1; begin vDataIn := p_data_in; v_json_sign:='{ "roles": [ { "locked": false, "emailMessage": { "content": "" }, "attachmentRequirements": [], "reassign": false, "specialTypes": [], "id": "Sender", "data": null, "type": "SIGNER", "index": 0, "signers": [ { "auth": { "challenges": [], "scheme": "NONE" }, "company": "Silanis", "firstName": "MATHEW", "lastName": "THOMAS", "phone": "", "email": "[email protected]", "knowledgeBasedAuthentication": null, "language": "en", "title": "Silanis", "external": null, "professionalIdentityFields": [], "userCustomFields": [], "delivery": { "email": true, "provider": false, "download": true }, "group": null, "signature": null, "address": null, "data": null, "name": "", "specialTypes": [] } ], "name": "Sender" }, { "locked": false, "emailMessage": { "content": "" }, "attachmentRequirements": [], "reassign": false, "specialTypes": [], "id": "Signer", "data": null, "type": "SIGNER", "index": 0, "signers": [ { "auth": { "challenges": [], "scheme": "NONE" }, "company": "OLAM", "firstName": "SABARI", "lastName": "PRASANNAKUMAR", "phone": "123456", "email": "[email protected]", "knowledgeBasedAuthentication": null, "language": "en", "title": "", "external": null, "professionalIdentityFields": [], "userCustomFields": [], "delivery": { "email": false, "provider": false, "download": false }, "group": null, "id": "Signer", "signature": null, "address": null, "data": null, "name": "", "specialTypes": [] } ], "name": "Signer" } ], "documents": [ { "approvals": [ { "role": "Signer", "signed": null, "accepted": null, "data": null, "fields": [ { "page": 0, "subtype": "FULLNAME", "width": 200, "binding": null, "extract": false, "extractAnchor": null, "left": 175, "top": 165, "validation": null, "height": 50, "data": null, "type": "SIGNATURE", "value": "" } ], "name": "" }, { "role": "Sender", "signed": null, "accepted": null, "data": null, "fields": [ { "page": 0, "subtype": "FULLNAME", "width": 200, "binding": null, "extract": false, "extractAnchor": null, "left": 550, "top": 165, "validation": null, "height": 50, "data": null, "type": "SIGNATURE", "value": "" } ], "name": "" } ], "name": "sampleAgreement" } ], "name": "Test Package REST OLAM", "type": "PACKAGE", "language": "en", "emailMessage": "", "description": "New Package", "autoComplete": true, "status": "SENT" }'; if (p_proxy_in is not null) and (p_no_proxy_domains_in is not null) then utl_http.set_proxy(p_proxy_in, p_no_proxy_domains_in); End if; utl_http.set_wallet('file:D:\app\Administrator\product\11.2.0\dbhome_1\BIN\owm\wallets', 'MyPass123'); utl_http.set_response_error_check(FALSE); Utl_Http.set_detailed_excp_support (TRUE ); l_basic_base64:='MyAPIKey'; nClobLength := length(vDataIn); nClobLength:=nClobLength+length(v_json_sign); l_http_req1 := utl_http.begin_request (p_url_in, 'POST',UTL_HTTP.HTTP_VERSION_1_1); utl_http.set_header (l_http_req1,'Authorization','Basic '||l_basic_base64); utl_http.set_header(l_http_req1, 'User-Agent', 'Mozilla/4.0'); utl_http.set_header(l_http_req1, 'content-type', 'application/json; esl-api-version=11.0; charset=UTF-8'); utl_http.set_header(l_http_req1, 'content-disposition', 'form-data; name="fname"; filename="olamcontracts.pdf"' ); utl_http.set_header(l_http_req1, 'X-Atlassian-Token', 'nocheck'); utl_http.set_header(l_http_req1, 'content-length', nClobLength); utl_http.set_header ( l_http_req1, 'Transfer-Encoding', 'chunked' ); if nClobLength = 32767 then utl_http.set_header(l_http_req1, 'content-length', nClobLength); UTL_HTTP.WRITE_text ( l_http_req1, vDataIn); else LOOP vChunkData := SUBSTR(vDataIn, nLength, 22500); UTL_HTTP.WRITE_TEXT ( l_http_req1,vChunkData); if nLength nClobLength then nLength:=nLength+22500; else exit; end if; End Loop; End if; nClobLength_json := length(v_json_sign); LOOP vChunkData_json := SUBSTR(v_json_sign, nLength_json, 22500); UTL_HTTP.WRITE_TEXT (l_http_req1,vChunkData_json); if nLength_json nClobLength_json then nLength_json:=nLength_json+22500; else exit; end if; End Loop; l_http_resp1 := utl_http.get_response (l_http_req1); UTL_HTTP.read_text(l_http_resp1, l_text, 32766); v_response := l_text; DBMS_OUTPUT.put_line (l_text); dbms_output.put_line ('status code: ' || l_http_resp1.status_code); dbms_output.put_line ('reason phrase: ' || l_http_resp1.reason_phrase); dbms_output.put_line ('reason phrase1: ' || Utl_Http.get_detailed_sqlerrm); utl_http.end_response (l_http_resp1); Exception when others then dbms_output.put_line (sqlerrm); raise; End eSignLive_http_post_E;

Hi Mathew, Have a look at our quick start guide on how to create and send a package with REST. It should give you a better idea on how to send a request to esignlive. You do not need to convert the JSON to base64 or the PDF document to clob. Basically, your HTTP POST request should look something like this:

POST /api/packages/ HTTP/1.1
Host: sanbox.esignlive.com
Connection: keep-alive
Content-Length: 80357
Accept: application/json
Authorization: Basic your_api_key
Content-Type: multipart/form-data; boundary=----WebKitFormBoundarycywBKPMXcPHApu4C

------WebKitFormBoundarycywBKPMXcPHApu4C
Content-Disposition: form-data; name="file"; filename="doc1.pdf"
Content-Type: application/pdf

%PDF-1.5
%µµµµ
1 0 obj
 >>>
endobj....

------WebKitFormBoundary7MA4YWxkTrZu0gW
Content-Disposition: form-data; name="payload"

{"name" : "document1"}
------WebKitFormBoundary7MA4YWxkTrZu0gW--

The first part of the multipart/form-data is the PDF binary and the second part is the json payload. Hope this helps :) https://developer.esignlive.com/guides/quick-start/creating-and-sending-a-package-rest/