Le Forms e le Interactive Grids sono i due oggetti principali che Oracle APEX mette a disposizione per costruire applicazioni che devono essere in grado di caricare e salvare i dati nel database Oracle.

Anche se li conosci e li hai già utilizzati, ti consiglio di leggere comunque questo articolo perché ti darò qualche piccola chicca che sono sicuro ti tornerà utile.

Se invece sei alle prima armi e vuoi imparare come creare con Oracle APEX una applicazione che sia in grado di inserire, modificare e cancellare i record di una tabella del database (ovvero eseguire quelle operazioni che in informatica vengono chiamate CRUD), questo è decisamente l’articolo che fa per te!

Buona lettura.

Prima di Iniziare

Per fare questo esercizio ho deciso di usare un Sample Data Set disponibile direttamente in Oracle APEX.

Puoi installare il Data Set in questo modo:

• Dall’APP Builder di Oracle APEX clicca su SQL Workshop > Utilities > Sample Dataset
• Installa il Data Set chiamato EMP/DEPT

Alla fine dell’operazione avrai a disposizione 3 oggetti nel database

• La tabella DEPT (Table)
• La tabella EMP (Table)
• La vista EMP_DEPT_V (View)

Creiamo una semplice applicazione di demo che useremo per aggiornare i dati dei dipendenti che sono salvati nella tabella EMP.

• Dall’APP Builder clicca su Create APP e seleziona New Application
• Dai un Nome all’applicazione e clicca Create Application

Crea una Form Page basata su una Tabella

Il primo modo che Oracle APEX mette a disposizione per modificare i dati di una tabella è attraverso una Page Form che viene utilizzata insieme ad un Interactive Report.

• Accedi all’APP Builder e clicca Create Page
• Seleziona Interactive Report
• Configura le opzioni base e premi Next
  • Report Name: Employees
  • Include Form Page: Yes
  • Form Page Name: Employee Details
  • Source Type: Table
  • Table / View Name: EMP

• Specifica la Primary Key della tabella su cui vanno salvati i dati e premi Create Page

Come risultato otterrai due pagine APEX:

• Employees: è un Interactive Report che permette di vedere le informazioni della tabella EMP
• Employee Details: è la Form Page che consente di modificare i dati del singolo record

Se provi ad eseguire la Page Preview, dovresti vedere una tabella che mostra tutti i record dalla tabella EMP. Per ciascun record Oracle APEX ha automaticamente creato un bottone Edit.

Cliccando sul bottone Edit di uno qualsiasi dei record si apre una Form che consente di modificare i campi. Per salvare le modifiche devi premere Apply Changes.

Crea una Interactive Grid basata su una Tabella

Una Interactive Grid è un componente di Oracle APEX che consente di modificare i dati di una tabella senza passare da una Form.

• Accedi all’APP Builder e clicca Create Page.
• Seleziona Interactive Grid e premi Next
• Seleziona le opzioni di configurazione seguenti e premi Next
  • Name: Employees Table
  • Source Type: Table
  • Table / View Name: EMP
  • Editing Enabled: Yes

• Specifica la Chiave Primaria e premi Create Page.

In questo caso Oracle APEX creerà una sola pagina con contiene un componente di tipo Interactive Grid.

Se lanci la Page Preview dovresti vedere una tabella all’interno della quale puoi modificare i dati. Quando hai fatto devi premere Save per salvare i dati nel database.

Per aggiungere un nuovo record devi premere Add Row mentre per cancellare un record devi cliccare sul menù Row Actions e poi clicca Delete Row

Premi Save per confermare la creazione o eliminazione del record.

Come usare le Vista o Query SQL nelle Form Page e Interactive Grid

Negli esempi che abbiamo visto sia la Form Page che l’Interactive Grid sono state collegate direttamente ad una tabella del database.

Ma cosa succede se invece utilizzo una Vista oppure una Query SQL?

Facciamo una prova con una Interactive Grid (il concetto è lo stesso anche per una Form).

• Accedi all’APP Builder e clicca Create Page.
• Seleziona Interactive Grid e premi Next.
• Nella sezione Data Source, anziché una tabella, specifica la vista EMP_DEPT_V
• Attiva l’opzione Editing Enabled e premi Next

• Seleziona la Primary Key EMPNO e premi Create Page.

Fin qui, penserai, niente di diverso da quanto già visto, giusto?

Purtroppo per noi, non è così e ce ne accorgiamo subito quando proviamo a modificare un record.

Appare un messaggio abbastanza criptico che recita ORA-01733: virtual column not allowed here

Cosa sta a significare questo errore e, soprattutto, come si risolve?

Vediamolo insieme.

Come correggere l’errore ORA-01733: virtual column not allowed here

