Essere in grado di cercare le informazioni di interesse in modo facile e veloce non è un bisogno che va sottovalutato quando si crea una applicazione web.

Questa necessità è vera a maggior ragione per le applicazioni gestionali che basano il loro funzionamento sui dati salvati all’interno di un database.

In un CRM, ad esempio, bisogna cercare i clienti partendo dal loro nome mentre in un Document Management System si cercano files e documenti (opportunatamente archiviati e catalogati) in funzione del loro contenuto e del tipo di documento.

Per questo motivo è importante saper creare moduli di ricerca attraverso i quali sia possibile interrogare i dati presenti nel database partendo dalle parole chiave (dette anche keywords) e sfruttando, prima di tutto, il linguaggio SQL.

Oracle APEX mette a disposizione degli sviluppatori numerosi metodi per costruire moduli di ricerca belli e funzionali.

In questo articolo ti mostrerò 5 diversi modi per implementare la ricerca e la visualizzazione dei dati in una applicazione web realizzata con Oracle APEX.

Partiremo da quelli più semplici ed immediati basati sui componenti standard di Oracle APEX (Faceted Search e Smart Filters) fino ad arrivare a costruire un piccolo motore di ricerca che implementa la Fuzzy Search.

Buona lettura!

Prima di Iniziare

Nei prossimi paragrafi ti spiegherò alcuni dei metodi principali per costruire della maschere di ricerca in Oracle APEX.

Se lo desideri puoi applicare questi metodi fin da subito usando i tuoi dati, in alternativa puoi installare un dataset di demo disponibile sempre in Oracle APEX.

Per installare il dataset di demo che ho usato per scrivere questo tutorial segui le seguenti istruzioni.

• Collegati ad Oracle APEX e vai in SQL Workshop > Utilities > Sample Datasets

• Installa uno dei dataset disponibili. Ad esempio io ho usato EMP/DEPT

Faceted Search

La Faceted Search (traducibile con Ricerca a Faccette) è un metodo di ricerca utilizzato moltissimo nell’ambito degli e-commerce.

Un esempio? Amazon, ovviamente.

Questo tipo di ricerca permette all’utente di filtrare un elenco indefinito di oggetti a seconda dei parametri che vengono applicati dall’utente stesso.

Ad esempio, se stiamo visitando la categoria dopobarba e sono interessato a visualizzare soltanto i prodotti di una certa marca, agirò sui filtri a sinistra in maniera tale da visualizzare solo i risultati che mi interessano.

Per implementare in Oracle APEX questo tipo di ricerca esiste un componente specifico, chiamato appunto Faceted Search.

• Accedi all’APP Builder
• Clicca Create Page > Faceted Search

• Seleziona la Vista EMP_DEPT_V e premi Next

• Seleziona le colonne per le quali vuoi creare un filtro. Ti consiglio di escludere quelle che potrebbero avere troppi valori diversi (nel mio caso le colonne ENAME e MGR che rappresentano rispettivamente il nome del dipendente e del relativo responsabile)
• Clicca Create Page

Il risultato è una pagina che contiene un Classic Report e a sinistra una serie di filtri preconfigurati.

Usando il campo di ricerca e i filtri (di tipo checkbox) che sono stati generati automaticamente è possibile filtrare i record del report.

Oltre ai filtri creati automaticamente ne puoi aggiungere di nuovi. Ad esempio possiamo creare un Facet per filtrare gli impiegati in base al loro stipendio annuale.

• Dall’APP Builder seleziona la region di tipo Faceted Search e crea un nuovo Facet di nome PX_SALARY di tipo Range

• Seleziona il Facet ed imposta le seguenti proprietà:
  • Database Column: SAL
  • Data Type: NUMBER

• Configura la seguente Lista Valori di tipo Static

Ora possiamo filtrare i record in funzione della fascia di stipendio.

Ovviamente possiamo costruire anche filtri di tipo Range basati sulle date; l’unica accortezza con gli intervalli temporali è quella di usare la notazione canonica (YYYYMMDDHH24MISS) per definire i vari ranges.

Ecco il risultato:

Per maggiori dettagli sulle caratteristiche di questo componente puoi leggere i seguenti articoli:

• Faceted Search in Oracle APEX
• Faceted Search Enhancements in APEX 20.2

