Campi varchar2 con stringhe vuote

mercoledì 7 ottobre 2015 alle 07:14 | Pubblicato su PL/SQL, SQL, Varie | Lascia un commento

Post molto sintetico:


set serveroutput on size unlimited

declare
a varchar2(10);
begin
a := '';
if (a is null) then dbms_output.put_line('empty = null');
else dbms_output.put_line('empty is not null');
end if;
if (a = '') then dbms_output.put_line('empty = empty');
else dbms_output.put_line('empty <> empty');
end if;
a := null;
if (a is null) then dbms_output.put_line('empty = null');
else dbms_output.put_line('empty is not null');
end if;
if (a = '') then dbms_output.put_line('empty = empty');
else dbms_output.put_line('empty <> empty');
end if;
end;

TEST@test112 > /
empty = null
empty <> empty
empty = null
empty <> empty

Annunci

Il costrutto SQL CASE

mercoledì 12 settembre 2012 alle 12:57 | Pubblicato su 11g, PL/SQL, SQL | Lascia un commento
Tag: , ,

Pur essendo il mio strumento di uso quotidiano da oltre 10 anni l’SQL è un linguaggio di cui ancora non ho piena padronanza, anche perché per le mie esigenze in realtà molti costrutti li ho usati poche volte. Un po’ alla volta però i nodi vengono al pettine e mi scontro con la necessità di approfondire l’uso di espressioni che in precedenza sono riuscito ad evitare. Fra queste ci oggi parlo delle espressioni “CASE“. Comincio con il dire, come apprendo dal libro “Oracle Database 11g SQL” di Jason Price che la CASE espression serve a implementare la classica logica IF-Then-else dentro statement SQL, è supportata da Oracle 9i in su (anche se sul sito di Tim Hall si parla di prima apparizione in Oracle 8i, evidentemente con limitazioni). Le espressioni CASE funzionano in modo simile alla funzione DECODE, ma la espressione CASE oltre ad essere più leggibile ha il vantaggio di essere conformo allo standard ANSI SQL/92, cosa che pare confermata da questa bozza di tale standard.

Per un’ottima descrizione di questo tipo di espressioni non mi posso esimere da riportare come primo riferimento il manuale ufficiale oracle: “Oracle Database SQL language reference“. E’ molto sintetico ma credo dica tutto e riporta anche due semplici esempi. La cosa che a me ha sempre confuso è il fatto che esistono due versioni di espressioni CASE:

  1. le Simple case expressions: che funzionano esattamente come la DECODE
  2. le Searched case expressions: che sono un po’ più complesse

A parte il vincolo sulla omogeneità dei tipi dato, non ci sono vincoli particolari sul tipo dato su cui possono lavorare le condizioni. In realtà noto la mancanza sul manuale a riferimenti a tipi data, però facendo un paio di prove a me pare funzioni:


SVILUPPO40@perseo10 > select case trunc(Sysdate) when to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > alter session set nls_date_format='yyyy-mm-dd';

Modificata sessione.

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else 2 end case1 from dual;
select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else 2 end case1 from dual
 *
