Hello everyone,
I am trying to get PDF file from Apex and trying to send it to an API through NodeJS. When i try to connect my nodejs i am getting:
"
- s_internal_error: false
- ora_sqlcode: -29273
- ora_sqlerrm: ORA-29273: HTTP request failed ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 93 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1500 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2501 ORA-12547: TNS:lost contact ORA-06512: at "SYS.UTL_HTTP", line 591 ORA-06512: at "SYS.UTL_HTTP", line 1206 ORA-06512: at "XXSD_PDF.PKG_PDF", line 104 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190100", line 599 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1476 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC_LOCAL", line 2457 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2503 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2538 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 76 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 1153 ORA-06512: at "APEX_190100.WWV_FLOW_PLUGIN", line 2457
- component.type: APEX_APPLICATION_PAGE_PROCESS
- component.id: 108272539171092548
- component.name: PP_UploadTest
- error_backtrace:
ORA-06512: at "SYS.UTL_HTTP", line 591 ORA-06512: at "SYS.UTL_HTTP", line 1206 ORA-06512: at "XXSD_PDF.PKG_PDF", line 104 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190100", line 599 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 93 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1500 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2501 ORA-06512: at "SYS.UTL_HTTP", line 591 ORA-06512: at "SYS.UTL_HTTP", line 1206 ORA-06512: at "XXSD_PDF.PKG_PDF", line 104 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190100", line 599 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1476 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC_LOCAL", line 2457 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2503 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2538 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 76 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 1153 ORA-06512: at "APEX_190100.WWV_FLOW_PLUGIN", line 2457 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS", line 203 - error_statement:
begin xxsd_pdf.pkg_pdf.p_sendpdf('a','b','c'); end;
"
You may also find my PKG_PDF below:
create or replace PACKAGE BODY PKG_PDF AS
procedure p_sendPDF(v_username varchar2 ,v_password varchar2 , v_apikey varchar2) AS
v_requestjson clob;
v_requestjsonblob blob;
v_url varchar2(1000) := 'http://localhost:3011/PDFConverter';
v_pdfcourl varchar2(1000) := 'https://api.pdf.co/v1/pdf/convert/to/csv';
l_clob clob;
l_buffer varchar2(32767);
l_amount number;
l_offset number;
req utl_http.req;
l_blob BLOB;
l_raw RAW(32767);
eof BOOLEAN;
jsonresponse CLOB; --json;
response utl_http.resp;
v_errmsg CLOB;
v_csvclob clob;
v_csvblob blob;
v_pdfclob clob;
v_pdfblob blob;
v_offset number(15) := 1;
v_value varchar2(1024);
v_amount number(15) := 32767;
v_length integer;
--v_request utl_http.req;
--v_response utl_http.resp;
v_buffer raw(32767);
r_pdfcocalllog xxsd_pdf.pdfcocalllog%rowType;
begin
-- 1. call nodeJS service to get shipment data from Transsmart.
begin
select value
into v_url
from XXSD_ADMIN.PARAMETERS
where code = 'PDFCOURL';
exception
when no_data_found then
return;
end;
SELECT
blob_content
into v_pdfblob
FROM
apex_application_temp_files
WHERE
name = v('P' || v('APP_PAGE_ID') || '_PDF')
AND blob_content IS NOT NULL
AND ROWNUM < 2;
v_pdfclob := xxsd_connect.pkg_import.blob_to_clob(v_pdfblob);
dbms_lob.createtemporary(v_requestjson, true, dbms_lob.call);
v_requestjson := '{
"request": {
"pdfcourl" : "'|| v_pdfcourl || ' ",
"username" : "'|| v_username || ' ",
"password" : "'|| v_password || ' ",
"apikey" : "'|| v_apikey || ' ",
"file" : "'|| xxsd_pdf.UTL_BASE64.encode_base64(v_pdfblob) || ' "
}
}';
--update XXSD_PDF.PDFCOCALLLOG set pdfblob= v_pdfblob where seq = 45;
--commit;
--return;
--dbms_lob.createtemporary(v_requestjson, true, dbms_lob.call);
--dbms_lob.open(v_file, dbms_lob.lob_readonly);
dbms_lob.open(v_requestjson, dbms_lob.lob_readwrite);
--raise_application_error(-20001,length(v_requestjson));
--dbms_output.put_line(v_requestjson);
--v_requestjson := replace(replace(v_requestjson,chr(13),''),chr(10),'');
req := utl_http.begin_request(v_url, 'POST');
v_requestjsonblob := xxsd_connect.pkg_import.clob_to_blob(v_requestjson);
v_length := dbms_lob.getlength(v_pdfblob);
--utl_http.write_text(req, v_requestjson);
begin
utl_http.set_header(req, 'Content-type', 'application/text');
utl_http.set_header(req, 'Content-Length', v_length);
UTL_HTTP.set_header(req, 'Transfer-Encoding', 'Chunked' );
while v_offset < v_length loop
dbms_lob.read(v_pdfblob, v_amount, v_offset, v_buffer);
utl_http.write_raw(req, v_buffer);
v_offset := v_offset + v_amount;
end loop;
end;
--dbms_lob.close(v_file);
dbms_lob.close(v_requestjson);
r_pdfcocalllog.requestdata := v_requestjson;
r_pdfcocalllog.statuscode := 'NOTPROCESSED';
r_pdfcocalllog.requestURL := v_pdfcourl;
insert into xxsd_pdf.pdfcocalllog
values r_pdfcocalllog
returning seq into r_pdfcocalllog.seq;
commit;
begin
response := utl_http.get_response(req);
DBMS_LOB.createtemporary(l_blob, FALSE);
eof := false;
LOOP
EXIT WHEN eof;
BEGIN
UTL_HTTP.read_raw(response, l_raw, 32766);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
EXCEPTION
WHEN utl_http.end_of_body THEN
eof := true;
when OTHERS then
utl_tcp.close_all_connections;
v_errmsg := sqlerrm;
update xxsd_pdf.pdfcocalllog
set statuscode = 'ERROR',ERRORMESSAGE = v_errmsg
where seq = r_pdfcocalllog.seq;
xxsd_admin.pkg_error.p_logerror('xxsd_pdf.PKG_pdf.p_sendPDF',sqlcode,
' Error; ' || sqlerrm
|| ' Back Trace; ' || Dbms_utility.format_error_backtrace);
commit;
END;
END LOOP;
exception when utl_http.end_of_body then
utl_http.end_response(response);
end;
utl_http.end_response(response);
jsonresponse := xxsd_connect.pkg_import.blob_to_clob(l_blob);
dbms_lob.freetemporary(l_blob); -- NEW BLOB *
dbms_output.put_line('response from PDFConverter server : ' || apex_json.get_varchar2(p_path => 'responseCSV'));
apex_json.parse(jsonresponse);
dbms_output.put_line('status from PDFConverter server : ' || apex_json.get_varchar2(p_path => 'status_code'));
v_csvclob := apex_json.get_varchar2(p_path => 'responseCSV');
--v_csvblob := xxsd_connect.pkg_import.clob_to_blob(v_csvclob);
if apex_json.get_varchar2(p_path => 'status_code') = 'OK' then
update xxsd_pdf.pdfcocalllog
set statuscode = 'PROCESSED',responsedata = jsonresponse, csvblob = v_csvblob, csvclob = v_csvclob , pdfblob = v_pdfblob, pdfclob = v_pdfclob
where seq = r_pdfcocalllog.seq;
/*DELETE FROM
apex_application_temp_files
WHERE
name = v('P' || v('APP_PAGE_ID') || '_PDF')
AND blob_content IS NOT NULL
AND ROWNUM < 2;*/
-- get response and parse it using the procedure below.
-- p_parseShipmentXML(apex_json.get_varchar2(p_path => 'response_xml');
else
update XXSD_PDF.PDFCOCALLLOG
set statuscode = 'ERROR',ERRORMESSAGE = apex_json.get_varchar2(p_path => 'status_message')
where seq = r_pdfcocalllog.seq;
/* DELETE FROM
apex_application_temp_files
WHERE
name = v('P' || v('APP_PAGE_ID') || '_PDF')
AND blob_content IS NOT NULL
AND ROWNUM < 2;*/
end if;
END;
END PKG_PDF;
Could you please help me to solve it?