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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.