Re: PLS-00386 when fetching into a previously declared type
- From: Andy Klock <andy@xxxxxxxxxxxxxxx>
- To: cruepprich@xxxxxxxxx
- Date: Wed, 7 Sep 2011 10:14:23 -0400
They're not the same though. One is an object (a class with attributes,
methods, etc) and the other is a PL/SQL record. You can still use your
object, you'll just need to use t_emp's constructor method.
CREATE OR REPLACE FUNCTION emp_fn RETURN NUMBER IS
l_emp t_emp;
CURSOR c1 IS
SELECT t_emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
FROM scott.emp;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO l_emp;
EXIT WHEN c1%NOTFOUND;
END LOOP;
RETURN 0;
END;
15 /
Function created.
SQL> select emp_fn from dual;
EMP_FN
----------
0
On Tue, Sep 6, 2011 at 3:52 PM, Christoph <cruepprich@xxxxxxxxx> wrote:
> I 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
>
>
>
>
> --
> "Men do not quit playing because they grow old; they grow old because they
> quit playing."
> - Justice Oliver Wendell Holmes
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