Smart Filters

Si tratta di un componente introdotto in Oracle APEX da relativamente poco tempo (rel. 21.2) e che, come per le Faceted Searches, fornisce agli utenti un modo molto semplice ed intuitivo per trovare quello di cui hanno bisogno.

L’aspetto grafico dello Smart Filters può trarti in inganno perché si presenta come una semplice barra di ricerca attraverso la quale poter eseguire una ricerca full-text su tutti i campi del report.

Tuttavia basta poco per rendersi conto che è possibile utilizzare dei suggerimenti di ricerca, cuore degli Smart Filters, che vengono visualizzati automaticamente mentre scrivi.

Ad esempio, nella figura sotto ti sto facendo vedere come puoi applicare più filtri contemporaneamente (un comportamento molto simile a quello che abbiamo visto con le Faceted Searches)

Infine è presente una sezione, posizionata sotto la barra di ricerca, che si chiama Suggestion Chips e che propone automaticamente dei filtri predefiniti e adattati al set di dati.

Vediamo come creare una pagina di ricerca basata su Smart Filters.

• Dall’APP Builder clicca su Create Page e seleziona Smart Filters

• Specifica la vista EMP_DEPT_V e clicca Next
• Possiamo indicare subito quali Smart Filters creare. Esattamente come visto per le Faceted Searches, conviene escludere le colonne che hanno un elevano numero di valori diversi (nel mio caso le colonne ENAME e MGR).
• Seleziona il layout Report, premi Create Page ed il gioco è fatto!

Se diamo una occhiata alla struttura della pagina ci rendiamo subito conto che va configurata in modo molto simile a quanto visto nel paragrafo precedente per le Faceted Search.

In effetti possiamo dire che hanno molti elementi in comune.

Ad esempio, anche per gli Smart Filters posso creare un filtro di tipo Range per cercare i dipendenti in funzione della fascia di stipendio

Ecco il risultato:

Per approfondire ulteriormente l’uso degli Smart Filters ti suggerisco di leggere l’articolo Smart Filters in Oracle APEX 21.2.

Interactive Report e Interactive Grid

L’Interactive Report (o Report Interattivo) è uno dei componenti fondamentali di Oracle APEX, utilissimo nell’ambito delle applicazioni gestionali.

Configurare un Interactive Report richiede da parte tua davvero pochissimo sforzo ma offre tantissimo in termini di funzionalità out-of-the-box.

Gli utenti possono personalizzarne l’aspetto scegliendo le colonne da visualizzare, possono aggiungere filtri ed ordinamenti avanzati, definire regole di formattazione condizionale, creare delle colonne formula, costruire pivot, grafici e molto altro.

A tal proposito, c’è da dire che molte di queste caratteristiche non sono alla portata di tutti e c’è il rischio che utenti poco esperti potrebbero non sfruttarle a dovere.

Questo è sicuramente uno dei motivi che lo rendono un componente che vale la pena usare ma che, in un certo senso, va spiegato.

In questo articolo ti mostro come costruire un Interactive Report e come usare le sue funzionalità principali.

Report con Parametri

Può capitare che tu abbia la necessità di creare dei report parametrizzati, ad esempio, quando vuoi aiutare l’utente a lavorare su un set specifico di record piuttosto che su tutto il dataset.

Per costruire un report con parametri in Oracle APEX partiamo da un normale Classic Report (ma potrebbe essere anche un Interactive Report o una Interactive Grid)

• Accedi all’APP Builder
• Clicca Create Page e scegli l’opzione Classic Report

• Seleziona anche in questo caso la vista EMP_DEPT_V e premi Create Page

Arrivati a questo punto avremo a disposizione un normalissimo Classic Report.

• Accedi al Page Designer e seleziona il Classic Report appena creato

• Crea un nuovo Page Item di nome PX_ENAME

• Aggiungi al Page Item PX_ENAME una Dynamic Action di tipo Key Release

• Aggiungi una Action di tipo Refresh per aggiornare il report Employees

• Questo sarà il risultato finale

• Seleziona il Classic Report e modifica le seguenti proprietà che trovi sotto la sezione Source
  • Where Clause: (instr(trim(upper(ename)), trim(upper(:PX_ENAME)))>0 or :PX_ENAME is null)
  • Page Items to Submit: PX_ENAME

