Andy, thanks a bunch for the solution. Christoph On Wed, Sep 7, 2011 at 9:14 AM, Andy Klock <andy@xxxxxxxxxxxxxxx> 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@xxxxxxxxx> 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 >@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 >> >> >> -- >> //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 -- //www.freelists.org/webpage/oracle-l