Download Interactive Report in de achtergrond

Bij de klant waar ik momenteel werk wil men de data van meerdere Apex interactive reports (IR) downloaden. Een IR heeft hiervoor een standaard functionaliteit, die over het algemeen prima toereikend is. In dit geval betreft het echter rapporten met soms wel honderdduizenden regels. Mijn eerste reactie hierop was dat dit soort rapportages in een BI-systeem thuis horen, maar de klant wil dit toch met de IR doen.
De applicatie is bereikbaar via IBM Tivoli Access Manager en WebSEAL. Deze WebSEAL heeft een zeer korte time-out periode. Daardoor komt het geregeld voor dat terwijl de data nog wordt opgebouwd aan de serverkant deze time-out periode al voorbij is. Dit resulteert dan in een “no respond error”. Deze time-out periode laten aanpassen blijkt geen optie. Om de downloadfunctionaliteit toch aan te kunnen bieden, heb ik een oplossing bedacht om dit voor elkaar te krijgen.
Daarvoor moeten de volgende stappen genomen worden:

  1. Verzamel IR gegevens.
  2. Creëer Oracle scheduler job om export te maken.
  3. Procedure om csv-bestand aan te maken.
  4. Procedure om csv-bestand op te halen.

Verzamel IR gegevens
Wat is er nodig om het rapport buiten Apex om te kunnen opbouwen?

  • Query van de IR
  • Filtergegevens van het moment van aanvraag
  • ID van de gebruiker die de download aanvraagt

Apex voorziet in diverse API’s. Ook voor IR zijn er een aantal (kijk hier voor alle API’s). Voor het opvragen van de query worden de volgende API’s gebruikt:

  • APEX_IR.GET_LAST_VIEWED_REPORT_ID
    om het ID van het laatst opgevraagde rapport op te vragen
  • APEX_IR.GET_REPORT
    om de metadata van het rapport op te vragen

Op de pagina met het IR is een button gecreëerd die het IR ID ophaalt en dit doorgeeft aan de procedure die met dit ID de overige gegevens kan ophalen.

l_report_id := apex_ir.get_last_viewed_report_id (p_page_id => l_page_id, p_region_id => l_region_id);
ir_package.get_ir(l_page_id, l_region_id, l_report_id, l_report_name);

In de procedure ir_package.get_ir worden de query en filtergegevens opgehaald vervolgens wordt er een Oracle job gestart die zorgt voor de opbouw van het csv-bestand.

  • apex_ir.get_report(p_page_id, p_region_id, p_report_id);
    om de SQL query en bind variabelen (filtergegevens) op te halen en de gegevens in een bruikbaar formaat in een BLOB weg te schrijven
  • dbms_scheduler
    om een program en job aan te maken

De query en bind variabelen worden in een BLOB kolom gezet. Indien er alleen tekst in deze kolom komt kan dit natuurlijk ook in een CLOB kolom gezet worden, met het voordeel dat je geen CLOB-BLOB conversie hoeft uit te voeren. In onze applicatie kunnen er echter ook andere documenten worden weggeschreven. Vandaar de keuze voor een BLOB.
APP_USER_FILES
USF_USR_LOGIN_NAME" VARCHAR2(30 CHAR)
USF_REP_CODE" VARCHAR2(10 CHAR)
USF_FILE_NAME" VARCHAR2(255 CHAR)
USF_FILE_MIMETYPE" VARCHAR2(255 CHAR)
USF_FILE_CHARSET" VARCHAR2(128 CHAR)
USF_FILE_BLOB" BLOB
USF_FILE_COMMENTS" VARCHAR2(4000 CHAR)

Create dbms_scheduler job en program
Het aanmaken van het csv-bestand wordt in de achtergrond uitgevoerd middels de Oracle scheduler. Hiervoor wordt eerst een program aangemaakt met een herkenbare naam en een aantal parameters.
l_user := v('APP_USER');
l_program_name := l_report_name||l_user;
dbms_scheduler.create_program
(program_name => l_program_name
,program_action => 'ir_package.download_ir'
,program_type =>'STORED_PROCEDURE'
,number_of_arguments => 2
,enabled => false);
dbms_scheduler.define_program_argument
(program_name => l_program_name
,argument_position => 1
,argument_type => 'varchar2');
dbms_scheduler.define_program_argument
(program_name => l_program_name
,argument_position => 2
,argument_type => 'varchar2');
dbms_scheduler.enable
(name => l_program_name);

Daarna wordt een job gestart die het daadwerkelijke csv-bestand aanmaakt. Hier wordt de user meegegeven omdat de apex user op het moment dat deze job draait niet meer bekend is. Je wil het rapport natuurlijk wel bij de juiste gebruiker afleveren.
l_job_name := dbms_scheduler.generate_job_name(l_report_name||'_');
dbms_scheduler.create_job
(job_name => l_job_name
,program_name => l_program_name
,comments => 'Create csv '||l_report_name||' for '||l_user
,enabled => false
,auto_drop => true);
dbms_scheduler.set_job_argument_value
(job_name => l_job_name
,argument_position => 1
,argument_value => l_user);
dbms_scheduler.set_job_argument_value
(job_name => l_job_name
,argument_position => 2
,argument_value => l_rep_code);
dbms_scheduler.enable
(name => l_job_name);

