[PL/SQL] - Stored Procedure Oracle, Assegnazione variabile tramite select Opzioni

[dany144]

Ciao a tutti, devo convertire una stored procedure da SQL SERVER a ORACLE.
Ma ho un problema quando cerco di assegnare una variabile tramite una select, ossia nella stored procedure di seguito, quando assegno le variabili len.
Se gli assegno dei valori fissi (1,2,3...) funziona perfettamente.
Premetto che non so nulla di PLSQL e lo sto vedendo ora per la prima volta.

CODE

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";

[elir]

prova con il costrutto select..into..

CODICE

SELECT CAST(NVL(IA_LENGTH,0) as int) into len1 FROM TPPAIA WHERE IA_SIGLA = SUBSTR(sigla,1,2)||StructAnagr AND IA_TCONT=SUBSTR(img,1,1);