Ciao a tutti, sto facendo una Stored Procedure Oracle, o meglio sto convertendo una stored procedure da SQL SERVER a ORACLE, diciamo che sono riuscito quasi in tutto tranne in un punto, quando devo assegnare una variabile tramite una select mi da errore, di seguito vi riporto la stored procedure:

CREATE OR REPLACE PROCEDURE "POPOLAPARTI" as
StructAnal VARCHAR(1):='4';
StructAnagr VARCHAR(1):='A';
CURSOR images_cursor IS
SELECT PA_SIGLA, PA_IM_CT FROM TPPAC1 WHERE PA_SIGLA like '__' || StructAnal;
sigla VARCHAR(3);
img VARCHAR(10);
len1 VARCHAR(10);
len2 number(4);
len3 number(4);
len4 number(4);
len5 number(4);
len6 number(4);
len7 number(4);
len8 number(4);
BEGIN
OPEN images_cursor;
LOOP
FETCH images_cursor INTO sigla,img;
exit when images_cursor%NOTFOUND;
len1 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,1,1)),0) as int);
len2 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,2,1)),0) as int);
len3 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,3,1)),0) as int);
len4 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,4,1)),0) as int);
len5 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,5,1)),0) as int);
len6 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,6,1)),0) as int);
len7 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,7,1)),0) as int);
len8 := CAST(NVL((SELECT IA_LENGTH FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,8,1)),0) as int);

INSERT INTO PARTI VALUES (
SUBSTR(sigla,1,2),
1, len1,
(len1+1), len2,
(len1+len2+1), len3,
(len1+len2+len3+1), len4,
(len1+len2+len3+len4+1), len5,
(len1+len2+len3+len4+len5+1), len6,
(len1+len2+len3+len4+len5+len6+1), len7,
(len1+len2+len3+len4+len5+len6+len7+1), len8);

END LOOP;
CLOSE images_cursor;
END "POPOLAPARTI";


Quando assegno le variabili len ho errore, ho provato mettendo un valore fisso e tutto va senza problemi.