ERRORE alla riga 1:
ORA-00932: tipi di dati incoerenti: previsto DATE, ottenuto NUMBER
SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else to_date('07-02-2010','d
d-mm-yyyy') end case1 from dual;

CASE1
----------
2010-02-01

L’errore che ho ricevuto nel penultimo esempio mi fa supporre che non sia una questione di conversione implicita ma una vera e propria gestione del tipo DATE.

Un bell’articoletto che spiega il CASE si può trovare sul sito databasejournal.com, anche se noto che parla solo delle “searched case expressions”. Invece Tim Hall fa degli esempi di entrambe le versioni, sia in SQL che in PL/SQL. Mi sento di dire che mentre le “simple case expressions” gestiscono gli stessi casi gestiti dalla decode, quindi solo uguaglianze su un campo, le “searched case expressions” permettono, sempre nel rispetto dell’omogeneità del tipo dato, costruzioni molto complesse usando anche operatori di comparazione quali “>” e “<“.

ORA-01461 e VARCHAR2

venerdì 28 ottobre 2011 alle 28:03 | Pubblicato su Diario, PL/SQL, SQL | 2 commenti
Tag: , , , ,

Più volte mi è stato segnalato sulla nostra applicazione l’errore ORA-01461, il cui testo è “can bind a LONG value only for insert into a LONG column“. Prima di tutto è curioso che abbia dovuto risalire fino alla documentazione della versione 9.2 del database per rintracciare la documentazione dell’errore, quando tale errore l’ho riprodotto fino alla versione 11.2.0.2.

Altra cosa bizzarra e che forse spiega la rimozione dalla documentazione ufficiale di tale errore è che nei nostri database il tipo LONG cui si riferisce il testo del messaggio di errore non è mai esistito.

Un indizio che mi aiutò all’inizio a capire da cosa potesse derivare questo errore è il fatto che l’errore abbia fatto le prime comparse dopo che siamo passati sui nostri database al character set UTF-8 (mentre prima si usava ISO-8859 o equivalente). Potrei dedicare un intero post solo a descrivere le mie peripezie a cercare di fare test con la gestione di caratteri non ASCII con il character set UTF-8 ma per ora soprassiedo.

Oltre alle difficoltà a fare dei test con l’UTF-8, per la cui gestione da windows ho ancora delle difficoltà che ho aggirato usando putty e collegandomi su macchine linux, sono stato ingannato dal comportamento di funzioni come RPAD che fa cose come questa:


SVILUPPO42@perseo10 > select length(rpad('a',4500,'b')) from dual;

LENGTH(RPAD('A',4500,'B'))
--------------------------
4000

Comportamento del tutto lecito e anche logico, ritornando tale funzione un tipo dato VARCHAR2 che ha come limite appunto 4000 (byte), ma non essendo espressamente specificato nella documentazione non davo per scontato. Poi sulle funzioni SQL in Oracle occorre sempre fare attenzione, se sono usate in SQL valgono delle regole, in PL/SQL le cose sono leggermente diverse (e un varchar2 in PL/SQL può arrivare a 32767 (byte)

Riassumendo riporto le prove che ho fatto:


SQL> create table test_char (a varchar2(1300 char));

Table created.

Primo tentativo di aggirare il limite di RPAD


SQL> insert into test_char values (rpad('a',4000,'b')||'test');
insert into test_char values (rpad('a',4000,'b')||'test')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Al che ho deciso di provare con bind variables ma ho incontrato un’altra anomalia:


SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 28 14:44:27 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> var v_test varchar2(4000 char);
SP2-0676: Bind variable length cannot exceed 1000 characters

Il che è dovuto, come ho scoperto in seguito, semplicemente al fatto che avevo settato (erroneamente) la variabile d’ambiente

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Quindi ho deciso di passare a un test con PL/SQL:


SQL> declare
a varchar2(5000 char);
begin
a :=rpad('a',2000,'a');
insert into test_char values (a);
end;
/  2    3    4    5    6    7
declare
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST_CHAR"."A" (actual: 2000,
maximum: 1300)
ORA-06512: at line 5

Dove si ottiene l’errore che mi aspetto. In realtà questo test funziona anche con una insert diretta con RPAD direttamente via SQL, ma il caso interessante è questo:


SQL> declare
a varchar2(5000 char);
begin
a :=rpad('a',4000,'a')||'test';
insert into test_char values (a);
end;
/  2    3    4    5    6    7
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 5

Che è l’errore che cercavo. Ho cercato di analizzare il trace generato attivando l’evento 10046 ma non ci ho trovato nulla di interessante.  L’errore si verifica anche da Java con driver JDBC, parrebbe che essendo una  stringa con lunghezz superiore ai 4000 caratteri la variabile venga convertita in tipo long e da ciò deriva l’errore, è una supposizione che faccio.

Gestione delle eccezioni in PL/SQL

martedì 21 luglio 2009 alle 21:11 | Pubblicato su PL/SQL | 6 commenti
Tag: , ,

Gia in due precedenti post (questo e questo) ho parlato di come “attrezzare” opportunamente le procedure PL/SQL per facilitare la manutenzione ed individuare facilmente errori o problemi.  Qualche giorno fa però ho letto sul blog di Chen Shapira un post in cui descrive una situazione in cui si è trovata coinvolta a causa di procedure PL/SQL in cui non è stata fatta una gestione delle eccezioni adeguata.

La situazione che Chen descrive ha veramente dell’incredibile,  un DBA ha lavorato per sei mesi a un progetto, il lunedì va in ferie e il mercoledì è previsto un passaggio in produzione, senza alcun passaggio di consegne decente. Naturalmente martedì mattina vengono fuori i problemi. Non pensavo che in aziende grandi e strutturate, dove si usa ITIL e dove ci sono “team” di DBA ecc ecc. succedessero cose simili.

Tralasciando quindi il contesto, mi focalizzerei sul problema, delle procedure PL/SQL che vanno in errore  e  gestiscono l’eccezione in modo generico in questo modo:

EXCEPTION
WHEN OTHERS
THEN
INSERT INTO NIGHT_JOB
(status, last_run_stmp)
VALUES (‘failed’, SYSDATE);
COMMIT;

Ebbene, questo è un bel problema, fra i commenti al post però c’è un suggerimento interessante, mettere un trigger sulla tabella NIGHT_JOB.  La soluzione proposta in realtà ha un errore e, come replica Chen, non va bene l’idea di rilanciare un’eccezione.

Il motivo per cui la soluzione proposta non è corretta  è il fatto che a quanto pare nel corpo del trigger le funzioni SQLCODE e SQLERRM si resettano,  anche se pare il fatto di rilanciare l’eccezione trasmetta anche l’errore originale, ecco qui un test:

SVILUPPO40@perseo10 > drop table test_1
2  /

Tabella eliminata.

SVILUPPO40@perseo10 > create table test_1 (
2   text varchar2(12),
3   datetime timestamp with time zone
4  )
5  /

Tabella creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > CREATE OR REPLACE TRIGGER TR_test_exc_01 before INSERT
2  ON test_1
3  FOR EACH ROW
4  begin
5  RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);
6  END;
7  /

Trigger creato.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > create or replace procedure test_catch_exception is
2   MY_EXCEPTION_IN_LOOP EXCEPTION;
3   PRAGMA EXCEPTION_INIT(MY_EXCEPTION_IN_LOOP, -20001);
4   a varchar2(512);
5  BEGIN
6   FOR i IN 1..10 LOOP
7   IF i = 5 THEN
8     RAISE MY_EXCEPTION_IN_LOOP;
9   END IF;
10  END LOOP;
11  EXCEPTION
12  WHEN OTHERS THEN
13   INSERT INTO TEST_1 VALUES (‘error’,systimestamp);
14  END;
15  /

Procedura creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > exec test_catch_exception;
BEGIN test_catch_exception; END;

*
ERRORE alla riga 1:
ORA-21000: numero errore dell’argomento in raise_application_error di  0 e fuori intervallo
ORA-06512: a “SVILUPPO40.TR_TEST_EXC_01”, line 2
ORA-04088: errore durante esecuzione del trigger ‘SVILUPPO40.TR_TEST_EXC_01’
ORA-06512: a “SVILUPPO40.TEST_CATCH_EXCEPTION”, line 13
ORA-20001:
ORA-06512: a line 1

La funzione RAISE_APPLICATION_ERROR vuole come primo argomento un numero compreso tra -20000 e -20999; stampando nel trigger i SQLCODE e SQLERRM si vede:

SQLCODE: 0
SQLERRM: ORA-0000: normal, successful completion

Nello stesso commento però si suggerisce la possibilità di usare le funzioni DBMS_UTILITY.FORMAT_ERROR_STACK and/or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE e la stessa Chen valuta la possibilità invece di rilanciare l’eccezione di salvare le informazioni in una tabella. Le due cose combinate secondo me danno la soluzione ottimale:

SVILUPPO40@perseo10 > drop table test_1
2  /

Tabella eliminata.

SVILUPPO40@perseo10 > create table test_1 (
2   text varchar2(100),
3   datetime timestamp with time zone
4  )
5  /

Tabella creata.

SVILUPPO40@perseo10 > drop table test_tr_2
2  /

Tabella eliminata.

SVILUPPO40@perseo10 > create table test_tr_2 (
2   backtr varchar2(200),
3   stack varchar2(200),
4   errm varchar2(512)
5  )
6  /

Tabella creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > CREATE OR REPLACE TRIGGER TR_test_exc_01 before INSERT
2  ON test_1
3  FOR EACH ROW
4  declare
5  a varchar2(2000);
6  BEGIN
7     a := sqlerrm;
8          INSERT INTO test_tr_2
9          VALUES
10          (
11          substr(DBMS_UTILITY.format_error_backtrace(),1,200),
12          substr(DBMS_UTILITY.format_error_stack(),1,200),
13          a);
14          dbms_output.put_line(a);
15  END;
16  /

