Oracle and other

Alcuni giorni fa, scrivendo una procedura in PL/SQL mi sono inbattuto in un problema particolare con i record in PL/SQL. In realtà non mi sono subito reso conto che il problema era nel tipo record ed ho fatto molta fatica a capire bene che cosa sbagliavo io e che queli sono in confini del PL/SQL.

Come ho gia detto ieri il PL/SQL è un linguaggio potente, ma nasconde un sacco di insidie per chi non lo conosce bene e non neconosce i confini, in particola nella interazione con il motore SQL.

Finalmente oggi sono riuscito a fare una serie di test per capire bene cosa si può fare e cosa non si può fare con i record, la versione Oracle alla quale mi riferisco è la 10.2.0.4, in 11g non ho fatto dei test ma ho visto che le cose sono diverse.

Cerco di dare in modo molto sintetico la specifica di ciò che voglio (o volevo) fare:

ho due tabelle A e B, A ha una chiave esterna verso B. Devo travare il contenuto della join tra A e B su una tabella C, a intervalli regolari. Le tabelle A e B vengono modifica in modi diversi, nella tabella A vengono solo inseriti nuovi record, nella tabella B vengono inseriti e aggiornati, in momenti diversi. A è una tabella molto grande, B è più piccola.

La mia idea, sulla cui legittimità per ora sorvoliamo, era questa:

1) apro un cursore sulla A che prende solo i record inseriti dall’ultima elaborazione

2) in un ciclo  faccio un bulk collect in una nested table con il “LIMIT“, nello stesso ciclo con una forall vorrei inserire i record di A in C.

Riporto un test di questa procedura (non fedelissimo):

conn scott/tiger
create table emp2 as select * From emp where 1=0;

create or replace procedure test1 is
cursor emp_cur is
select
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       — ,
– COMM      ,
– DEPTNO
from emp;
type nt_emp_rec_type is table of emp_cur%rowtype;
nt_emp_var nt_emp_rec_type;
begin
open emp_cur;
loop
nt_emp_var := nt_emp_rec_type();
fetch emp_cur bulk collect into nt_emp_var limit 1000;
forall i IN nt_emp_var.first..nt_emp_var.last
insert into EMP2
(
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       –,
– COMM      ,
– DEPTNO
)
values nt_emp_var(i);
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
close emp_cur;
end;
/

Questa procedura da il seguente errore di compilazione:

SCOTT@perseo10 > show errors
Errori in PROCEDURE TEST1:

LINE/COL ERROR
——– —————————————————
21/5     PL/SQL: SQL Statement ignored
32/2     PL/SQL: ORA-00947: non ci sono abbastanza valori

Ora, nella procedura ci sono un po’ di “variabili” in gioco, prinpipalmente il fatto che adopero i costrutti bulk, per cercare di migliorare le prestazioni, però stando alla documentazione, che dice:

…

If you issue the INSERT through the FORALL statement, you can insert values from an entire collection of records. The number of fields in the record must equal the number of columns listed in the INTO clause, and corresponding fields and columns must have compatible datatypes. To make sure the record is compatible with the table, you might find it most convenient to declare the variable as the type table_name%ROWTYPE.

…

sembrerebbe che ciò che ho scritto sia lecito. In realtà non lo è, infatti il secondo esempio che si trova su questa pagina sul sito di Oracle, c’è scritto:

YOU CAN'T BIND AN EXPLICIT COLUMN LIST TO A RECDORD

Con un po’ di ricerche ho scoperto che c’è un modo per aggirare questo limite, non so il perché e non ne ho trovata spiegazione, però ecco due link dove c’è la soluzione:

1) oraclequirks (Gary Mayers nei commenti da la soluzione)

2) Oracle Forums

Ed ecco il test:

