TABLE e PIPELINED functions

mercoledì 18 luglio 2007 alle 18:47 | Pubblicato su Diario, PL/SQL | 2 commenti

In questi giorni sono stato preso da un lavoro urgente di scrittura di procedure PL/SQL che mi hanno impedito di fare le mie solite attività di documentazione e scrittura su questo blog. In questo lavoro che mi ha impegnato ho avuto modo per la prima volta di sperimentare l’uso delle table functions e delle pipelined functions.

Devo ammettere che il mio approccio in queste situazioni è un po’ disordinato. Quando mi pare che per risolvere il problema che mi si presenta mi pare si possa applicare una funzionalità che ho gia intravisto ma mai sperimentato, vado sul manuale, leggo mooolto velocemente l’introduzione fino ad arrivare al primo esempio e poi comincio ad adattare l’esempio alla mia esigenza.

Così ho fatto anche questa volta, mi sono trovato a dover fare una procedura di “normalizzazione” di un database. Ho una tabella, che chiamerò tabella A, con un campo di tipo varchar2 che contiene dei numeri separati da virgole: questi id sono riferimenti ad un’altra tabella, che chiamerò tabella B. Quindi devo prendere per ogni record di questa tabella tutti questi id e generare n record da mettere in una bella tabella di relazione. Allora ho provato a fare una funzione che prende in input un numero (l’id della tabella A) e una stringa (il campo della tabella A che contiene gli id della tabella B separati da virgole) e restituisce tanti record quanti sono gli id separati da virgola nella stringa di input, questi record sono niente altro che coppie di id da mettere in una tabella di relazione che gestisce la relazione n-n tra la tabella A e la tabella B. Per questo quelle che avevo gia intravisto come pipelined functions mi sono sembrate adatte alla mia idea di soluzione. Effettivamente in poco tempo ho fatto la mia pipelined function che ho chiamato tokenize che si comporta così:

select * from table(tokenize(‘180,182,186,191,192,193,195’,5690));
I_X2000AVERID I_X2000AVERCNC
————- ————–
5690 180
5690 182
5690 186
5690 191
5690 192
5690 193
5690 195

Selezionate 7 righe.


I dolori sono arrivati quando ho cercato di mettere questa procedura dentro un’altra procedura cercando di passare come parametri della procedura gli elementi di una nested table all’interno di un ciclo for: mi sono scontrato con l’errore:


737/9 PL/SQL: SQL Statement ignored
743/21 PLS-00231: function ‘TOKENIZE’ may not be used in SQL
743/21 PL/SQL: ORA-00904: : invalid identifier

Confesso di non aver capito completamente l’errore, fra i miei disperati e confusionari tentativi di aggirarlo mi sono anche inbattuto in quello che sicuramente è un baco oracle, in quanto quando cercavo di creare il corpo del package la mia sessione veniva stroncata con questo errore:

ERROR:
ORA-03114: non connesso a ORACLE
create or replace package body tokenizer is
*
ERRORE alla riga 1:
ORA-03113: end-of-file sul canale di comunicazione
Alla fine credo di aver capito che all’interno di una procedura PL/SQL non si può usare una “pipelined function” (o meglio vi sono delle restrizioni, non ben documentate), probabilmente perchè non è strettamente necessario, nel mio caso in fatti era sufficiente una “table function” una funzione cioè che restituisce un oggetto “table”. Le “pipelined functions” invece restituiscono il risultato come una coda fifo, cominciando subito, prima che la funzione abbia terminato completamente l’elaborazione, si tratta di una estensione delle table functions

31 Luglio 2007: AGGIORNAMENTO 

Oggi guardando casualmente gli RSS feed di AskTom mi è balzato all’occhio uno con il titolo “PLS-00231” che mi è parso familiare, era proprio il mio errore, e qui Tom spiega come evitarlo. In realtà poi, facendo test separati, ho incontrato l’errore “ORA-22905: cannot access rows from a non-nested table item”  che inizialmente, sempre in preda alla confusione mentale non ho capito; sono andato a vedere meglio la descrizione dell’errore:

Error:	ORA-22905
Text:	cannot access rows from a non-nested table item 
---------------------------------------------------------------------------
Cause:	attempt to access rows of an item whose type is not known at parse 
	time or that is not of a nested table type 
Action:	use CAST to cast the item to a nested table type

Infatti io aprivo un cursore, scaricava i dati su una Nested Table e poi cercavo di inserire i dati in una tabella con questa istruzione:

INSERT INTO tabellaA
(x2000averid,
x2000acncid
)
(
SELECT x.i_x2000averid , x.i_x2000avercnc
from table(TOKENIZE(mia_nestedtable_nt(i).x2000averacnc2 , mia_nestedtable_nt(i).x2000averid )) x
where x.i_x2000avercnc is not null
);

In realtà bisogna usare il CAST per gli elementi della Nested Table che vengono passati come parametro per la funzione “TOKENIZE”:

INSERT INTO bo_verbalicnc
(x2000averid,
x2000acncid
)
(
SELECT x.i_x2000averid , x.i_x2000avercnc
from table(TOKENIZE(cast(mia_nestedtable_nt(i).x2000averacnc2 as varchar(100)), cast(mia_nestedtable_nt(i).x2000averid as number))) x
where x.i_x2000avercnc is not null
);

Spero proprio di ricordarmi bene queste cose la prossima volta, d’altra parte è proprio per ricordarmele che le scrivo qui!

2 commenti »

RSS feed for comments on this post. TrackBack URI

  1. […] 31st, 2007 · No Comments Oggi ho approfondito il problema di cui ho parlato in questo post. Credo di essermi chiarito le idee ed aver capito i miei errori, ho aggiornato il suddetto post con […]

  2. […] L’articolo di “Inside the Oracle Optimizer” è molto interessante ed istruttivo, l’unico punto un  po’ vago è quello proprio sulle “Lateral Views”, che sono un concetto che fa parte di qualche standard SQL (secondo il manuale SQL Foundation 2003) e in Oracle è implementato tramite la funzione “TABLE” che in sostanza serve a tirare fuori gli elementi da una collection direttamente via SQL, oppure, come mostra Jonathan Lewis nei suoi interessanti esempi, si utilizza con le funzioni “PIPELINED” (ne ho parlato qui). […]


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: