FAQ
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



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

Search Discussions

  • Andy Klock at Sep 7, 2011 at 2:14 pm
    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


    On Tue, Sep 6, 2011 at 3:52 PM, Christoph 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
    ----------


    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
  • Christoph at Sep 7, 2011 at 2:35 pm
    Andy,
    thanks a bunch for the solution.
    Christoph
    On Wed, Sep 7, 2011 at 9:14 AM, Andy Klock wrote:

    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
    ----------



    On Tue, Sep 6, 2011 at 3:52 PM, Christoph 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
    ----------


    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

    --
    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 6, '11 at 7:52p
activeSep 7, '11 at 2:35p
posts3
users2
websiteoracle.com

2 users in discussion

Christoph: 2 posts Andy Klock: 1 post

People

Translate

site design / logo © 2018 Grokbase