PLS-00386 when fetching into a previously declared type.
742417Sep 6 2011 — edited Sep 6 2011I received error PLS-00386 when trying to fetch a cursor into a variable based on an object:
SQL >-- Create type based on scott.emp
SQL >CREATE OR REPLACE TYPE t_emp AS OBJECT
2 (
3 empno NUMBER(4),
4 ename VARCHAR2(10),
5 job VARCHAR2(9),
6 mgr NUMBER(4),
7 hiredate DATE,
8 sal NUMBER(7, 2),
9 comm NUMBER(7, 2),
10 deptno NUMBER(2)
11 );
12 /
Type created.
SQL >
SQL >show error
No errors.
SQL >
SQL >-- Create a function that fetches records into t_emp:
SQL >
SQL >CREATE OR REPLACE FUNCTION emp_fn RETURN NUMBER IS
2 l_emp t_emp;
3 CURSOR c1 IS
4 SELECT * FROM emp;
5 BEGIN
6 OPEN c1;
7 LOOP
8 FETCH c1
9 INTO l_emp;
10 EXIT WHEN c1%NOTFOUND;
11 END LOOP;
12 RETURN 0;
13 END;
14 /
Warning: Function created with compilation errors.
SQL >
SQL >show error
Errors for FUNCTION EMP_FN:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5 PL/SQL: SQL Statement ignored
9/12 PLS-00386: type mismatch found at 'L_EMP' between FETCH cursor
and INTO variables
SQL >
Now when I declare the type exactly the same way inside the function, the function compiles and executes correctly:
SQL >@test_emp2
SQL >CREATE OR REPLACE FUNCTION emp_fn RETURN NUMBER IS
2
3 TYPE t_emp_rec IS RECORD(
4 empno NUMBER(4)
5 ,ename VARCHAR2(10)
6 ,job VARCHAR2(9)
7 ,mgr NUMBER(4)
8 ,hiredate DATE
9 ,sal NUMBER(7, 2)
10 ,comm NUMBER(7, 2)
11 ,deptno NUMBER(2));
12
13 l_emp t_emp_rec;
14
15 CURSOR c1 IS
16 SELECT * FROM emp;
17 BEGIN
18 OPEN c1;
19 LOOP
20 FETCH c1
21 INTO l_emp;
22 EXIT WHEN c1%NOTFOUND;
23 dbms_output.put_line( l_emp.empno);
24 END LOOP;
25 RETURN 0;
26 END;
27 /
Function created.
SQL >
SQL >show error
No errors.
SQL >
SQL >select emp_fn from dual;
EMP_FN
----------
0
1 row selected.
Why can does the first function not compile and return PLS-00386?
Thanks,
Christoph