L’errore ORA-01733: virtual column not allowed here viene generato da Oracle APEX quando si verificano contemporaneamente queste due condizioni:

• l’Origine Dati è una Vista o una Query SQL che recupera le informazioni da più tabelle contemporaneamente.
• il salvataggio dei dati è gestito tramite un Page Process di tipo Automatic Row Processing

Questo particolare tipo di Page Process è quello che Oracle APEX utilizza di default quando crei una Form o una Interactive Grid usando la procedura guidata (esattamente quello che abbiamo fatto noi).

Puoi controllare com’è fatto questo Page Process nel modo seguente:

• Accedi all’APP Builder ed apri il Page Builder della pagina APEX in cui hai definito l’oggetto Form o IG
• Clicca su Page Processes

• Seleziona il Page Process che nel caso di una IG si chiama Employees – Save Interactive Grid Data

Ti faccio notare che il Process Type è di tipo Interactive Grid – Automatic Row Processing (DML)

In maniera del tutto analoga, per una Form il Page Process creato automaticamente sarà di tipo Form – Automatic Row Processing (DML): il nome è leggermente diverso ma la sostanza non cambia.

Ebbene questi tipi di Page Process funzionano come ci aspettiamo (ovvero salvano i dati nel database) se l’Origine Dati è una tabella e nella rispettiva Form o IG è stata specificato il Page Item che rappresenta la Primary Key della tabella.

Controlliamo, ad esempio, il Page Item PX_EMPNO della Form Page che abbiamo creato pochi minuti fa.

Nella sezione Source, oltre ad aver specificato a quale colonna della tabella è collegata, è indicato che il campo è una Primary Key.

Quando l’Origine Dati, invece, è una Vista (o una Query SQL) non possiamo usare questo tipo di Page Process.

Per risolvere l’errore ORA-01733: virtual column not allowed here dobbiamo gestire il salvataggio dei dati nel database usando un Page Process di tipo PL-SQL.

Come costruire un Page Process di tipo PL-SQL

Un Page Process di tipo PL-SQL è sicuramento il modo più flessibile che hai a disposizione per eseguire nel database tutte le operazioni che servono quando l’utente preme il bottone “Salva”.

Tuttavia, per essere realizzato richiede da parte tua un po’ di dimestichezza con il PL-SQL e, in generale, con le istruzioni SQL del database Oracle.

Se non sai come sviluppare codice in PL-SQL ti consiglio di leggere questa guida che ho scritto per tutti coloro che desiderano imparare ad usarlo.

In ogni caso, non preoccuparti perché ti fornirò io tutte le istruzioni necessarie per completare questo tutorial.

Sei pronto? Iniziamo!

Lo script di base che da cui devi partire per gestire il salvataggio dei dati nel database è il seguente.

begin
    if :apex$row_status = 'D' then 
      -- DELETE the record 
  
    elsif :apex$row_status = 'U' then
   -- UPDATE the record 
    elsif :apex$row_status = 'C' then
      -- INSERT the record 
    end if;
end;

In sostanza, consiste un una concatenazione di istruzioni IF-ELSE per ciascuna delle quali viene valutata una condizione sulla variabile applicativa :apex$row_status

Senza voler scendere troppo nel dettaglio, ti basta sapere che usando questo script PL-SQL, quando premi il bottone “Salva”, Oracle APEX valorizza automaticamente la variabile :apex$row_status con uno tra i valori possibili:

• D quando si cancella un record
• U quando si aggiorna un record
• C quando si crea un record

A questo punto non resta che completare lo script inserendo nei rispettivi blocchi le istruzioni SQL che servono rispettivamente per cancellare, aggiornare e creare un record nella tabella di destinazione.

Trattandosi di uno script PL-SQL puoi ovviamente aggiungere tutte le istruzioni che vuoi per aggiornare anche più tabelle in un colpo solo.

Per passare allo script PL-SQL i parametri della pagina dovrai usare la classica notazione che si usa per fare questo tipo di operazione :<PAGE_ITEM>.

Inoltre ti consiglio di convertire i campi di tipo NUMBER e DATE usando rispettivamente le istruzioni TO_NUMBER e TO_DATE.

Qui sotto trovi l’esempio completo che useremo per gestire il salvataggio dei dati.

begin
    if :apex$row_status = 'D' then 
      -- DELETE the record 
        delete from emp
        where empno = :empno;
    elsif :apex$row_status = 'U' then
   -- UPDATE the record 
        update emp
        set ename = :ename,
            job = :job,
            hiredate = to_date(:hiredate, 'DD-MON-RR'),
            sal = :sal,
            comm = :comm
        where
            empno = :empno;
    elsif :apex$row_status = 'C' then
      -- INSERT the record 
        insert into emp (
            ename, job, hiredate, sal, comm
        ) values (
            :ename, :job, to_date(:hiredate, 'DD-MON-RR'), :sal, :comm
        );
    end if;
