Re: How to test procedure return multi-rows in SQL-PLUS

A copy of this was sent to "Tony Yu" <tonyyu_at_pathcom.com> (if that email address didn't require changing) On Thu, 25 Nov 1999 22:36:24 -0500, you wrote:

>Hi, All
>I have a problem with testing procedure which return multi-rows in SQL-Plus.
>
>In SQL-PLUS
>
>declare
>type abc is varray(30) of varchar2(20);
>begin
>pack.person(abc);
>end;
>

you are trying to pass a TYPE to a procedure -- not even a variable in the above. The types don't match either. the correct code would be:

declare

   abc pack.cde;
begin

   pack.person(abc);
end;

NEVER redefine a type that you made in a package spec. even though you and i both know that a table of varchar2(30) is the same -- the compiler wants you to use the SAME exact type.

You should use ref cursors for this operation anyway. see the url in my signature for a HOWTO on refcursors. your example should be:

scott_at_8i> create or replace package pack   2 as
  3 type rc is ref cursor;
  4
  5 procedure person( x in out rc );   6 end;
  7 /

Package created.

scott_at_8i>
scott_at_8i> create or replace package body pack   2 as
  3
  4 procedure person ( x in out rc )
  5 is
  6 begin
  7 open x for select ename, empno from emp;   8 end;
  9
 10
 11 end;
 12 /

Package body created.

scott_at_8i> 
scott_at_8i> variable x refcursor
scott_at_8i> 
scott_at_8i> exec pack.person(:x)

PL/SQL procedure successfully completed.

scott_at_8i> print x

ENAME EMPNO
---------- ----------

SMITH            7369
ALLEN            7499
WARD             7521
JONES            7566
MARTIN           7654
BLAKE            7698
CLARK            7782
SCOTT            7788
KING             7839
TURNER           7844
ADAMS            7876
JAMES            7900
FORD             7902
MILLER           7934

14 rows selected.

>I got an error which is "Wrong number or type of arguement".
>here is package:
>
>package pack as
>type cde is table of varchar2(20);
>begin
>procedure person( cde );
>end;
>
>package pack body as
>type cde is table of varchar2(20);
>begin
>proceduer person(cde)
>is
> declare
> cursor aa_cur is select aa from person;
> percount number :=1;
> begin
> for single_aa in aa_cur loop
> cde(percount) := single_aa.aa;
> percount := percount + 1;
> end loop;
> end;
>end ;
>
>Thanks in advance!!!
>
>Tony.
>E-mail: tonygyu_at_hotmail.com
>
>

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation