Current Position:Home > Generate PDF (BI Publisher) through APEX_PLSQL_JOB.SUBMIT_PROCESS

Generate PDF (BI Publisher) through APEX_PLSQL_JOB.SUBMIT_PROCESS

Update:11-30Source: network consolidation
Oracle 32, Apex 4.0.2, BIP 10.x Win.
I have to generate cca. 1000 reports in PDF (some customer bills) through BI Publisher print server.
PDF is defined through "Reports queries" and "Reports layouts" directly defined from Apex env. Report template is Word RTF file. Report query has one parameter:
and R.BILL_ID = :G_BILL_IDRunning from App page process it is not acceptable to do that because operation should takes hours and user cannot have open freezed browser.
So I found out "APEX_PLSQL_JOB.SUBMIT_PROCESS", which submit process in "job" as background process.
On page I left just "SUBMIT_PROCESS" call:
  APEX_PLSQL_JOB.SUBMIT_PROCESS ('begin bills_pkg.generate_biils; end');
END;and put the whole logic in package. Small problem was to change Apex item (Application item), what I solved and change with:
APEX_UTIL.set_session_state(p_name => 'G_BILL_ID', p_value => cur_array(i).B_ID);for every record in cursor (I know his is not optimal but I just wanted to get working demo).
Call from package procedure is like (slightly modified from Apex page process which was working!):
open cur;
  -- assign to have that value as parameter for !??
  APEX_UTIL.set_session_state(p_name => 'G_BILL_ID', p_value => cur_array(i).B_ID);
  print_one_report (cur_array(i).B_ID);
close cur;where "print_one_report" is mine procedure which saves PDF in db (proc was the same as before when was working but with too long result).
Unfortunately then all generated PDF reports (which I save in database) has 0 bytes length (I call the same procedure called as before when reports were generated but from Apex page process directly).
Seems that "G_BILL_ID" is no longer having correct values ... like session problems.
How to achieve and fix that (generate PDF from cursor) or is there any article how to manage BI Publisher printing with APEX_PLSQL_JOB.SUBMIT_PROCESS?
Damir Vadas
apex_custom_auth.define_user_session (p_user, p_session_id );
apex_application.g_flow_id := p_app_id;might help?
Edited by: user1050751 on May 13, 2011 12:57 PM

The Best Answer

Here is one way that you could get a background job to create your thousand reports (4 steps).
1st define your report query and layout within APEX (and BI-Publisher builder - Word plugin).
2nd write a custom process to call apex_util.get_print_document to generate your 1000 reports and place them in a blob column.
    l_document BLOB;
     l_security_group_id NUMBER;
    CURSOR your_cur IS
    SELECT...your thousand records
    l_security_group_id := wwv_flow_api.get_security_group_id;
    FOR your_rec IN your_cur LOOP
             set any item values that will be referenced in your report query here
             l_document := APEX_UTIL.GET_PRINT_DOCUMENT (p_application_id     => v_your_app_id,
                                                         p_report_query_name  => v_your_defined_report_name,
                                                         p_report_layout_name => v_your_defined_layout_name,
                                                         p_report_layout_type => 'rtf',
                                                         p_document_format    => 'pdf',
                                                         --p_print_server             =>
            INSERT INTO your_table... id, l_document, file_name, mime_type...
            WHEN others THEN
                  write an error to a job log...
     WHEN others THEN
           write an error to a job log...
END;3rd write logic that will submit your custom process using APEX_PLSQL_JOB.SUBMIT_PROCESS or you can use dmbs_scheduler package.
4th write code to retrieve the report data from the blob column and display or distribute to users.
I've never called the apex_util.get_print_document recursively like this before so I'm not sure how it will perform. There are 4 versions of the get_print_document API so use the one that best fits your needs with performance in mind.
After writing the above logic I found this post that is very similar.
Scheduling reports with dbms_scheduler
An alternative method to what I've listed above is scheduling reports using the Bi-Publisher web service.
Edited by: tfa on May 16, 2011 8:03 AM