Re: PLS-00386 when fetching into a previously declared type

From: Andy Klock <andy_at_oracledepot.com>
Date: Wed, 7 Sep 2011 10:14:23 -0400
Message-ID: <CADo_RaM3bk0Fy+Jd2_9QCKpJ+PPF543t45DWDvdm7hsZAif3fA_at_mail.gmail.com>

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_at_gmail.com> 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 >_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
> ----------
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 07 2011 - 09:14:23 CDT