Come risultato otterrai qualcosa di simile alla figura seguente:

Scrivendo una parola chiave il Classic Report si aggiornerà automaticamente filtrando i record che nella colonna ENAME contengono la parola chiave.

Possiamo rendere i parametri del nostro report più interattivi grazie ad un Text Item With Autocomplete che suggerirà all’utente quale valore scegliere.

Fuzzy Search con Oracle Text

Fuzzy Search è una tecnica di ricerca testuale che permette di trovare parole simili oltre alle corrispondenze esatte per il termine di ricerca.

La ricerca fuzzy può essere molto efficace perché, sebbene possa dare risultati di ricerca meno esatti, può restituire informazioni rilevanti che non sarebbero state prese in considerazione da un meccanismo di ricerca eccessivamente rigido.

Cerco di spiegarmi meglio.

Supponiamo di voler costruire nel nostro CRM una funzionalità di ricerca dei clienti.

Una ricerca classica prevede che l’utente inserisca una parola chiave di ricerca esatta affinché possa essere trovato qualche risultato.

Pertanto, se il cliente che vogliamo trovare si chiama ACME S.P.A non potrà mai essere trovato se l’utente lo cerca usando la parola chiave ACME SPA (ti faccio notare che non ho inserito i puntini).

Mentre la ricerca rigida restituirà solo record che al massimo contengono la parla ACME S.P.A., una ricerca fuzzy eseguirà automaticamente una interrogazione per termini simili restituendo effettivamente dei risultati utili.

Per effettuare una Fuzzy Search utilizzeremo Oracle Text.

Parliamo di Oracle Text

Oracle Text è una funzionalità del database Oracle e che consente di creare applicazioni in grado di effettuare ricerche testuali avanzate.

In un certo senso potremmo definire Oracle Text un vero e proprio motore di ricerca in grado di classificare e indicizzare documenti (come stringhe e campi di testo).

Prima di tutto è bene verificare che Oracle Text sia correttamente attivato.

Un modo fare qualche primo esperimento con Oracle Text è attraverso l’applicazione Sample Interactive Grid (è una delle Sample APPs messe a disposizione da APEX)

• Dopo averla installata vai in Reporting > Oracle Text
• Se leggi il messaggio “Oracle Text is not available” significa che è necessaria qualche attività preliminare.

• L’applicazione mi suggerisce di contattare l’amministratore di sistema e di assegnare il ruolo CTXAPP all’utente del Workspace APEX che sto usando (che nel mio caso si chiama WKSP_APPIN5MINUTIDEMO) usando la seguente istruzione SQL, da eseguire con un utente del database che abbia il profilo ADMIN.

grant CTXAPP to <WORKSPACE_NAME>;

• Dopo aver fatto logout ed essermi autenticato nuovamente nell’applicazione leggo un messaggio diverso. Senza troppi indugi clicca sul bottone Create Oracle Text Index (anche se per il momento questi passaggi non ti sono chiari, non preoccuparti. Ne parleremo tra pochissimo)

Dopo qualche secondo la pagina dovrebbe aggiornarsi mostrando una semplice Interactive Grid ma che consente di cercare i dati usando una ricerca fuzzy.

Proviamo, ad esempio a cercare “gustav eifel“.

Nonostante il nome sia palesemente sbagliato, Oracle APEX (o forse sarebbe meglio dire Oracle Text) è stato comunque in grado di trovare un risultato utile e per fare questo non è stato necessario usare una query complicata.

Ora che abbiamo una idea più chiara di cosa sia una Fuzzy Search vediamo come usare Oracle Text in una applicazione APEX.

A differenza delle volte precedenti, quello che andrò a spiegarti da qui in avanti potrebbe risultare un po’ complesso, dovrò usare qualche termine più tecnico e dovremo sporcarci le mani.

Non spaventarti, ti guiderò passo dopo passo.

Come negli esempi precedenti, utilizzeremo sempre le nostre tabelle EMP e DEPT.

Come funziona Oracle Text

Quindi, come avviene la magia?

Senza volerci addentrare troppo negli aspetti più tecnici di Oracle Text, devi sapere che il suo funzionamento è possibile grazie ad un particolare database index, chiamato appunto Oracle Text Index.

