Re: stored proc problem

From: ykhogaly <ykhogaly_at_us.oracle.com>
Date: Fri, 25 Feb 2000 09:06:42 -0700
Message-ID: <8969bm$99v$1@inet16.us.oracle.com>

Hi,

create or replace package cursor_weak as   type my_cursor is REF CURSOR;
  procedure open_cursor_dept (my_cur in out my_cursor);   procedure open_cursor_emp (my_cur in out my_cursor);   end;

  Create or replace package body cursor_weak as   procedure open_cursor_dept (my_cur in out my_cursor) is   begin
  open my_cur FOR SELECT * FROM dept;
  end;
  procedure open_cursor_emp (my_cur in out my_cursor) is   begin
  open my_cur FOR SELECT * FROM emp;
  end;
  end;

/*-------------pro*c calling the package---------------*/
  #include ....
  struct dept_rec { int deptno;
  char dname[15];
  char loc[14];
  };
  struct emp_rec { .....};

  EXEC SQL BEGIN DECLARE SECTION;
  SQL_CURSOR my_cur;
  struct dept_rec department;
  struct emp_rec employee;
  EXEC SQL END DECLARE SECTION; n(){
/* After Connect .... */

  EXEC SQL ALLOCATE :my_cur;
  EXEC SQL EXECUTE BEGIN
  cursor_weak.open_cursor_dept(:my_cur);   END;
  END-EXEC;
  EXEC SQL FETCH :my_cur INTO :department;   EXEC SQL CLOSE :my_cur;

  EXEC SQL EXECUTE BEGIN
  cursor_weak.open_cursor_emp(:my_cur);   END;
  END-EXEC;   EXEC SQL FETCH :my_cur INTO :employee;   EXEC SQL CLOSE :my_cur;
  ....

  Same results can be achieved by using proc dynamic method 3 in a much simpler way.

More examples I hope they could be of a help.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

 p1.sql

---

  create or replace package mayi1 As
  type empTab is table of emp%ROWTYPE INDEX by binary_integer ;   cursor c1 is select * FROM EmP ORDER by enamE ;   procedure pr1 ( emp_tab out empTab ) ;   function f1 return empTab ;
  end mayi1 ;
/

  show errors

  create or replace package body mayI1 AS   procedure pr1 (emp_tab out empTab) is   begin

         open c1 ;
         for i in 1 .. 10 loop
             fetch c1 into    emp_tab(i) ;
         end loop;
         close c1;

  end pr1 ;
  function f1 return empTa
b is
  f_tab empTaB;
  begin

         open c1;
         for i in 1 .. 10 loop
       fetch c1 into f_tab(i) ;
           end loop;
         close c1 ;
         return f_tab;

  end f1;

  end mayI1 ;
/

  show errors
  ~
  ~
  p2.sql

---

  declare

     etab mayi1.empTab;
  begiN

      mayi1.pr1(etab) ;
      for i in 1 .. 10 loop
         dbms_output.put_line(etab(i).ename  ) ;
      end loop;

  end;
/

  ~
  ~
  p3.sql

---

  create or replace procedure p3( out_tbl out MAYI1.EmpTab ) is   begin

       out_tbl := mayi1.f1 ;


  /*     foR I In 1 .. 10 loop


         dbms_output.put_line(out_tbL(I).ENAME  ) ;
      end loop; */

  end;
/

  show errors
  p4.sql

---

  CREATE OR REPLACE PACKAGE samp_pkg AS   TYPE dept_cur is REF CURSOR RETURN SCOTT.DEPT%ROWTYPE;   TYPE emp_cur IS REF CURSOR RETURN mayi1.empTaB;   PROCEDURE open_dept(LOC_PARM_IN VARCHAR2);   PROCEDURE open_emp(DEPTNO_PARM_IN NUMBER);   END samp_pkg;
/

  SHO ERROR   CREATE OR REPLACE PACKAGE BODY samp_pkg AS   PROCEDURE open_dept (LOC_PARM_IN VARCHAR2) IS   c1 dept_cur;
  deptrec SCOTT.DEPT%ROWTYPE;
  BEGIN
  OPEN c1 for
  SELECT * FROM SCOTT.DEPT
  WHERE LOC = LOC_PARM_IN;
  LOOP
  EXIT WHEN c1%NOTFOUND;
  FETCH c1 INTO deptrec;
  DBMS_OUTPUT.PUT_LINE(deptrec.deptno);   open_emp(deptrec.deptno);
  END LOOP;
  CLOSE c1 ;
  END open_dept;

  PROCEDURE open_emp (DEPTNO_PARM_IN NUMBER) IS   c2 emp_cur;
  emprec emp%ROWTYPE;
  BEGIN
  OPEN c2 for
  SELECT * FROM SCOTT.EMP
  WHERE DEPTNO = DEPTNO_PARM_IN;
  loop
  exit when c2%NOTFOUND;
  fetch c2 into emprec;
  DBMS_OUTPUT.PUT_LINE(emprec.empno);
  end loop;
  CLOSE c2;
  END open_emp;
  END samp_pkg;
/

  SHO ERROR "kev" <kevin.porter_at_fast.no> wrote in message news:38B69F19.FE2F2F50_at_fast.no...

> Hi,
>
> I am trying to get a stored proc to return a ref cursor. Here's the code
> I have at the moment:
>
> create or replace package types
> as
>         type cursorType is ref cursor;
> end;
> /
>
> create or replace procedure retcurs_proc
> (mycurs out types.cursorType)
> is
> begin
> open mycurs for select * from emp;
>
> end retcurs_proc;
> /
>
>
> This compiles with no errors. When I do exec retcurs_proc(), I get this
> error:
>
> PLS-00306: wrong number or types of arguments in call to 'RETCURS_PROC'
>
> Why do I get the error? The procedure has no IN variables so how is
> there a wrong number of parameters in the above call?
>
> thanks,
>
> - Kev
>

Received on Fri Feb 25 2000 - 10:06:42 CST