4 Replies Latest reply on Sep 6, 2011 9:11 PM by 742417

    PLS-00386 when fetching into a previously declared type.

    742417
      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
        • 1. Re: PLS-00386 when fetching into a previously declared type.
          Solomon Yakobson
          Since you are trying to fetch into an object, your SELECT must select object:
          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  emp;
              BEGIN
                  OPEN c1;
                  LOOP
                    FETCH c1
                      INTO l_emp;
                    EXIT WHEN c1%NOTFOUND;
                  END LOOP;
                  RETURN 0;
          END;
          /
          SY.
          • 2. Re: PLS-00386 when fetching into a previously declared type.
            742417
            Thank you so much for the quick reply. It makes sense now.
            BTW: What do you do to format the code nicely like you did?

            Thanks,
            Christoph
            • 3. Re: PLS-00386 when fetching into a previously declared type.
              Frank Kulash
              Hi, Christoph,
              Christoph wrote:
              ... BTW: What do you do to format the code nicely like you did?
              This site normally doesn't display multiple spaces in a row.
              Whenever you post formatted text (such as query results) on this site, type these 6 characters:

              \
              (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
              • 4. Re: PLS-00386 when fetching into a previously declared type.
                742417
                SELECT 'Thanks a lot!'
                  FROM dual;
                ;)