CODEWITHSUNDEEP
oracle-databaseplsqltypes
Gábor Varga
Gábor Varga

Reputation: 840

PL SQL table parameter

I would like pass a table type to a procedure as parameter.

create or replace package FOO is
    type FOOTYPE is record (
        FOOTYPE_A varchar2(5) null,
        FOOTYPE_B varchar2(5) null,
        FOOTYPE_C varchar2(5) null
    ); 

    type FOOTYPETABLE is table of FOOTYPE;
...
    procedure sendNew (
        table_a in FOOTYPETABLE
    ) is
        a number;
    begin
       ...
    end sendNew;
...
end FOO;

declare
    type bartabletype is table of FOO.FOOTYPE index by binary_integer;
    bartable bartabletype;
    begin
        bartable(0).FOOTYPE_A := '';
        bartable(0).FOOTYPE_B := '';
        bartable(0).FOOTYPE_C := '';
        bartable(1).FOOTYPE_A := '';
        bartable(1).FOOTYPE_B := '';
        bartable(1).FOOTYPE_C := '';
    FOO.sendNew(bartable);
end;

But Oracle says:

"ora-00306 wrong number or types of arguments".

Why?

Upvotes: 0

Views: 13188

Answers (3)

schurik
schurik

Reputation: 7928

bartable variable must be of type FOO.FOOTYPETABLE and not bartabletype

Upvotes: 0

tbone
tbone

Reputation: 15473

You are trying to pass in an associative array (index by) when its expecting a pl/sql table (nested table). For example, do this:

create or replace package tpkg as
  type t_myrec is record (
      val1 varchar2(1000),
      val2 varchar2(1000)
  );
  type t_myrec_tab is table of t_myrec;

  procedure recv_ary(i_ary in t_myrec_tab);
end;

create or replace package body tpkg as
    procedure recv_ary(i_ary in t_myrec_tab) is
    begin
      -- do something here
      dbms_output.put_line('Array has ' || i_ary.count || ' elements');
    end;
end;

And use it:

declare
  some_ary tpkg.t_myrec_tab;
begin
  select object_name, object_type
  bulk collect into some_ary
  from user_objects
  where rownum <= 100;

  tpkg.recv_ary(some_ary);
end;

Notice I declared "some_ary" as type "tpkg.t_myrec_tab". In other words, I referenced the package type specifically, so I know its the correct type of collection.

Upvotes: 5

mustaccio
mustaccio

Reputation: 18945

It's probably because FOOTYPETABLE, which is declared as the procedure sendNew() parameter type, is not the same as table of FOO.FOOTYPETABLE, which you are attempting to pass to it.

Upvotes: 0

Related Questions