create or replace procedure test2 is
cursor emp_cur is
select
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       — ,
– COMM      ,
– DEPTNO
from emp;
type nt_emp_rec_type is table of emp_cur%rowtype;
nt_emp_var nt_emp_rec_type;
begin
open emp_cur;
loop
nt_emp_var := nt_emp_rec_type();
fetch emp_cur bulk collect into nt_emp_var limit 1000;
forall i IN nt_emp_var.first..nt_emp_var.last
insert into
(select
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       –,
– COMM      ,
– DEPTNO
from emp2 where 1=0)
values nt_emp_var(i);
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
close emp_cur;
end;
/
SCOTT@perseo10 > show errors
Nessun errore.

sostituendo a <nome tabella> (<elenco campi>) la query (select <elenco campi> from <nome tabella>) è possibile legare una lista esplicita di colonne a un record.

Superato il problema degli insert arriva il secondo problema, la fase due della mia procedura. L’idea adesso è quella di aprire un cursore sulla tabella B con i record modificati dall’ultima elaborazione ed andare a fare un update sulla tabella C. Anche qui vorrei sfruttare l’efficenza dei costrutti bulk, ma qui neppure la documentazione da scampo (ma anche qui ho dovuto sbattere il naso, a causa dell’errore:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

per cui questa implementazione non funziona:

create or replace procedure test3 is
cursor emp_cur is
select
EMPNO     ,
– ENAME     ,
– JOB       ,
– MGR       ,
– HIREDATE  ,
– SAL       — ,
COMM      ,
DEPTNO
from emp;
type nt_emp_rec_type is table of emp_cur%rowtype;
nt_emp_var nt_emp_rec_type;
begin
open emp_cur;
loop
nt_emp_var := nt_emp_rec_type();
fetch emp_cur bulk collect into nt_emp_var limit 1000;
forall i IN nt_emp_var.first..nt_emp_var.last
update emp2
set
comm = nt_emp_var(i).comm,
deptno = nt_emp_var(i).deptno;
where empno = nt_emp_var(i).empno;
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
close emp_cur;
end;
/

Fra l’altro mi sembra che qui ci sia anche la violazione della restrizione:

Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.

Qui subentra un trucco interessante, basato sull’utilizzo della funzione SQL TREAT, che ho trovato su AskTom:

create type emp_obj_type is object (
empno    number(4),
comm        number(7,2),
deptno    number(2));
/
create type nt_emp_obj_type is table of emp_obj_type;
/

create or replace procedure test7 is
cursor emp_cur is
select
EMPNO     ,
– ENAME     ,
– JOB       ,
– MGR       ,
– HIREDATE  ,
– SAL       — ,
COMM      ,
DEPTNO
from emp;
nt_emp_var nt_emp_obj_type;
begin
select emp_obj_type(EMPNO     ,
COMM      ,
DEPTNO )
bulk collect into nt_emp_var
from emp;
forall i IN nt_emp_var.first..nt_emp_var.last
update emp2
set
comm  = treat(nt_emp_var(i) as emp_obj_type).comm,
deptno = treat(nt_emp_var(i) as emp_obj_type).deptno
where
empno = treat(nt_emp_var(i) as emp_obj_type).empno;
end;
/

Procedura creata.

SCOTT@perseo10 > show errors
Nessun errore.

Qui c’è un primo grosso problema: non riesco (e non sembra possibile) ad usare un cursore esplicito con un bulk collect in una nested table con il “LIMIT“. Non mi piace il fatto di usare tipi esterni (SQL), non so se rimanga più efficente. Se la tabella è molto grande (non è il mio caso secondo le mie specifiche iniziali) mi carico una nested table molto grande, cosa che non va molto bene. Trovo però affascinante come questo trucco riesca a permettere di superare uno dei limiti della FORALL.

E’ chiaro che devo imparare ancora qualcosa sul PL/SQL. Anche l’ultimo esempio che ho riportato mostra come l’interazione tra motore PL/SQL e motore SQL nasconde molte insidie. Questo post, nel pieno spirito di questo blog ha più lo scopo di appunto mio che spiegazione, in quanto in effetti non è che spieghi molto.

Ieri ho trovato un problema che secondo me sarebbe stato bene nel blog “Annals of Oracle’s Improbable Errors“, infatti l’ho cercato li, ma no l’ho trovato.

Un programmatore aveva l’esigenza di avere una funzione PL/SQL per il calcolo dell’hash con l’algoritmo MD5; per conto suo aveva già effettuato una ricerca sul web ed era arrivato al package DBMS_OBFUSCATION_TOOLKIT, con un esemprio di funzione che però gli dava un errore che però non ha saputo mostrarmi (ecco perché non amo certi strumenti grafici). Siccome l’esigenza era di utilizzare questa funzione su 9iR2 ho fatto una indagine, anche perché confesso di non aver mai utilizzato ne tale funzione ne tale package, però mi sembrava di ricordare che ci fossero (come dovrebbe essere normale) differenze tra la versione 9 e la 10. Infatti, sulla 10gR2 per questo scopo esiste la funzione HASH del packge DBMS_CRYPTO. Sulla 9iR2 tale package non esiste, e nella documentazione del package DBMS_OBFUSCATION_TOOLKIT non compaiono funzioni MD5. In realtà, facendo un desc del package da SQL*Plus, si ha una sorpresa:

SVILUPPO30@perseo92 > desc dbms_obfuscation_toolkit
…
PROCEDURE MD5
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
INPUT                          RAW                     IN
CHECKSUM                       RAW                     OUT
FUNCTION MD5 RETURNS RAW(16)
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
INPUT                          RAW                     IN
PROCEDURE MD5
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
INPUT_STRING                   VARCHAR2                IN
CHECKSUM_STRING                VARCHAR2                OUT
FUNCTION MD5 RETURNS VARCHAR2(16)
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
INPUT_STRING                   VARCHAR2                IN

Ho tagliato la parte che non mi interessa. Come si vede compaiono delle funzioni MD5. Il problema è che provando la funzione si ha un errrore:

SVILUPPO30@perseo92 > select dbms_obfuscation_toolkit.md5('cristian') from dual;
select dbms_obfuscation_toolkit.md5('cristian') from dual
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'MD5' match this call

Una ricerca sul metalink mi ha portato alla nota 734764.1 la quale riporta:

error is because there are 2 DESEncrypt functions and the SQL parser does not know which one to call

In questo caso parla della funzione DESEncrypt che evidentemente soffre dello stesso problema.

Utilizando la versione procedurale della funzione MD5 si riesce ad aggirare il problema. Il punto è che ho notato che l’overloading di funzioni PL/SQL con paremetri VARCHAR2 o RAW non funziona, ovvero se ho due funzioni uguali, una con un parametro di input di tipo RAW e l’altra con una parametro di input di tipo VARCHAR2, Oracle non è in grado di “disambiguare” e identificare la corretta definizione della funzione e ritorna l’errore riportato. Ho avuto la prova di questa facendo un banalissimo test:

create or replace package test_pkg as
function a (i_p number) return number;
function a (a_p varchar2) return number;
function a (r_p raw) return number;
end;
/
create or replace package body test_pkg as
function a (i_p number) return number is
l_var number;
begin
l_var:=1;
return l_var;
end;
function a (a_p varchar2) return number is
l_var number;
begin
l_var:=2;
return l_var;
end;
function a (r_p raw) return number is
l_var number;
begin
l_var:=3;
return l_var;
end;
end;
/

SVILUPPO30@perseo92 >  select test_pkg.a(8) from  dual;

TEST_PKG.A(8)
-------------
1

SVILUPPO30@perseo92 >  select test_pkg.a('11') from dual;
select test_pkg.a('11') from dual
*
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'A' match this call

Ho provato anche con la funzione CAST o il package UTL_RAW ma non ho trovato un modo per aggirare il problema. Va detto poi che lo stesso test l’ho fatto su una istanza 10.2.0.4 e il problema persiste

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 i miei test e i chiarimenti. Ero indeciso se fare un nuovo post di chiarimento o aggiornare quello originale, alla fine ho trovato questo compromesso. Questo caso mostra come conoscere bene PL/SQL non sia proprio facile da conoscere a fondo.