Re: Passing pl/sql table as parameter problems

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 12 May 2003 12:07:39 -0700
Message-ID: <130ba93a.0305121107.65d9829e@posting.google.com>

admiral_freebee_at_hotmail.com (Hervé Ferreira) wrote in message news:<ca41e26c.0305120017.15230f6c_at_posting.google.com>...
> Hi,
>
> I've written a procedure that returns a pl/sql table as an
> outputparameter. In the procedure in which I call this procedure I
> want to store the table in a local pl/sql table. But I keep getting
> the following compilation error:
>
> 'Compilation errors for PACKAGE BODY EVAP.PCK_208E2_TEST
>
> Error: PLS-00306: wrong number or types of arguments in call to
> procedure 'BAR_LEEFTIJD'.
> Line: 130
> Text: PCK_208E2_BAREMA.bar_leeftijd(P_BAR_CODE => baremac,
> P_AANTAL_UREN => to_number(t_type_uur), P_DTVAN => v_datum, P_DTTOT =>
> ttot, P_BAR_KLASSE_CODE => klassecode, P_TYPE_LOON => typel,
> P_LEEFTIJD => leeftijd,'
>
> Originally I thought the problem was a type mismatch between the local
> pl/sql table and the type of the one I return from the procedure, but
> that doesn't seem to be the case.
>
> any suggestions would be very welcome.

You can not use structural matching for pl/sql tables (or associative array). Full name of the type must be used when delcaring variables. That means use types created in SQL or within the package.

• Use type in the package SQL> create or replace package my_pkg as 2 type pkg_tab_typ is table of varchar2(10) index by binary_integer; 3 procedure proc1 (in_tab in pkg_tab_typ); 4 end; 5 /

Package created.

SQL> create or replace package body my_pkg as   2 procedure proc1(in_tab in pkg_tab_typ) is   3 x pkg_tab_typ;
  4 begin
  5 x:=in_tab;
  6 dbms_output.put_line('first element: '||x(1)||' 2nd element:

'||x(2));
  7  end;
  8  end;

  9 /

Package body created.

• Use a local pl/sql table variable SQL> declare 2 type tab_typ is table of varchar2(10) index by binary_integer; 3 w tab_typ; 4 begin 5 w(1):='dog'; 6 w(2):='cat'; 7 my_pkg.proc1(w); 8 end; 9 / my_pkg.proc1(w); * ERROR at line 7: ORA-06550: line 7, column 1: PLS-00306: wrong number or types of arguments in call to 'PROC1' ORA-06550: line 7, column 1: PL/SQL: Statement ignored
• Use the "fulll name" of the user defined type SQL> set serveroutput on SQL> declare 2 w my_pkg.pkg_tab_typ; 3 begin 4 w(1):='dog'; 5 w(2):='cat'; 6 my_pkg.proc1(w); 7 end; 8 / first element: dog 2nd element: cat

PL/SQL procedure successfully completed.

SQL>

• Use SQL nested table type SQL> create type sql_tab_typ as table of varchar2(10); 2 /

Type created.

SQL> create or replace procedure my_proc(in_tab in sql_tab_typ) as   2 begin
  3 dbms_output.put_line('1st: '||in_tab(1)||' , 2nd: '||in_tab(2));   4 end;
  5 /

Procedure created.

• Must use the same SQL type name SQL> declare 2 x sql_tab_typ:=sql_tab_typ('cat','dog'); 3 begin 4 my_proc(x); 5 end; 6 / 1st: cat , 2nd: dog

PL/SQL procedure successfully completed.

• Get error even though the structure of the local variable is same as the
• procedure parameter SQL> declare 2 type y is table of varchar2(10); 3 x y; 4 begin 5 x(1):='cat'; 6 x(2):='dog'; 7 my_proc(x); 8 end; 9 / my_proc(x); * ERROR at line 7: ORA-06550: line 7, column 1: PLS-00306: wrong number or types of arguments in call to 'MY_PROC' ORA-06550: line 7, column 1: PL/SQL: Statement ignored

SQL>

• Jusung Yang

Received on Mon May 12 2003 - 14:07:39 CDT