Re: pl/sql procedure call problem / array OUT

On Sun, 7 Apr 2002 00:34:33 +0200, "cel" <cel975_at_yahoo.com> wrote:

>Hi,
>
>I have to test a procedure which has a parameter OUT that returns an array.
>In the package Packyyy, I have defined structures, types, arrays etc ...
>In the package Packxxx, I have defined a procedure proc1 which has a
>parameter OUT that returns an array,
>defined in the package Packyyy.
>
>----------------------------------------
>CREATE OR REPLACE PACKAGE Packyyy AS
>
> -- Structures declaration
> TYPE XYZ IS RECORD ( var11 VARCHAR2(15),
> var12 VARCHAR2(1),
> Var13 VARCHAR2(15),
> var14 VARCHAR2(50));
>
>TYPE the_array IS TABLE OF XYZ INDEX BY BINARY_INTEGER;
>etc ...
>---------------------------------------
>Package Packxxx
>....
>PROCEDURE proc1(var1 IN VARCHAR2,
> var2 IN VARCHAR2,
> var3 IN VARCHAR2,
> var4 OUT Packyyy.the_array,
> var5 OUT VARCHAR2,
> var6 OUT VARCHAR2,
> var7 OUT NUMBER,
> var8 OUT VARCHAR2) ;
>etc ...
>-------------------------------------
>
>How do i modify the following script which doesnt work because of the type
>of the variable var4 ?
>Test proc1 : test_proc1.sql
>
>SET SERVEROUTPUT ON
>DECLARE
>var1 VARCHAR2:='aa';
>var2 VARCHAR2:='bb';
>var3 VARCHAR2:='cc';
>var4 Packyyy.the_array; -- problem here
>var5 VARCHAR2;
>var6 VARCHAR2;
>var7 NUMBER;
>var8 VARCHAR2;
>
>BEGIN
>
>SchemaName.Packxxx.proc1(var1, var2,var3,var4,var5,var6,var7,var8);
>
>DBMS_OUTPUT.PUT_LINE('var1 = ' || var1);
>DBMS_OUTPUT.PUT_LINE('var2 = ' || var2);
>DBMS_OUTPUT.PUT_LINE('var3 = ' || var3);
>DBMS_OUTPUT.PUT_LINE('var4 = ' || var4); -- to display an array ??
>DBMS_OUTPUT.PUT_LINE('var5 = ' || var5);
>DBMS_OUTPUT.PUT_LINE('var6 = ' || var6);
>DBMS_OUTPUT.PUT_LINE('var7 = ' || var7);
>
>END;
>/
>
>Any idea ? thanks in advance
>
>
>

Please do NOT cross AND multipost. Obviously you did multipost as Thomas Kyte's answer doesn't show up in other groups. It is that I monitor all groups, otherwise I would have wasted my time responding again, after a correct answer already has been posted

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Apr 07 2002 - 00:48:20 CST