PLS-00386: type mismatch found between FETCH cursor and INTO variables
12,585
Solution 1
you'd need to use the object constructor on the select:
SELECT OBJTYP(A, B, C)
FROM my_table
WHERE Study_Number = p_StudyNum(i)
but you can simplify the procedure to this instead of all those loops:
begin
select cast(multiset(select /*+ cardinality(s, 10) */ a, b, c
from my_table t, table(p_StudyNum) s
where t.study_number = s.column_value) as OutputTyp)
into p_StdyDtl
from dual;
end;
Solution 2
Try declaring your cursor as:
CURSOR c_StudyTbl
IS
SELECT OBJTYP(A, B, C)
FROM my_table
WHERE Study_Number = p_StudyNum(i);
Author by
Vaishali Bulusu
Updated on June 14, 2022Comments
-
Vaishali Bulusu almost 2 years
The following package throws : PLS-00386: type mismatch found at 'V_STUDYTBL' between FETCH cursor and INTO variables
Purpose of the code: Define two types outside the package, one is used to send a bunch of numbers into the stored proc and the other is used to return the corresponding rows from my_table
Thank you in advance for the inputs.
Create OR REPLACE Type InputTyp AS VARRAY(200) OF VARCHAR2 (1000); CREATE TYPE OBJTYP AS OBJECT ( A NUMBER, B VARCHAR2 (1000), C VARCHAR2 (100) ); CREATE TYPE OutputTyp IS VARRAY (2000) OF OBJTYP; / CREATE OR REPLACE PACKAGE my_package AS PROCEDURE my_procedure(p_StudyNum IN InputTyp, p_StdyDtl OutputTyp); END my_package; / CREATE OR REPLACE PACKAGE BODY my_package AS PROCEDURE MyProcedure(p_StudyNum IN InputTyp, p_StdyDtl OutputTyp) IS i BINARY_INTEGER := 1; j BINARY_INTEGER := 1; CURSOR c_StudyTbl IS SELECT A, B, C FROM my_table WHERE Study_Number = p_StudyNum(i); v_StudyTbl OBJTYP; BEGIN p_StdyDtl := OutputTyp (); LOOP -- This is the first cursor opened for each of the items in the list. EXIT WHEN i > p_StudyNum.count; OPEN c_StudyTbl; LOOP FETCH c_StudyTbl INTO v_StudyTbl; EXIT WHEN c_StudyTbl%NOTFOUND; p_StdyDtl.EXTEND (); p_StdyDtl (j).A := v_StudyTbl.A; p_StdyDtl (j).B := v_StudyTbl.B; p_StdyDtl (j).C := v_StudyTbl.C; j := j + 1; END LOOP; CLOSE c_StudyTbl; i := i + 1; END LOOP; IF c_StudyTbl%ISOPEN THEN CLOSE c_StudyTbl; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END my_package; /