Skip to Main Content
  • Questions
  • Generating and Storing a Report in the Database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Huy.

Asked: July 28, 2020 - 2:29 am UTC

Answered by: Connor McDonald - Last updated: August 04, 2020 - 6:45 am UTC

Category: Application Express - Version: 20.1.0.00.13

Viewed 100+ times

You Asked

>Hi Tom 's team, I am happy for your quick response my old question

https://asktom.oracle.com/pls/apex/asktom.search?tag=storing-and-accessing-reports-in-the-database-on-oracle-apex-of-version-20

I make a new question for my present problem. I listed those steps I created for that problem:

a. I created a table named from file report_archive_tbl.sql on my database or directly on APEX.

b. I created a process named store_blobquery_report when press Print button. Print button redirect to url I created for a report query.

declare
  l_report blob;
begin
  l_report := apex_util.get_print_document (
    p_application_id      => :APP_ID, -- assign id of app or no need
    p_report_query_name   => 'multiquery', -- <b>create name for a report query</b>
    p_report_layout_name  => 'multiquery', --  <b>create name for a report layout</b>
    p_report_layout_type  => 'rtf', -- <b>create a format for a report</b>
    p_document_format     => 'pdf'-- <b>define format for a report<b>
  );
  insert into report_archive (
   filename,
   mimetype,
   report,
   created_date,
   created_by
  ) values (
   'Multi Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')',
   'application/pdf',
   l_report,
   sysdate,
   :USER
  );
end;


c. When I want to click print report, one row were inserted into the database for each report you ran.
. That step while I pressed Print button, it only show that report downloaded but on a table of report_archive had no data.

Thank you in advance ^_^.

and we said...

It might be just a commit ? I did the following without any issue

My dynamic action was:

DECLARE
    l_document BLOB;
BEGIN
   l_document := APEX_UTIL.GET_PRINT_DOCUMENT (
    p_application_id      =>100,
    p_report_query_name   =>'demo',
    p_report_layout_name  =>null,
    p_report_layout_type  =>'xsl-fo',
    p_document_format     =>'xml',
    p_print_server        =>null);

  :P11_new := dbms_lob.getlength(l_document);

insert into report_archive (
   filename,
   mimetype,
   report,
   created_date,
   created_by
  ) values (
   'Multi Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')',
   'application/pdf',
   l_document,
   sysdate,
   :USER
  );
  
  commit;

END;



and then after running it, I got:


SQL> select count(*) from asktom.report_archive;

  COUNT(*)
----------
         1

SQL> select dbms_lob.getlength(report) from asktom.report_archive;

DBMS_LOB.GETLENGTH(REPORT)
--------------------------
                      9202


See in my example I had a quick debug item to show the size of the blob etc. Add some of that into your code to ensure that you are truly generating a blob.

and you rated our response

  (2 ratings)

Reviews

Ask how to run with many values of session state

July 30, 2020 - 7:23 am UTC

Reviewer: Huy Nguyen

Hi Tom' s team,
Thank you for my previous question. I have a harder case,I try my best to fix that but it is not done.

My goal: When press print button, it will be automatically split many pdf files corresponded with for each of class of grade,, grade is a parameter I assign on the page of 241. (Ex: class_id 1 = pdf1, class_id 2 = pdf 2,.. And content of each pdf file is different.

My present problem :After that, when I clicked Print, it showed me a single report includes all of student of class in grade and that was duplicate many times respectively with total number classes in a grade, that not what I expected. And When I checked on DB it run many reports but they have a same report and a value of class_id on DB, it always showed same value of the last id of class in grade( Ex: id = 1,2, 3,4 and then it showed all of value equal were 4) that thing I do not understand. When I checked on session state of page, it got only P241_class_id = 4.


That is my code for Print button:

DECLARE
    l_document BLOB;
    
-- Select list of class in grade
 Cursor c_subs is   
select cl.id  v_cl, cl.name 
from e_edufit_class cl, e_edufit_grade gr
where gr.id = :P241_GRADE
and cl.grade_id = gr.id;

v_subs c_subs%Rowtype;

    Begin
    Open c_subs;
    Loop
    Fetch c_subs
    into v_subs;
    Exit when c_subs%Notfound;
 
-- That statement I want to assign each class of grade for page    
Apex_Util.Set_Session_State('P241_CLASS_ID',v_subs.v_cl);


   l_document := APEX_UTIL.GET_PRINT_DOCUMENT (
    p_application_id      =>1001,
    p_report_query_name   =>'test',
    p_report_layout_name  =>'test',
    p_report_layout_type  =>'rtf',
    p_document_format     =>'pdf',
    p_print_server        =>'f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=test');

insert into report_archive (
   filename,
   mimetype,
   report,
   created_date,
   created_by,
   <b>class_id</b>
  ) values (
   v_subs.name,
   'application/pdf',
   l_document,
   sysdate,
   :USER_ID,
   :P241_CLASS_ID
  );
  end loop;

  commit;

END;


and then, that is code of my report query:

select rownum STT,
       cl.CODE,
       cl.NAME CL,
       cl.DESCRIPTION,
       gr.name GR,
       y.name ,
       cm.id_user Student,

  from E_EDUFIT_CLASS cl, e_edufit_grade gr, e_school_year y, E_EDUFIT_CLASS_MEMBERS cm
  where cl.grade_id = gr.id
  and   cl.SCHOOL_YEAR_ID = y.id
  and  (GRADE_ID = :P241_GRADE or :P241_GRADE is null)
  AND  cm.id_class = :P241_CLASS_ID
And I used session state and select items P241_CLASS_ID



I hope that time I describe my problem more clearly to you. Thank in advance ^_^
Connor McDonald

Followup  

July 31, 2020 - 2:40 am UTC

Have you made sure that you've nominated that session state is to be available for that report ?

Session state on report query

July 31, 2020 - 3:30 am UTC

Reviewer: Huy Nguyen

I make sure you that I enable session state because I check it on page. Eventually, I find out a way to solve my problem about splitting many file pdf by apex_collection. Thanks for your helping. Next time, I hope I will receive many experiences and skills from you
Connor McDonald

Followup  

August 04, 2020 - 6:45 am UTC

Thanks for getting back to us

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.