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 >_at_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
1 row selected.
Why can does the first function not compile and return PLS-00386?
Thanks,
Christoph
--
"Men do not quit playing because they grow old; they grow old because they
quit playing."
- Justice Oliver Wendell Holmes
--
http://www.freelists.org/webpage/oracle-l
