ORA-06502 con Nested Tables

mercoledì 14 gennaio 2009 alle 14:31 | Pubblicato su Diario, PL/SQL | Lascia un commento
Tag:

Sul tipo di errore di cui scrivo in questo post credo di aver letto un’altro post da qualche parte, ho passato un’ora a cercare, ma non ho trovato traccia; quindi siccome non voglio più impazzire ho deciso di scrivere un post (d’altra parte è questo uno dei motivi per cui ho iniziato a scrivere un blog) così la prossima volta trovo subito le informazioni. Mi scuso per l’esposizione un po’ disordinata, ma non ho avuto tempo per far di meglio.

L’errore può sembrare banale, una volta capito a me è venuto il dubbio sul perché in una condizione non capita. Ma ora vediamo il caso.

Mi capita, di quando in quando di dover scrivere delle procedure PL/SQL per elaborazioni massive di dati, non cose complicate. Da molto tempo ormai ho imparato (più o meno) ad utilizzare strutture dati come le “nested tables”  e i costrutti “bulk” che rendono le prestazione di queste procedure PL/SQL nettamente superiori alle procedure che molto spesso da noi vengono fatte a livello applicativo (in java) se non ci sono problemi di tempo. Non è mia intenzione dare qui una spiegazione del perché conviene usare suddetti costrutti e perché danno un vantaggio in termini di prestazioni, ma appunto mettere in guardia su situazioni in cui la procedura può dare un errore tipo:

SQL>  exec paccageX.proceduraY();
0 –
ORA-06502: PL/SQL: numeric or value error

Ed ecco un esempio di pezzo di codice che può portare a tale errore:

OPEN idval_cur;
LOOP
idval_nt := idval_type();

FETCH idval_cur BULK COLLECT INTO idval_nt LIMIT MAXROWS;
dbms_output.put_line(idval_nt.count||’ – ‘||idval_nt.first);
FOR j IN idval_nt.FIRST..idval_nt.LAST LOOP
–DONOTHING, DO SOMETHING
dbms_output.put_line(idval_nt(j)||’ – ‘||j);
END LOOP;
EXIT WHEN idval_cur%NOTFOUND;
END LOOP;

In sostanza apro un cursore (idval_cur) e a blocchi di MAXROWS record metto il contenuto del dataset nella nested table idval_nt e poi con un ciclo FOR faccio qualcosa con questi record.

La procedura sopra in certe condizioni da errore, ma è l’evoluzione (o involuzione?) di questa:

OPEN idval_cur;
LOOP
idval_nt := idval_type();
FETCH idval_cur BULK COLLECT INTO idval_nt LIMIT MAXROWS;
dbms_output.put_line(idval_nt.count||’ – ‘||idval_nt.first);
FORALL  j IN idval_nt.FIRST..idval_nt.LAST
update log_elaborazione set deleted01=idval_nt(j);
END LOOP;
EXIT WHEN idval_cur%NOTFOUND;
END LOOP;

A differenza della prima questa non da mai errore.

Il motivo per cui la prima da errore è perchè il fetch restituisce ZERO record il che tipicamente può capitare in due condizioni (almeno credo):

  1. il cursore restituisce zero record fin dall’inizio
  2. all’ultimo ciclo se il ciclo precedente aveva estratto fino all’ultimo record

lascio ai lettori l’esercizio di verifica e prova di queste condizioni. In ogni caso ciò che accade è che la fetch non estrae nulla, quindi di fatto idval_nt è vuota,  ciò si vede dal fatto che l’output di

dbms_output.put_line(idval_nt.count||’ – ‘||idval_nt.first);

è

0 –

Da tale output si vede anche che idval_nt.first è NULL, quindi quando viene usato in

FOR j IN idval_nt.FIRST..idval_nt.LAST LOOP

La procedura da l’errore

ORA-06502: PL/SQL: numeric or value error

In questa situazione il costrutto FORALL sembra molto più intelligente, infatti

FORALL  j IN idval_nt.FIRST..idval_nt.LAST

Non da errori

Quindi nel caso di ciclo FOR normale occorre mettere una condizione di controllo sulla nested table per esempio adoperando idval.count>0 (che funziona)

Lascia un commento »

RSS feed for comments on this post. TrackBack URI

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...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: