Come eseguire codice PL-SQL da JavaScript in Oracle APEX

Se stai leggendo questo articolo probabilmente sai cos’è Oracle APEX e sai anche quanto sia semplice da usare per chi desidera creare applicazioni belle e professionali.

Tuttavia, anche se con APEX basta davvero poco sforzo per ottenere risultati notevoli, ci sono situazioni in cui hai bisogno di costruire qualche funzionalità più evoluta oppure specifica per le tue esigenze.

Che fare in queste situazioni?

Beh, devi sapere che, come la stragrande maggioranza dei framework utilizzati per sviluppare applicazioni web, anche Oracle APEX può essere esteso facilmente sia tramite l’uso di plugins ma anche con del codice personalizzato che può essere eseguito direttamente all’interno della tua APP (tramite JavaScript) oppure sul database ORACLE tramite script SQL e PL-SQL.

Insomma, è proprio vero che il limite è la tua fantasia.

In questo articolo desidero spiegarti un tipo di personalizzazione molto semplice da sviluppare ma altresì molto utile, ossia come eseguire codice PL-SQL all’interno di una funzione JavaScript.

Sei pronto? Iniziamo!

Prima di iniziare

Per fare questo esercizio ho creato una semplicissima applicazione basata su Interactive Report + Form che permette di salvare in una tabella del database Oracle dei record (della frutta in questo caso).

Quello che desideriamo fare è estendere l’Interactive Report classico aggiungendo una colonna con un pulsante custom.

Se l’utente preme Elimina Riga, l’applicazione eseguirà una operazione di DELETE; in questo modo non servirà aprire la form per rimuovere il record dal database.

A tal proposito ho creato la procedura PL-SQL delete_fruit che accetta come parametro in input l’ID del record da cancellare e restituisce due parametri in output un codice e un messaggio di uscita.

Come puoi immaginare, questo è un banale pretesto per spiegare come integrare due linguaggi che per definizione sono diametralmente opposti: il PL-SQL che viene eseguito sul database server e il JavaScript che invece è eseguito localmente.

Qui sotto ho riportato gli script di creazione degli oggetti

• Tabella ALL_FRUITS

create table all_fruits (
     id                             number generated by default on null as identity 
                                    constraint all_fruits_id_pk primary key,
     fruit                          varchar2(250 char),
     quantity                       number
 )
 ;

• Procedura DELETE_FRUIT

create or replace procedure delete_fruit (
    p_record_id       in   number,
    p_return_code     out  number,
    p_return_message  out  varchar2
) is
begin
    delete from all_fruits
    where id = p_record_id;
    p_return_code := 0;
    p_return_message := 'Record eliminato correttamente';
exception when others then
    p_return_code := 1;
    p_return_message := 'Impossibile eliminare il record';
end;

Step 1: crea un processo AJAX (AJAX Callback)

• Accedi all’APP Builder di Oracle APEX.
• Apri la definizione della Pagina da modificare

• Vai alla sezione Processing > Ajax Callback

• Clicca con il pulsante destro del mouse e seleziona Create Process.

• Crea un nuovo Process con queste caratteristiche
  • Name: DELETE_FRUIT
  • Type: Execute Code
  • Location: Local Database
  • Language: PL-SQL
  • PL-SQL Code: copia il codice seguente

declare
     lv_error_code  number;
     lv_error_msg   varchar2(4000);
 begin
     /* Questo è il frammento di codice che esegue la procedura PL-SQL */
     for i in 1..apex_application.g_f01.count loop
         delete_fruit(p_record_id => apex_application.g_f01(i), p_return_code => lv_error_code, p_return_message => lv_error_msg);
     end loop;
     
     /* Questo è il frammento di codice che esegue stampa a video un messaggio */
     apex_json.open_object;
     apex_json.open_array('output');
     apex_json.open_object;
     apex_json.write('lv_error_code', lv_error_code);
     apex_json.write('lv_error_msg', lv_error_msg);
     apex_json.close_object;
     apex_json.close_array; 
     apex_json.close_object; 
end;

Ti faccio notare che il processo Ajax che abbiamo scritto restituisce in output una response in formato JSON.

Step 2: crea una funzione JavaScript

• Seleziona il nodo principale della pagina APEX che stai modificando

• Vai alla proprietà Function and Global Variabile Declaration ed inserisci la seguente funzione JavaScript

function deleteFruit(fruit_id)  {
    var lSpinner$ = apex.util.showSpinner();
    /* ajax to call the database procedure */
    apex.server.process("DELETE_FRUIT", 
                        {f01 : fruit_id,
                         pageItems: "#DELETE_ROW"   
                        }, 
                        {success: function(pData) {
                        /* now we can remove the spinner */
                            lSpinner$.remove();
                            /* The Ajax process will return lv_error_msg 
                                            and lv_error_code, if lv_error_code = 0 show the successful message, if not show the error */ 
                            var errorMsg = pData.output[0].lv_error_msg; 
                            if (pData.output[0].lv_error_code == '0') {
                                document.location.reload();
                            } else {
                                apex.message.clearErrors();
                                apex.message.showErrors([{
                                    type: "error",
                                    location: ["page"],
                                    message: errorMsg,
                                    unsafe: false
                                }]);
                            }
                        }});
}

Step 3: crea la colonna Elimina Record

• Seleziona l’Interactive Report

• Vai alla proprietà Source > SQL Query ed aggiungi una nuova colonna virtuale, chiamata DELETE_ROW

• Seleziona la colonna DELETE_ROW

• Configura la colonna DELETE_ROW nel modo seguente:
  • Type: Link
  • Column Heading: Elimina Riga
  • Target Type: URL
  • Target URL: javascript:deleteFruit(#ID#);
  • Link Attributes: class=”t-Button t-Button–simple t-Button–hot t-Button–stretch”

Conclusioni

A questo link puoi vedere l’esempio che ho creato per questo articolo.

Fammi sapere cosa ne pensi e se lo trovi utile, condividilo con i tuoi amici!

Un abbraccio,

Daniele