Re: Passing PL/SQL table as a parameter problem

akragl_at_my-dejanews.com wrote in message <7f5b0i$uvt$1_at_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

In the ARRAY_TEST procedure you have to make sure that you use the SAME type definition for the PL/SQL table variable, ie:

        p_lists nett_open_lists.ListTabTyp;

instead of

        p_lists ListTabTyp;

Thus, the second type definition in the TEST package is no longer necessary, you just use the type definition from NETT_OPEN_LISTS.

HTH. Finn Received on Fri Apr 16 1999 - 04:46:18 CDT