Oracle Apex save PDF into Database with ORDS?

Question

We have a function that turns sql query into xml and in shared components we have a xsl-fo stylesheet. With both combined we want to generate PDF's and save them into the Database.
Now we tried to use the Print API's (APEX_UTIL.GET_PRINT_DOCUMENT) but the PDF's are damaged and return a 401 Error (Unathorized). We are using Apex 19.1 and as print option ORDS.
Ive read somewhere that you cant use those tho because with ORDS the PDF is only returned to the client.

Since im pretty new to this whole PDF printing subject:

Is there a way to still save the PDF into the Database? Or is this a feature that only comes with the paid solutions? Are there any necessary settings we have to configure with REST?

Enrique Flores Enrique Flores · Accepted Answer · 2020-09-17T15:16:29

You can save the PDF if you can convert it into BLOB, but APEX by itself cannot give you that functionality.

You are right about this "Ive read somewhere that you cant use those tho because with ORDS the PDF is only returned to the client."

For example I created a report in Jasper Server Community and save it into the database using PLSQL

 declare
    l_url   VARCHAR2(3000) := '//apexdevtesting.com:8081/jasperserver/rest_v2/reports/reports/Blank_Letter.pdf?STATUS=';
    l_request   SYS.utl_http.req;
    l_response  SYS.utl_http.resp;
    l_download  RAW(32767);
    l_file      BLOB;
    l_mimetype  VARCHAR2(50) := 'application/pdf';
    l_charset   VARCHAR2(50) := 'UTF8';
    l_filename varchar2(50) := 'archivo_'||ARCHIVOS_CARGADOS_SEQ.nextval||'.pdf';
    
begin
l_request := SYS.utl_http.begin_request(l_url ||'ASSIGNED&'||'j_username=jasperadmin'||'&'||'j_password=jasperadmin' );
                SYS.utl_http.set_header(l_request, 'User-Agent', 'Mozilla/5.0 (Windows NT x.y; Win64; x64; rv:10.0) Gecko/20100101 Firefox/10.0');
                
                l_response := SYS.utl_http.get_response(l_request);
                dbms_lob.createtemporary(l_file, TRUE, dbms_lob.SESSION);
                LOOP
                BEGIN
                    SYS.utl_http.read_raw(l_response, l_download);
                    dbms_lob.writeappend(l_file, utl_raw.LENGTH(l_download), l_download);
                    
                    INSERT INTO j_test2
                    VALUES (SYS_GUID(),
                      SYSTIMESTAMP,
                      l_file,
                      l_mimetype,
                      l_filename);
                    
                    EXCEPTION WHEN SYS.utl_http.end_of_body THEN
                        EXIT;
                    END;
                END LOOP;
                SYS.utl_http.end_response(l_response);
                commit;
end;

Oracle Apex save PDF into Database with ORDS?

2 Answers