Oracle APEX: creare un Report basato su Query Dinamica

Quando crei un report in Oracle APEX, le opzioni più comunemente utilizzate per recuperare i dati da visualizzare sono sostanzialmente due:

• Table / View: i dati vengono presi direttamente dalla tabella o da una vista.

• SQL Query: i dati arrivano da più tabelle, viste o funzioni PL-SQL attraverso una query SQL.

Entrambi i metodi hanno aspetti positivi e negativi.

Per quanto riguarda il primo, il vantaggio principale è decisamente l’immediatezza, soprattutto quando le informazioni sono già organizzate in una vista o in una tabella.

Il secondo metodo, invece, è inevitabile quando i dati sono distribuiti su più oggetti e vanno “messi insieme”.

Comunque sia c’è da dire che nella maggior parte delle casistiche, i metodi citati sopra sono più che sufficienti per implementare la stragrande maggioranza delle applicazioni.

Tuttavia, devi sapere che esiste una terza opzione, un po’ meno famosa forse perché poco immediata, che però è davvero utile: Function Body Returning SQL Query.

In questo tutorial desidero spiegarti come costruire un Report in Oracle APEX basata su una query SQL dinamica, che quindi non è inserita nella definizione del report ma che, invece, viene generata dinamicamente da uno script PL-SQL.

Creare un Report con Query Dinamica

Per fare questo esercizio ho usato 2 tabelle:

• TABLE_FRUIT
• TABLE_VEGETABLES

Quello che vorrei fare è creare una pagina (un Interactive Report) che, in funzione di un parametro scelto dall’utente, mostri i dati recuperati da una tabella piuttosto che da un’altra.

Puoi vedere una applicazione di esempio cliccando a questo link.

A tal proposito ho creato una funzione PL-SQL che accetta in input un parametro (il tipo di oggetto da recuperare) e restituisce in output lo statement SQL che deve essere eseguito per recuperare le informazioni dal database.

Ecco come fare:

• Accedi al Workspace APEX e seleziona SQL Workshop

• Vai in SQL Commands e crea le seguenti tabelle

create table "TABLE_VEGETABLES" (
     "ID"                     number
         generated by default on null as identity minvalue 1 maxvalue 9999999999999999999999999999 increment by 1 start with 1 cache
         20 noorder nocycle nokeep noscale
         not null enable,
     "VEGETABLE_NAME"         varchar2(100 char),
     "VEGETABLE_COLOR"        varchar2(100 char),
     "VEGETABLE_DESCRIPTION"  varchar2(200 char),
     constraint "TABLE_VEGETABLES_ID_PK" primary key ( "ID" )
         using index enable
 )
 /

create table "TABLE_VEGETABLES" (
     "ID"                     number
         generated by default on null as identity minvalue 1 maxvalue 9999999999999999999999999999 increment by 1 start with 1 cache
         20 noorder nocycle nokeep noscale
         not null enable,
     "VEGETABLE_NAME"         varchar2(100 char),
     "VEGETABLE_COLOR"        varchar2(100 char),
     "VEGETABLE_DESCRIPTION"  varchar2(200 char),
     constraint "TABLE_VEGETABLES_ID_PK" primary key ( "ID" )
         using index enable
 )
 /

• Una volta create le due tabelle, compila la seguente funzione PL-SQL: get_dynamic_report_query

create or replace function get_dynamic_report_query (p_param varchar2) 
                    return varchar2 is
    v_sql varchar2(4000);
begin
    if ( p_param = 'VEGETABLES' ) then
        v_sql:= 'select ID, VEGETABLE_NAME NAME, VEGETABLE_COLOR COLOR, VEGETABLE_DESCRIPTION DESCRIPTION from TABLE_VEGETABLES';
    elsif ( p_param = 'FRUITS' ) then
        v_sql:= 'select ID, FRUIT_NAME NAME, FRUIT_COLOR COLOR, FRUIT_DESCRIPTION DESCRIPTION from TABLE_FRUIT';
    else
        v_sql:= 'select NULL ID, NULL NAME, NULL COLOR, NULL DESCRIPTION from DUAL';
    end if;
    return v_sql;
end;

• Crea una nuova pagina APEX di tipo Interactive Report

• Vai alle proprietà dell’Interactive Report e configura le seguenti opzioni
  • Type: Function Body returning SQL Query
  • Language: PL/SQL
  • PL/SQL: inserisci il seguente script

declare
    v_sql varchar2(4000);
 begin
    v_sql := get_dynamic_report_query(:P6_REPORT_SOURCE);
    return v_sql;
 end;

• Questa è la configurazione che dovresti ottenere

• Crea un nuovo Page Item di tipo Select List: P6_REPORT_SOURCE

• Configura il nuovo Page Item in questo modo
  • Name: P6_REPORT_SOURCE
  • Type: Select List
  • List of Values > Type: Static
  • Static Values: FRUITS, VEGETABLES

• Seleziona l’item PX_REPORT_SOURCE e crea una la Dynamic Action

• Proprietà della Dynamic Action
  • Name: change_selection
  • Event: Change
  • Selection Type: Item(s)
  • Item(s): P6_REPORT_SOURCE

• Crea una Action di tipo Refresh che servirà ad aggiornare automaticamente l’Interactive Report quando si seleziona il tipo di dati da visualizzare (FRUITS o VEGETABLES)
  • Action: Refresh
  • Selection Type: Region
  • Region: Dynamic Interactive Report, ossia l’Interactive Report che abbiamo creato precedentemente

• Salva il lavoro ed esegui la Preview della Pagina
• Se selezioni dalla lista valori il tipo dato FRUITS l’applicazione legge i dati dalla tabella TABLE_FRUITS

• Se selezioni dalla lista valori il tipo dato VEGETABLES l’applicazione legge i dati dalla tabella TABLE_VEGETABLES

Conclusioni

L’uso delle Query Dinamiche in Oracle APEX è una funzionalità avanzata molto interessante, soprattutto perché supportata in diversi contesti oltre Interactive Reports.

Puoi creare List Valori, Select List e Interactive Grid e Classic Report.

Fammi sapere cosa ne pensi e ricordati di iscriverti alla pagina Linkedin di APPin5Minuti per ricevere tutti gli aggiornamenti sui contenuti che pubblico periodicamente!

Un abbraccio

Daniele