Questa è la sintassi per definire sulla tabella EMP un semplice Oracle Text Index che indicizza il contenuto della colonna ENAME

CREATE INDEX emp_textindex_1
  ON emp(ename) indextype IS ctxsys.context parameters('sync (on commit)');

Possiamo utilizzare subito questo indice nella nostra applicazione APEX.

• A tal proposito creiamo una Interactive Grid collegata alla tabella EMP.
• Dopodiché, configuriamo la proprietà Oracle Text Index Column in questo modo:

Avviamo la nostra applicazione e proviamo a cercare il record KING usando la ricerca globale. Ecco il risultato:

Fino a qui direi abbastanza scontato visto che esiste un record con ENAME=’KING’. Ora proviamo ad eseguire una ricerca per parola chiave KONG.

Stranamente non otteniamo alcun risultato..

Ora proviamo a scrivere la seguente chiave di ricerca ?KONG (si, metti un punto interrogativo prima della parola chiave).

Abbiamo ottenuto un risultato!

In effetti Oracle APEX ha effettuato una Fuzzy Search ed è stato in grado di trovare un record con una parola chiave simile a quella che abbiamo inserito.

A questo punto ti chiederai…perché mettere quel punto interrogativo prima della parola chiave?

Devi saper che per usare Oracle Text non è sufficiente creare un indice di database ma vanno usati specifici operatori insieme alla clausola contains.

Ad esempio, per eseguire una query usando il metodo Fuzzy Search, bisogna usare l’operatore ‘?’.

Ecco un esempio:

select * 
  from emp 
 where contains (ename,'?kong')>0

Quando facciamo una ricerca usando l’IG, Oracle APEX aggiunge automaticamente la clausola contains (ename,’?kong’)>0

Se anziché usare l’operatore ‘?‘ ci limitassimo a scrivere la parola ‘kong’ non otterremmo alcun risultato perché non stiamo dicendo ad Oracle Text che tipo di ricerca eseguire.

La Fuzzy Search è solo uno dei metodi di ricerca che puoi usare con Oracle Text.

Ad esempio puoi effettuare una ricerca su più parole chiave usando gli operatori booleani.

Se volessi trovare tutti i record che contengono la parola KING oppure MARTIN posso usare l’operatore ‘OR’

Per scoprire le potenzialità di Oracle Text puoi dare una occhiata a questo articolo: Querying with Oracle Text.

Come attivare la Fuzzy Search in una Interactive Grid

Ora che abbiamo una idea di cosa sia Oracle Text e come funziona, la domanda nasce spontanea..

Come possiamo sfruttare Oracle Text in una applicazione APEX?

Infatti, non possiamo chiedere all’utente di usare gli operatori di Oracle Text per fare una ricerca.

Come abbiamo visto nell’applicazione Sample Interactive Grid, quello che dovremo fare è creare una form di ricerca dove

1. l’utente inserisce una parola chiave senza indicare alcun operatore specifico
2. l’applicazione eseguirà una Fuzzy Search e visualizzerà all’utente i risultati.

Inoltre, la nostra ricerca non deve basarsi su una singola colonna (come nell’esempio che abbiamo fatto poco fa) ma deve prendere in considerazione più colonne, provenienti anche da più tabelle (EMP e DEPT).

Per ottenere questo obiettivo dovremo costruire prima di tutto un Oracle Text Index un po’ più complesso di quello visto poco fa.

In sostanza quello che faremo sarà creare sulla colonna ENAME della tabella EMP un Oracle Text Index che indicizza dei dati e delle informazioni che arrivano da colonne e tabelle diverse.

Da qui un avanti vedremo come creare un Oracle Text Index che potremmo definire di tipo avanzato che utilizza alcuni oggetti di supporto come i Datastore, i Sectioners e i Lexers.

Questi oggetti vengono definiti per configurare l’indice e specificare alcune sue caratteristiche come, ad esempio, quali colonne e tabelle indicizzare e se il contenuto da indicizzare è scritto in una lingua specifica (italiano, inglese, ecc).

Prima di tutto partiamo dal compilare una store procedure PL-SQL che verrà usata da Oracle Text per recuperare i dati da indicizzare.