Trigger creato.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > DECLARE
2   MY_EXCEPTION_IN_LOOP EXCEPTION;
3   PRAGMA EXCEPTION_INIT(MY_EXCEPTION_IN_LOOP, -20001);
4  BEGIN
5   FOR i IN 1..10 LOOP
6   IF i = 5 THEN
7     RAISE MY_EXCEPTION_IN_LOOP;
8   END IF;
9  END LOOP;
10  EXCEPTION
11  WHEN MY_EXCEPTION_IN_LOOP THEN
12   INSERT INTO TEST_1 VALUES (‘error’,systimestamp);
13  END;
14  /
ORA-0000: normal, successful completion

Procedura PL/SQL completata correttamente.

SVILUPPO40@perseo10 > select * from test_tr_2;

BACKTR
——————————————————————————
STACK
——————————————————————————
ERRM
——————————————————————————
ORA-06512: a line 7
ORA-20001:
ORA-0000: normal, successful completion

SVILUPPO40@perseo10 > select * from test_1;

TEXT
——————————————————————————
DATETIME
—————————————————————————
error
21-LUG-09 18:06:57,452000 +02:00

In aggiunta a quanto ho gia’ scritto nei miei vecchi post, pare sia utile l’utilizzo congiunto di format_error_backtrace e di format_error_stack, il primo permette di risalire alla riga di codice che ha generato l’eccezione, però non dice qual’è l’eccezione, il secondo da questa informazione.

Il ciclo FOR in PL/SQL su Oracle 10g

lunedì 20 luglio 2009 alle 20:51 | Pubblicato su PL/SQL | 1 commento
Tag: , ,

Trovo solo ora il tempo e la forza per scrivere un nuovo post, per dare segni di vita su questo blog. Il contenuto non è molto avanzato, anzi mette in evidenza delle mie lacune della mia cultura PL/SQL (che però una alla volta cerco di colmare 🙂 ).

Scrivendo un procedura PL/SQL la settimana scorsa, avendo la necessità di gestire un’eccezione all’interno di un ciclo FOR, avendo in questa gestione la necessità di registrare l’indice del ciclo che ha generato l’eccezione, ho scoperto che l’indice di un ciclo FOR in PL/SQL è una variabile locale al solo ciclo for, sempre.

Mi spiego meglio con un esempio:

SVILUPPO40@perseo10 > DECLARE
2   MY_EXCEPTION_IN_LOOP EXCEPTION;
3  BEGIN
4   FOR i IN 1..10 LOOP
5   IF i = 5 THEN
6     RAISE MY_EXCEPTION_IN_LOOP;
7   END IF;
8  END LOOP;
9  EXCEPTION
10  WHEN MY_EXCEPTION_IN_LOOP THEN
11  DBMS_OUTPUT.PUT_LINE(i);
12  END;
13  /
DBMS_OUTPUT.PUT_LINE(i);
*
ERRORE alla riga 11:
ORA-06550: riga 11, colonna 22:
PLS-00201: l’identificativo ‘I’ deve essere dichiarato
ORA-06550: riga 11, colonna 1:
PL/SQL: Statement ignored

Ora, io non so perché, immagino che in linguaggi come C e Java, i linguaggi con cui mi è capitato di lavorare in passato, l’indice o contatore del ciclo for debba essere una variabile esplicitamente dichiarata, io quando cominciai a scrivere i miei primi programmi in PL/SQL pensavo che la variabile contatore andasse dichiarata prima di poter essere utilizata. Poi ho scoperto che i programmi funzionavano anche se non mettevo tali dichiarazioni. Il fatto è che dietro questo meccanismo di nasconde una trappola, come mostro in questo esempio:

SVILUPPO40@perseo10 > DECLARE
2   MY_EXCEPTION_IN_LOOP EXCEPTION;
3   i NUMBER := -1;
4  BEGIN
5   FOR i IN 1..10 LOOP
6   IF i = 5 THEN
7     RAISE MY_EXCEPTION_IN_LOOP;
8   END IF;
9  END LOOP;
10  EXCEPTION
11  WHEN MY_EXCEPTION_IN_LOOP THEN
12  DBMS_OUTPUT.PUT_LINE(i);
13  END;
14  /
-1

Procedura PL/SQL completata correttamente.

Ovvero, quella che dichiaro e quella che viene usata nel ciclo for sono due variabili diverse, perché su due “livelli diversi”; quello che intendo è che io mi sarei aspettato, ingenuamente, nel caso sopra come output il valore 5. Invece occorre fare una cosa del genere:

