Re: EXEC syntax for SP which returns row set

m0002a_at_yahoo.com wrote:
> I have a package and stored procedure as follows:
>
> create or replace package TEST.ReturnCur is
> -- REF CURSOR type for returning result sets from procedures
> TYPE REF_CUR IS REF CURSOR;
> END ReturnCur;
>
>
> CREATE or replace PROCEDURE TEST.SPTEST
> (C1 OUT ReturnCur.REF_CUR)
> AS
> BEGIN
> open C1 for SELECT ID from TEST.EMPLOYEE;
> END;
>
>
> How do I test this procedure from SQL*Plus.

That is not *this* procedure, to test *this* procedure, your call should look like "exec test.sptest(my_cur_variable)"
> When I use: exec sec.test2
>
> I get the following error message:
>
> BEGIN sec.test2; END;
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST2'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> When I use: exec sec.test2()
>
> I get the following error message:
>
> BEGIN sec.test2(); END;
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST2'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
>
> When I use [ID is char(8)]: exec sec.test2('12345678')
>
> I get:
>
> BEGIN sec.test2('12345678'); END;
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST2'
> ORA-06550: line 1, column 17:
> PLS-00363: expression '12345678' cannot be used as an assignment target
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>

Well, assuming , you will test your SPTEST procedure and not SEC.TEST2 procedure ( that i don't see any reference to it ), it could look like :

scott_at_ORA92> create table employee as select empno id from emp;

Table created.

scott_at_ORA92> create or replace package returncur is

   2 type ref_cur is ref cursor;
   3 end returncur;
   4 /

Package created.

scott_at_ORA92> create or replace procedure sptest(c1 out returncur.ref_cur)

   2 as
   3 begin
   4 open c1 for select id from employee;    5 end;
   6 /

Procedure created.

scott_at_ORA92> declare

   2 t returncur.ref_cur;
   3 l_id employee.id%type;
   4 begin
   5 sptest(t);
   6 loop
   7 fetch t into l_id;
   8 dbms_output.put_line(l_id);
   9 exit when t%notfound;
  10 end loop;
  11 end;
  12 /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
7934

PL/SQL procedure successfully completed.

In general , you should call stored procedures always with parameters which they expect ( i.e. stored procedure created with parameter of type REF CURSOR should be called with parameter of same type)

For further reading i would recommend

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch4.htm#sthref840

There are also some good books available ...

Best regards

Maxim Received on Thu Jul 14 2005 - 01:54:28 CDT