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

On Wed, Sep 7, 2011 at 9:14 AM, Andy Klock <andy_at_oracledepot.com> 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
> ----------
> 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
>>
>>
>>
>

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