• Accedi ad Oracle APEX e vai in SQL Workshop > SQL Commands
• Compila la seguente procedura PL-SQL che verrà usata da Oracle Text per salvare i dati che desideriamo indicizzare in un XML.

create or replace procedure employee_search_upd_proc(
        rid     in rowid,
        tlob    in out nocopy varchar2 )
is
    l_emp_name emp.ename%type;
    l_emp_job  emp.job%type;
    l_emp_dep  dept.dname%type;
    l_emp_loc dept.loc%type;
begin
    select e.ename,e.job,d.dname,d.loc
      into l_emp_name, l_emp_job, l_emp_dep, l_emp_loc
      from emp e,dept d
     where e.deptno = d.deptno (+)
       and e.rowid = rid;
    tlob := 
        '<ENAME>' || l_emp_name || '</ENAME>' ||
        '<JOB>'   || l_emp_job  || '</JOB>'   ||
        '<DNAME>' || l_emp_dep  || '</DNAME>' ||
        '<LOC>'   || l_emp_loc  || '</LOC>';
        
end employee_search_upd_proc;

Adesso andremo a creare quelle che in gergo si chiamano preferences, ossia delle impostazioni che ci servono per dire ad Oracle Text come lavorare.

• Datastore: specifica la store procedure da usare per indicizzare i dati (usa l’istruzione drop_preference se desideri eliminare la preference)

begin
    ctx_ddl.create_preference(
        preference_name => 'emp_ds',
        object_name     => 'user_datastore'
    );
    ctx_ddl.set_attribute (
        preference_name => 'emp_ds',
        attribute_name  => 'procedure',
        attribute_value => 'WKSP_APPIN5MINUTIDEMO.EMPLOYEE_SEARCH_UPD_PROC'
    );
end;
/*
begin
    ctx_ddl.drop_preference(
        preference_name => 'emp_ds'
    );
end;
*/

• Sectioner: definisce i section groups, ovvero i nodi dell’xml in cui verranno organizzate le informazioni indicizzate. Definire i section groups è utile perché ci permette di fare interrogazioni avanzate su specifici attributi.

begin
    ctx_ddl.create_section_group(
        group_name => 'emp_sg',
        group_type => 'XML_SECTION_GROUP'
    );
    ctx_ddl.add_field_section ('emp_sg','ENAME', 'ENAME', true);
    ctx_ddl.add_field_section ('emp_sg','JOB',   'JOB',   true);
    ctx_ddl.add_field_section ('emp_sg','DNAME', 'DNAME', true);
    ctx_ddl.add_field_section ('emp_sg','LOC',   'LOC', true);
end;
/*
begin
    ctx_ddl.drop_section_group(
        group_name => 'emp_sg'
    );
end;
*/

• Lexer: definisce altre impostazioni di ricerca come, ad esempio, il fatto che non sia case sensitive (MIXED_CASE=NO) e che funzioni usando le lettere base dell’alfabeto (quindi la parola würstel verrebbe trovata anche usando chiave di ricerca wurstel).

begin
    ctx_ddl.create_preference('emp_lx', 'BASIC_LEXER');
    ctx_ddl.set_attribute ('emp_lx','MIXED_CASE',       'NO');
    ctx_ddl.set_attribute ('emp_lx','BASE_LETTER',      'YES');
    ctx_ddl.set_attribute ('emp_lx','BASE_LETTER_TYPE', 'GENERIC');
end;
/*
begin
    ctx_ddl.drop_preference('emp_lx');
end;
*/

Ora possiamo finalmente creare il nostro indice usando lo script seguente