SVILUPPO40@perseo10 > <<my_block>>
2  DECLARE
3   MY_EXCEPTION_IN_LOOP EXCEPTION;
4   i NUMBER := -1;
5  BEGIN
6   FOR i IN 1..10 LOOP
7   my_block.i := i;
8   IF i = 7 THEN
9     RAISE MY_EXCEPTION_IN_LOOP;
10   END IF;
11  END LOOP;
12  EXCEPTION
13  WHEN MY_EXCEPTION_IN_LOOP THEN
14  DBMS_OUTPUT.PUT_LINE(i);
15  END;
16  /
7

Procedura PL/SQL completata correttamente.

Qui ho voluto utilizzare un “feature” che non ho mai utilizzato, ovvero una “label, secondo me per evitare confusione è semplicemente meglio usare nomi di variabili diverse per non far confusione con le regole di “scoping”.

Rileggendo con attenzione questo paragrafo del manuale queste cose sono tutte ben spiegate, facile a dirsi, ma come titola il blog di Iggy Fernandez: “So Many Oracle Manuals, So Little Time”.

Il tipo Record in PL/SQL

martedì 7 aprile 2009 alle 07:19 | Pubblicato su PL/SQL | 1 commento

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.

Appunti sul PL/SQL

lunedì 6 aprile 2009 alle 06:37 | Pubblicato su PL/SQL | 1 commento
Tag: ,

Il PL/SQL come gli utenti Oracle sanno è il linguaggio di programmazione integrato nel motore DBMS Oracle che si integra anche con SQL. Una definizione più precisa e corretta non mi viene in mente.  Si tratta di un linguaggio procedurale che alcuni ritengono obsoleto e molto limitato, altri ritengono che sia validissimo e utilissimo. Secondo me la verità sta in mezzo, nel senso che è molto utile per determinate e precise tipologie di operazioni, in particolare io lo utilizzo solo per operazioni di caricamento, elaborazione massiva di dati, il cosidetto ETL del datawarehouse.

Francamente non mi capitano spessissimo lavori da fare in PL/SQL, per cui ho maturato una conoscenza del linguaggio che ritengo buona, ma ancora con qualche lacuna. Mi trovo spesso in difficoltà per alcuni limiti del PL/SQL e per mia mancanza di conoscenza approfondita dell’interzione fra motori  PL/SQL ed SQL .

Un esempio deriva da una questione emersa venerdì scorso su OraclePortal. Siccome da quella questione sono emersi due ottimi appunti ho deciso di fare un post qui dove ho facilità a ritrovare le cose.

La prima annotazione è la modalità delle chiamate delle funzioni PL/SQL, pur avendone parlato in passato, mi sfuggiva, fino a venerdì sera, una differenza fondamentale, ovvero che c’è una differenza tra il chiamare una funzione  PL/SQL da SQL, cioè inserendola in una query come ad esempio

SELECT funzione() from dual;

e chiamarla da un blocco PL/SQL, ad esempio;


begin
a := funzione();
end;

Una delle differenze più subdole è la modalità di passaggio dei parametri, nelle chiamate da SQL non è possibile utilizzare la chiamata di funzione con il passaggio dei parametri per nome, cosa che però all’interno di un blocco PL/SQL invece è lecita. Venerdì pomeriggio, un po’ frettolosamente, ho pensato che quello invece potesse essere il problema alla base della questione, cosa che invee non era, devo ringraziare il partecipante che ha chiarito il mio errore e spiegato la differenza, spero che mi rimanga in mente.

Il punto invece del problema sottoposto nel forum di OraclePortal invece era la gestione dei tipi dato. Francamente non ho nulla da aggiungere a quanto gia spiegato nelle due risposte che spiegano il problema e la soluzione. Una volta vista la spiegazione mi è diventato chiaro e mi ha fatto riflettere sul alcuni accorgimenti da utilizzare nella programmazione PL/SQL. Mi ha anche fatto venire in mente  un progetto a cui lavorai tre o quattro anni fa in cui creai un mostro, perché appunto per questi problemi di tipi, scrissi dei package che in sostanza avevano dei riferimenti incrociati, uno conteneva le definizioni dei tipi e le procedure principali (direi quelle pubbliche) mentre gli altri package contenevano delle procedure che riferivano i tipi definiti nel primo package e che venivano richiamate da una procedura sempre nel primo package, insomma un bel guazzabuglio. Non ricordo perché non feci un terzo package con la sola definizione del tipo …

Overloading in PL/SQL

venerdì 6 marzo 2009 alle 06:33 | Pubblicato su PL/SQL | 6 commenti

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

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)

