Re: pl/sql procedure call problem / array OUT

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Apr 2002 16:53:06 -0800
Message-ID: <a8o59i02eep@drn.newsguy.com>

In article <3caf7743$0$11100$626a54ce_at_news.free.fr>, "cel" says...
>
>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;
>

problem not where you say it is. The varchar2 := 'aa' is a problem as is all of the varchar2's there -- they are missing a length. to print out an array, you need to iterate over it and print out each element. Here is the full example:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE PACKAGE Packyyy AS   2
  3 -- Structures declaration
  4 TYPE XYZ IS RECORD ( var11 VARCHAR2(15),

  5      var12 VARCHAR2(1),
  6      Var13 VARCHAR2(15),
  7      var14 VARCHAR2(50));

  8
  9 TYPE the_array IS TABLE OF XYZ INDEX BY BINARY_INTEGER;  10 end;
 11 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace Package Packxxx
  2  as
  3  PROCEDURE proc1(var1        IN VARCHAR2,
  4    var2     IN VARCHAR2,
  5    var3     IN VARCHAR2,
  6    var4     OUT Packyyy.the_array,
  7    var5     OUT VARCHAR2,
  8    var6     OUT VARCHAR2,
  9    var7     OUT NUMBER,
 10    var8     OUT VARCHAR2) ;

 11 end;
 12 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace Package body Packxxx   2 as

  3  PROCEDURE proc1(var1        IN VARCHAR2,
  4    var2     IN VARCHAR2,
  5    var3     IN VARCHAR2,
  6    var4     OUT Packyyy.the_array,
  7    var5     OUT VARCHAR2,
  8    var6     OUT VARCHAR2,
  9    var7     OUT NUMBER,
 10    var8     OUT VARCHAR2)

 11 is
 12 begin

 13          var4(1).var12 := 'x';
 14          var4(1).var13 := 'Hello';
 15          var4(1).var14 := 'world';

 16 end;
 17
 18 end;
 19 /

Package body created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> SET SERVEROUTPUT ON
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> DECLARE
  2  var1        VARCHAR2(25):='aa';
  3  var2        VARCHAR2(25):='bb';
  4  var3        VARCHAR2(25):='cc';
  5  var4        Packyyy.the_array; -- problem here
  6  var5        VARCHAR2(25);
  7  var6        VARCHAR2(25);
  8  var7        NUMBER;
  9  var8        VARCHAR2(25);

 10
 11 BEGIN
 12
 13 Packxxx.proc1(var1, var2,var3,var4,var5,var6,var7,var8);  14

 15  DBMS_OUTPUT.PUT_LINE('var1 = ' || var1);
 16  DBMS_OUTPUT.PUT_LINE('var2 = ' || var2);
 17  DBMS_OUTPUT.PUT_LINE('var3 = ' || var3);

 18 -- DBMS_OUTPUT.PUT_LINE('var4 = ' || var4); -- to display an array ??  19 for i in 1 .. var4.count
 20 loop
21 dbms_output.put_line( var4(i).var12 || ',' || var4(i).var13 || ',' || var4(i).var14 );
 22 end loop;

 23  DBMS_OUTPUT.PUT_LINE('var5 = ' || var5);
 24  DBMS_OUTPUT.PUT_LINE('var6 = ' || var6);
 25  DBMS_OUTPUT.PUT_LINE('var7 = ' || var7);

 26
 27 END;
 28 /

var1 = aa
var2 = bb
var3 = cc

x,Hello,world

var5 =
var6 =
var7 =

PL/SQL procedure successfully completed.

>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
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Apr 06 2002 - 18:53:06 CST