Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Ref Cursors ...
Regarding Ref Cursors ... [message #193455] Sun, 17 September 2006 03:50 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi pals,


I have any doubt regarding ref cursors. Can anyone suggest what could be the problem in the following code.


Create or replace package MyPack
As
TYPE resultset IS REF CURSOR;
procedure p1(v_rs OUT resultset);
end;
/
create or replace package body MyPack
as
procedure p1(v_rs OUT resultset)
as
qry varchar2(100);
begin
qry:='select ename,dname from emp e,dept d where e.deptno=d.deptno' ;
open v_rs for qry;
end;
end;
/

Now i tried to execute the above stored procedure as follows by writing a small PL/SQL block. but it is giving error. But i am successfull using 2nd way i.e using bind variables.

Way-1
======

declare
v_rs MyPack.resultset;
i v_rs%rowtype;
begin
MyPack.p1(v_rs);
loop
fetch v_rs into i;
if(v_rs%NOTFOUND) then
exit;
end if;
dbms_output.put_line(i.deptno||' '||i.dname||' '||i.loc);
end loop;
close v_rs;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
/
i v_rs%rowtype;
*
ERROR at line 3:
ORA-06550: line 3, column 5:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 3, column 5:
PL/SQL: Item ignored
ORA-06550: line 7, column 19:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 25:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 11, column 4:
PL/SQL: Statement ignored


SQL>
Way-2
======
But if i am tring the below one iam getting my required ouput
variable rs refcursor

exec MyPack.p1(:rs)
SQL> print rs

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

14 rows selected.

SQL>


But why i am not able to get the same output using way-1.
Is there any wrong in my PL/SQL client block.

Plz help me. I want to know that.

Thanks in advance,
Frank





Re: Regarding Ref Cursors ... [message #193471 is a reply to message #193455] Sun, 17 September 2006 12:34 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
During parse stage parser doesn't know which type will be returned by procedure. You have to tell him that.

If you change your code with following one, it will work ;

create or replace package body MyPack
as
  procedure p1(v_rs OUT resultset)
  as
    qry varchar2(100);
  begin
    qry:='select d.*  from emp e,dept d where e.deptno=d.deptno' ;
    open v_rs for qry;
end;


declare
  v_rs MyPack.resultset;
  i dept%rowtype;
begin
  MyPack.p1(v_rs);
  loop
    fetch v_rs into i;
    if(v_rs%NOTFOUND) then
      exit;
    end if;
    dbms_output.put_line(i.deptno||' '||i.dname||' '||i.loc);
  end loop;
  close v_rs;
EXCEPTION
  WHEN OTHERS THEN
     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;


[Updated on: Sun, 17 September 2006 12:36]

Report message to a moderator

Re: Regarding Ref Cursors ... [message #193507 is a reply to message #193471] Mon, 18 September 2006 00:15 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
This is coming but in the SELECT stmt if i have to select columns from both the tables then i am getting the error. Why?

Any suggestions.

Thanks in advance ,
Frank
Re: Regarding Ref Cursors ... [message #193518 is a reply to message #193507] Mon, 18 September 2006 01:44 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Here is an example. I hope it will help Smile

CREATE OR REPLACE PACKAGE mypack
AS
   TYPE resultset IS REF CURSOR;
   PROCEDURE p1 (v_rs OUT resultset);
END;
/

CREATE OR REPLACE PACKAGE BODY mypack
AS
   PROCEDURE p1 (v_rs OUT resultset)
   AS
      qry   VARCHAR2 (100);
   BEGIN
      qry := 'select e.empno, e.ename, e.job, d.deptno, d.dname, d.loc from emp e,dept d where e.deptno=d.deptno';
      OPEN v_rs FOR qry;
   END;
END;
/

DECLARE
   v_rs   mypack.resultset;
   TYPE t_emp_dep IS RECORD (
      empno    emp.empno%TYPE,
      ename    emp.ename%TYPE,
      job      emp.job%TYPE,
      deptno   dept.deptno%TYPE,
      dname    dept.dname%TYPE,
      loc      dept.loc%TYPE
   );
   i      t_emp_dep;
BEGIN
   mypack.p1 (v_rs);
   LOOP
      FETCH v_rs
       INTO i;
      IF (v_rs%NOTFOUND)
      THEN
         EXIT;
      END IF;
      DBMS_OUTPUT.PUT_LINE (i.deptno || ' ' || i.dname || ' ' || i.loc);
   END LOOP;
   CLOSE v_rs;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE_APPLICATION_ERROR (-20001, 'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END;

Re: Regarding Ref Cursors ... [message #193678 is a reply to message #193518] Mon, 18 September 2006 23:38 Go to previous message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi aorehek,

Thanks a lot.You r really too good.

Previous Topic: When to create an index and use hints
Next Topic: E.F.COdd
Goto Forum:
  


Current Time: Sat Dec 03 18:08:25 CST 2016

Total time taken to generate the page: 0.10152 seconds