Passare parametri a sottoprogrammi PL/SQL

lunedì 31 dicembre 2007 alle 31:25 | Pubblicato su Diario, PL/SQL | 1 commento

Sotto titolo: Errore nella documentazione del package UTL_FILE

Ultimo post dell’anno. Oggi è toccato a me presidiare l’ufficio nell’eventualità di emergenze dell’ultimo giorno dell’anno. Sperando non accada nulla trovo il tempo per quest’ultimo post. Recentemente non sono riuscito a scrivere molto a causa di lavori di manutenzione edilizia a casa che mi hanno impegnato notevolmente.

Pochi giorni fa, scrivendo una procedure in PL/SQL mi sono scontrato con una mia carenza che però mi pare anche una carenza della documentazione Oracle. Stando alla documentazione un sotto-programma PL/SQL, fra cui dovrebbero rientrare procedure e funzioni, si può invocare passando i parametri i tre modalità:

  1. secondo l’ordine,
  2. per nome (uso di =>)
  3. mista .

Ora, non ne ho ancora trovato riscontro nella documentazione, ma nel caso di funzioni pare che l’unica modalità valida sia la prima, ovvero quella posizionale. Se si prova la modalità per nome si riceve l’errore:

ERRORE alla riga 2:
ORA-00907: parentesi chiusa mancante

Devo precisare che il problema che ho riscontrato è stato con la versione 9iR2 di Oracle.

Per farla breve dovevo fare una procedura di caricamento dati da un file di testo, a tal scopo ho usato le “External Tables”, però non volendo modificarne la definizione dinamicamento ho scritto una procedura che riceve in input il nome del file come viene passato (ogni volta il file ha un nome diverso) e come fase preliminare lo copia con il nome usato nella definizione della tabella esterna. Per fare ciò ho utilizzato il “sotto-programma” FCOPY del package UTL_FILE. Ho scritto “sotto-programma” perchè mentre nella documentazione 9iR2 si parla di funzione, in 10gR2 FCOPY è diventato procedura. In realtà in 9iR2 andando a vedere la definzione del package di scopre che si tratta di procedura. Stando alla documentazione attuale della versione 10gR2 la procedura è così definita:



UTL_FILE.FCOPY (
   location   IN VARCHAR2,
   filename   IN VARCHAR2,
   dest_dir   IN VARCHAR2,
   dest_file  IN VARCHAR2,
   start_line IN PLS_INTEGER DEFAULT 1,
   end_line   IN PLS_INTEGER DEFAULT NULL);
 
Se però si va a vedere la definizione del package UTL_FILE nello schema SYS, si trova:
 
/*
** FCOPY - Copy all or part of a file to a new file.
**
** IN  location     - source directory of file
** IN  filename     - source file name (including extention)
** IN  dest_dir     - destination directory of file
** IN  dest_file    - destination file name (including extention)
** IN  start_line   - line number from which to begin copying, default is
**                         1 referring to the first line in the file
** IN  end_line     - line number from which to end copying, default is NULL
**                         referring to end-of-file
** EXCEPTIONS
**   invalid_path      - not a valid file handle
**   invalid_filename  - file not found or file name is NULL
**   invalid_lineno    - bad start_line or end_line value
*/
PROCEDURE fcopy(src_location  IN VARCHAR2,
src_filename  IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line    IN BINARY_INTEGER DEFAULT 1,
end_line      IN BINARY_INTEGER DEFAULT NULL);
PRAGMA RESTRICT_REFERENCES(fcopy, WNDS, RNDS, TRUST);
 

 

E in 9iR2 è identica. Quindi i nomi dei parametri nella documentazione sono sbagliati e se si cerca di usare tale procedura passando i parametri con la notazione per nome (utl_file.fcopy(location=>’DIR’ ….) si otterrà l’errore:



ERRORE alla riga 1:
ORA-06550: riga 1, colonna 22:
PLS-00201: l'identificativo 'LOCATION' deve essere dichiarato
ORA-06550: riga 1, colonna 7:
PL/SQL: Statement ignored

 

Devo precisare che grazie alla decisione di documentare qui questo errore della documentazione Oracle mi sono accorto che non avevo del tutto capito quanto c’era di sbagliato. In un primo momento avevo pensato che veramente FCOPY in 9iR2 fosse una funzione poi trasformata in procedura nella 10gR2. Invece si è trattato solo di una correzione (parziale) della documentazione.

Pagina successiva »

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