create index ft_emp on emp(ename) 
indextype is ctxsys.context 
parameters('
    datastore       emp_ds
    section group   emp_sg
    lexer           emp_lx
    sync (on commit)');

Se adesso riapri la tua applicazione APEX e provi a testare la Fuzzy Search, vedrai che funziona non solo con la colonna ENAME ma anche con le colonne JOB, DNAME, MGR e LOC

Bene, quello che manca a questo punto è evitare di usare gli operatori di Oracle Text per eseguire una Fuzzy Search.

Quello che faremo sarà creare una store procedure PL-SQL che convertirà ciò che l’utente inserisce nel campo di ricerca in una stringa che sia comprensibile ad Oracle Text.

create or replace function employee_search_convert_text_query( p_enduser_query in varchar2 ) return varchar2 
    is 
        l_tokens       apex_application_global.vc_arr2;
        l_set_boolean  boolean := false;
        c_xml constant varchar2(32767) := '<query><textquery><progression>' ||
                                            '<seq>#NORMAL_AND#</seq>' ||
                                            '<seq>#FUZZY_AND#</seq>' ||
                                            '<seq>#FUZZY_OR#</seq>' ||
                                          '</progression></textquery></query>';
        l_textquery    varchar2(32767) := '';
 
        function generate_query( p_feature in varchar2, p_combine in varchar2) return varchar2 is
            l_query        varchar2(32767);
            l_clean_token  varchar2(100);
        begin
            for i in 1..l_tokens.count loop
                l_clean_token := lower( regexp_replace( l_tokens( i ), '[<>{}/()*%&!$?.:,;\+#]', '' ) );
                if ltrim( rtrim( l_clean_token ) ) is not null then
                    if p_feature = 'FUZZY' then
                        l_query := l_query || 'FUZZY({' || l_clean_token || '}, 50, 500) ';
                    elsif p_feature = 'WILDCARD_RIGHT' then 
                        l_query := l_query || l_clean_token || '% ';
                    else 
                        l_query := l_query || '{' || l_clean_token || '} ';
                    end if;
                    if p_combine = 'OR' then 
                        l_query := l_query || ' or ';
                    else 
                        l_query := l_query || ' and ';
                    end if;
                end if;
            end loop;
            if p_combine = 'AND' then
                l_query := substr( l_query, 1, length( l_query ) - 5 );
            else
                l_query := substr( l_query, 1, length( l_query ) - 4 );
            end if;
            return ltrim( rtrim( l_query )); 
        end generate_query;
    begin
        if substr( p_enduser_query, 1, 8 ) = 'ORATEXT:' then
            return substr( p_enduser_query, 9 );
        else 
            l_textquery := c_xml;
            l_tokens := apex_util.string_to_table( p_enduser_query, ' ' );
    
            l_textquery := replace( l_textquery, '#NORMAL_AND#', generate_query( 'NORMAL', 'AND' ) );
            l_textquery := replace( l_textquery, '#FUZZY_AND#', generate_query( 'FUZZY', 'AND' ) );
    
            return l_textquery;
        end if;
    end employee_search_convert_text_query; 

Per attivare l’utilizzo di questa funzione esegui i seguenti passaggi:

• Accedi all’APP Builder e vai in Shared Components > Application Definition
• Specifica nella proprietà Oracle Text Function il nome della funzione PL-SQL appena creata

Ed ecco qua che finalmente possiamo usare la Fuzzy Search nella nostra Interactive Grid in modo naturale. Fantastico!

Come attivare la Fuzzy Search in un Classic Report

Ora che sai come funziona Oracle Text e quali sono le sue potenzialità lo possiamo sfruttare in altro contesti.

Ad esempio possiamo costruire un funzionalità di ricerca fuzzy applicata ad un Classic Report.

Per far questo è necessario prima di tutto costruire un report con un parametro di ricerca come visto nel paragrafi precedenti.

Dopodiché dovrai configurare la Query del Report usando la clausola contains insieme alla funzione employee_search_convert_text_query.

Ecco un esempio:

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO,
       DNAME,
       LOC
  from EMP_DEPT_V
 where contains(ename, employee_search_convert_text_query(:P4_SEARCH)) > 0 
    or :P4_SEARCH is null

Ed ecco il risultato

Conclusioni

In questo tutorial abbiamo imparato diversi metodi e strategie per cercare e visualizzare i dati in Oracle APEX.

Abbiamo visto quanto siano potenti alcuni componenti standard come Faceted Search, Smart Filters ed Interactive Report.

Abbiamo capito come creare un report parametrizzato, tecnica utile se dobbiamo filtrare i dati che l’utente deve manipolare.

Infine, abbiamo capito che possiamo creare un vero e proprio motore di ricerca.

Certo, è necessario avere una cerca abilità con il PL-SQL e devi imparare ad usare Oracle Text, tuttavia la possibilità di poter indicizzare i contenuto della propria applicazione è sicuramente una opzione da non trascurare.

Fammi sapere nei commenti se questo tutorial ti è piaciuto.

Un abbraccio

Daniele