pl/sql procedure call problem / array OUT

From: cel <cel975_at_yahoo.com>
Date: Sun, 7 Apr 2002 00:35:30 +0200
Message-ID: <3caf777c$0$11112$626a54ce_at_news.free.fr>

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 Received on Sun Apr 07 2002 - 00:35:30 CEST