giafai.dev

Oracle mette a disposizione una tipologia di query molto interessante. Permette di creare strutture padre-figlio dicendo al motore database da quale valore partire e quale campo utilizzare per definire la struttura.

Un esempio di query è la seguente:

SELECT campo1
    FROM tabella
    START WITH campoX = valore
    CONNECT BY PRIOR campoX = campoY

Ad esempio, se avessimo la seguente tabella

campo1	campoX	campoY
Verde	1	0
Rosso	2	1
Beige	3	1
Nero	4	0
Bianco	5	1

Il risultato della query sarebbe questo:

Verde
   Rosso
   Beige
   Bianco
Nero

Davvero semplice. La parte iniziale è una select normale, START WITH imposta il valore iniziale del campo, e CONNECT By PRIOR serve, appunto, a dire ad Oracle quali campi utilizzare per definire la struttura, dove CampoX è il padre e CampoY è il figlio.

Se avessimo bisogno di inserire delle condizioni WHERE, sarebbe sufficiente aggiungerle dopo FROM tabella. Se, invece, avessimo bisogno di ordinare i risultati? La cosa è un po' più complicata, aggiungendo un semplice ORDER BY, i risultati verranno ordinati per il campo scelto, ma verrà persa la struttura padre-figlio, dato che Oracle non ne terrà conto nell'ordinamento.

Se volessimo, ad esempio, ordinare i precedenti risultati alfabeticamente, il risultato sarebbe questo:

   Beige
   Bianco
Nero
   Rosso
Verde

Per ovviare a questo problema, basta aggiungere ORDER SIBLINGS BY campoZ alla fine della query:

SELECT campo1, campo2
    FROM tabella
    START WITH campoX = valore
    CONNECT BY PRIOR campoX = campoY
    ORDER SIBLINGS BY campoZ

Che dice ad Oracle di ordinare i risultati a seconda del sottolivello in cui si trovano. Adesso il risultato è corretto ed è:


Nero
Verde
   Beige
   Bianco
   Rosso

Oggi ho dovuto affrontare un errore in una query per Oracle. L'errore era:

ORA-00907: parentesi chiusa mancante

Non proprio comprensibile...

La prima cosa che ho fatto è stata di controllare tutte le parentesi, tutte correttamente annidate e chiuse. Allora ho provato a indentarla in maniera corretta, visto che nell'errore di Oracle veniva riportata in maniera tutt'altro che leggibile. Ma ancora nulla. Allora ho cominciato a cancellare pezzi singolarmente, in maniera da ridurre il codice sql e individuare quale parte desse l'errore. E poi ho notato una cosa non proprio carina, tre apici in successione. Una cosa di questo tipo:

select * from table where column = replace(string, ''', '');

La funzione replace() ovviamente permette di sostituire un carattere con un altro all'interno di una stringa, ma ovviamente quel ''' da errore. Ho pensato di poter fare l'escape dell'apice interno con un backslace (\). Nulla di fatto.

Allora ho cominciato una piccola ricerca che ha prodotto questo risultato:

www.mydigitallife.info/2007/03/24/how-to-escape-characters-in-oracle-plsql-queries/

In pratica si dice di segnalare nella query stessa qual'è il carattere utilizzato per l'escape:

SELECT * FROM table WHERE column LIKE '%\_%' ESCAPE '\';

Poco sotto, però, viene affermato che questo metodo non funziona per fare l'escape dell'apice singolo. In quel momento m'è venuto in mente di usare un trucchetto per "non far vedere" a Oracle che carattere sto cercando nella mia stringa. E ho trasformato la mia query in questa:

select * from table where column = replace(string, chr(39), '');

chr(code) è la funzione che dice ad Oracle di sostituire il codice con il carattere corrispondente. (trovate una tabella delle corrispondenze qui)

La soluzione prospettata invece nell'articolo è quella di aggiungere un ulteriore apice davanti a quello di cui fare l'escape. Quindi il ''' diventa ''''.