Passing PL/SQL table as a parameter problem

From: <akragl_at_my-dejanews.com>
Date: Thu, 15 Apr 1999 18:26:37 GMT
Message-ID: <7f5b0i$uvt$1@nnrp1.dejanews.com>

Hi,

here is a problem I am encountering:

When executing a stored procedure (from a package) which needs several parameters (one of which is of type PL/SQL table) it returns an error:

"PLS-00306: wrong number or types of arguments in call to 'GET_OPEN_LISTS' "

It seems to me that number and type of parameters is OK, but... What am I missing here?

Here are the specs: Oracle 8.0.5

DEFINITION OF PACKAGE SPEC BEING INVOKED:

---

CREATE OR REPLACE PACKAGE NETT_OPEN_LISTS IS type ListTabTyp is table of VARCHAR2(20) index by binary_integer;

PROCEDURE Get_Open_Lists (p_Return_Array IN OUT ListTabTyp,
                          p_Count IN OUT NUMBER,
                          p_Status IN OUT VARCHAR2);

END NETT_OPEN_LISTS;
/

DEFINITION OF PACKAGE BODY BEING INVOKED:

---

CREATE OR REPLACE PACKAGE BODY NETT_OPEN_LISTS IS

PROCEDURE Get_Open_Lists (p_Return_Array IN OUT ListTabTyp,
                          p_Count IN OUT NUMBER,
                          p_Status IN OUT VARCHAR2)

IS

  CURSOR Get_Lists_Cur IS
    SELECT List_Name, List_Code
    FROM Lists
    WHERE UPPER(List_Type) = 'O'
    ORDER BY List_Name;

  i NUMBER;

BEGIN
   i := 0;

	FOR Get_Lists_Rec IN Get_Lists_Cur LOOP
        i := i + 1;
        p_Return_Array(i) := Get_Lists_Rec.List_Name;
	END LOOP;

   p_Count := i;
   p_Status := 'OK';

END Get_Open_Lists;

END Nett_Open_Lists;
/

AND, HERE IS THE PACKAGE AND PROCEDURE THAT CALLS THE PROCEDURE ABOVE:

---

CREATE OR REPLACE PACKAGE test IS

type ListTabTyp is table of VARCHAR2(20) index by binary_integer;

PROCEDURE array_test;

END test;
/

CREATE OR REPLACE PACKAGE BODY test IS

procedure array_test
is

        p_count number;
        p_lists ListTabTyp;
        p_status varchar2(10);
        i_loop  number;

begin
        Nett_Open_Lists.Get_Open_Lists (p_lists, p_count, p_status);
        i_loop := 1;
        dbms_output.put_line('Recalling the values for p_lists...');
        while (i_loop <= p_count) loop
                begin

dbms_output.put_line('p_lists('||i_loop||')='||p_lists(i_loop));
                end;
                i_loop := i_loop + 1;
        end loop;

end array_test;

END test;
/

---

Any help is much appreciated. Thanks!

Alex.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 15 1999 - 13:26:37 CDT