Procedure om csv-bestand aan te maken
In deze procedure wordt met behulp van dbms_sql de data opgehaald om het csv-bestand op te bouwen.
Open cursor
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, l_query, dbms_sql.native);

Activeer de bind variabelen
for i in 1..l_para_count
loop

dbms_sql.bind_variable(l_cursor, l_para_name, l_para_value);
end loop;

Open BLOB om het csv-bestand in op te slaan. Let bij het openen van de BLOB dat de cache parameter op TRUE staat. Indien deze op FALSE staat kan het, vooral bij grote rapporten, een grote performance impact hebben.
dbms_lob.createtemporary( l_csv, TRUE );
dbms_lob.open( l_csv, dbms_lob.lob_readwrite );

Definieer de rapport kolommen
dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl );
for i in 1 .. l_col_count loop
dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );
end loop;

Schrijf de kolomkoppen naar de BLOB
for i in 1 .. l_col_count loop
l_col_val := l_desc_tbl(i).col_name;
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10);
else
l_col_val := '"'||l_col_val||'";';
end if;
l_raw := utl_raw.cast_to_raw( l_col_val );
dbms_lob.writeappend( l_csv, utl_raw.length( l_raw ), l_raw );
end loop;

Schrijf de regels naar de BLOB. Om te zorgen dat het csv-bestand goed gelezen kan worden, worden alle kolommen tussen quotes geplaatst. Als scheidingsteken wordt  ;  gebruikt.
l_cursor_status := sys.dbms_sql.execute(l_cursor);
-- write result set to CSV file
loop exit when dbms_sql.fetch_rows(l_cursor) <= 0;
for i in 1 .. l_col_count loop
dbms_sql.column_value(l_cursor, i, l_col_val);
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10);
else
l_col_val := '"'||l_col_val||'";';
end if;
l_raw := utl_raw.cast_to_raw( l_col_val );
dbms_lob.writeappend( l_csv, utl_raw.length( l_raw ), l_raw );
end loop;
end loop;

Sluit cursor en BLOB
dbms_sql.close_cursor(l_cursor);
dbms_lob.close( l_csv );

Schrijf BLOB naar app_user_files
insert into app_user_files( usf_usr_login_name
, usf_rep_code
, usf_file_name
, usf_file_mimetype
, usf_file_charset
, usf_file_blob
, usf_file_comments)
values ( p_user
, l_rep_code
, l_file_name
, 'text/csv'
, null
, l_csv
, 'Ready');

Procedure om het csv-bestand op te halen
Op de “home” pagina van de applicatie staan diverse gebruikers specifieke gegevens. Daar is een rapport toegevoegd die alle beschikbare csv-bestanden voor de ingelogde gebruiker laat zien en die de mogelijkheid biedt om deze te downloaden. De link roept hiervoor de procedure get_user_file aan. In overleg met de klant is de keuze gemaakt om van ieder rapport maximaal één versie te bewaren, hierdoor zal er per rapport maximaal een download beschikbaar zijn.
procedure get_user_file
(p_usf_rep_code in varchar2,
p_usf_file_name in varchar2)
as
l_mime varchar2(2000) ;
l_length number;
l_file_name varchar2 (2000) ;
l_lob blob;
begin
select usf.usf_file_mimetype
, usf.usf_file_blob
, lower(usf.usf_file_name)
, dbms_lob.getlength(usf.usf_file_blob)
into l_mime
, l_lob
, l_file_name
, l_length
from app_user_files usf
where usf.usf_usr_login_name = v('APP_USER')
and usf.usf_rep_code = p_usf_rep_code
and usf.usf_file_name = p_usf_file_name;
/*--*/
/*-- set up HTTP header*/
/*--*/
/*-- use an NVL around the mime type and*/
/*-- if it is a null set it to application/octect*/
/*-- application/octect may launch a download window from windows*/
owa_util.mime_header(nvl(l_mime, 'application/octet'), false) ;
/*-- set the size so the browser knows how much to download*/
htp.p('Content-length: ' || l_length) ;
/*-- the filename will be used by the browser if the users does a save as*/
htp.p('Content-Disposition: attachment; filename="'||l_file_name||'"') ;
/*-- close the headers*/
owa_util.http_header_close;
/*-- download the BLOB*/
wpg_docload.download_file(l_lob) ;
exception
when others then htp.p('Fout'||SQLCODE||' -ERROR- '||SQLERRM);
end get_user_file;

Tenslotte
Ik heb niet de complete code van de procedures in dit artikel gekopieerd, omdat er op meerdere plekken applicatie specifieke zaken gebeuren die niets toevoegen aan dit artikel. Wil je deze oplossing gebruiken, maar kom je er niet uit? Dan mag je mij altijd mailen en zal ik je proberen verder te helpen.