end;

Carichiamo in Oracle APEX l’istruzione PL-SQL

• Accedi al Page Builder della pagina APEX contenente l’Interactive Grid e seleziona il campo HIREDATE

• Vai in Appearance e configura il formato DD-MON-RR (o quello che più ti piace, l’importante è usare lo stesso formato nell’istruzione TO_DATE dello script)

• Vai in Processes e seleziona il Page Process Employees – Save Interactive Grid Data

• Cambia Process Type a Execute Code ed incolla nel campo PL/SQL Code lo script PL-SQL che ti ho dato.

Adesso, se provi ad eseguire la pagina, potrai modificare e salvare i dati senza problemi.

Come rendere le colonne di una Interactive Grid non modificabili.

Talvolta è necessario far si che alcune colonne di una IG non siano modificabili dall’utente.

Per raggiungere questo obiettivo ci sono diverse opzioni, vediamone alcune.

La prima consiste nel cambiare l’Item Type del Page Item associato alla colonna che vogliamo rendere non modificabile.

Quello che devi fare è cliccare sul Page Item e cambiarne il tipo a Display Only.

Come effetto otterrai che la relativa colonna dell’Interactive Grid sarà sempre non modificabile

Un altro metodo che puoi usare si basa sulla configurazione della proprietà Read Only.

Rispetto al metodo precedente hai un maggiore grado di flessibilità perché puoi definire delle condizioni personalizzate in base alle quali far scattare o meno l’applicazione della proprietà Read Only.

Facciamo un esempio concreto.

Supponiamo di voler rendere uno specifico campo dell’Interactive Grid non modificabile se ENAME=KING. Possono configurare la proprietà Read Only in questo modo:

• Type: Item = Value
• Item: ENAME
• Value: King
• Execute: For Each Row

L’effetto finale sarà che il campo ENAME (e tutti quelli che desidero) saranno Read-Only solo per quei record che soddisfano la condizione che è stata configurata

Ricorda che le tipologie di condizione che puoi usare sono diverse; ad esempio, il tipo Expression è quella che ti permette di creare condizioni basate su espressioni PL-SQL.

Ecco come costruire la stessa condizione usando una espressione in PL-SQL

Come attivare o disattivare i tipi di Operazioni

L’ultima cosa che voglio spiegarti riguarda un metodo attraverso il quale puoi controllare il tipo di operazioni CRUD che sono ammesse dalla tua Form e, ovviamente, Interactive Grid.

Quello che andrà a spiegarti vale per entrambi gli oggetti. Per comodità userò una IG.

• Accedi al Page Builder e seleziona l’Interactive Grid
• Clicca su Attributes
• Vai alla sezione Edit per attivare o disattivare la possibilità di eseguire le operazioni di INSERT, UPDATE o DELETE

Disattivando, ad esempio, l’opzione Add Row, il bottone per aggiungere una nuova riga non sarà più disponibile

In maniera del tutto analoga puoi disattivare anche la cancellazione o la modifica di un record: la cosa importante che devi sapere è che questa impostazione viene applicata a tutta la griglia.

Oltre a quello che ti ho spiegato, è possibile far si che i tipi di operazione ammessi su un record vengano gestiti dinamicamente.

Supponiamo, a tal proposito, di voler impedire che un utente possa cancellare il record KING. Possiamo costruire questo vincolo usando l’opzione Allowed Row Operations Column.

• Accedi al Page Builder e seleziona l’Interactive Grid
• Vai all’Origine Dati ed inserisci la seguente Query SQL che aggiunge la colonna virtuale ALLOWED_ROW_OPERATION.
  • Se il record è KING allora il valore restituito dalla query sarà U (Update)
  • In tutti gli altri casi restituisce il valore UC (Update & Create)

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO,
       DNAME,
       LOC,
       (case when ENAME = 'KING' then 'U' else 'UD' end) allowed_row_operation
  from EMP_DEPT_V

• Clicca su Attributes e nella sezione Edit configura la proprietà Allowed Row Operations Column come in figura

Ora esegui la pagina. Se apri il menù di riga l’opzione Delete Row sarà disabilitata su quei record per i quali il valore della colonna ALLOWED_ROW_OPERATION è U

Invece per tutti gli altri record sarà abilitata normalmente

Conclusioni

Abbiamo terminato anche questo tutorial, adesso dovresti sapere tutto quello che ti serve per gestire il salvataggio dei dati di una Form o di una Interactive Grid realizzata con Oracle APEX.

Fammi sapere nei commenti se anche tu hai qualche trucchetto nel cassetto su come gestire il salvataggio dei dati nel database, mi farebbe molto piacere.

Al prossimo articolo!

Un abbraccio,

Daniele