PLS-00386 when fetching into a previously declared type

From: Christoph <cruepprich_at_gmail.com>
Date: Tue, 6 Sep 2011 14:52:30 -0500
Message-ID: <CAJdEiBr5-wzBaGH07Gq1vMdYJvTYyeQO8y5Sv-mfoB4gY7E7Zg_at_mail.gmail.com>

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

Received on Tue Sep 06 2011 - 14:52:30 CDT