Re: Passing in arrays to a stored procedure

From: Maurécio <mbf_at_uniconsult.com.br>
Date: Wed, 01 Sep 1999 17:00:30 GMT
Message-ID: <7qjm36$r8f$1@nnrp1.deja.com>

Hi Vijav,

fortunatelly it is possible (as long as you are in Forms 5 or 6):

First you create a package with the definition of a type which is your array or "pl/sql table" (with whatever components you want).

Then you define the parameter of your procedure using this type; in forms you declare a variable of this type, fill it and pass it! See:

1. (in sql*plus) create or replace package pc_types is

type my_rec is record (v1 varchar2(2000), v2 number, v3 date); type my_arr is table of my_rec index by binary_integer;

end pc_types;
/

create or replace procedure my_proc

                            (x pc_types.my_arr, n binary_integer) is

begin
  for i in 1..n
  loop
    ...
    do_something_with(x(i).v1, x(i).v2, x(i).v3)     ...
  end loop;
end;

Don't forget that access to an unidefined position of a pl_sql_table gives you no_data_found...

Hope this helps.

Regards,
Maurício

In article <37cd38dd_at_news1.us.ibm.net>,   "Vijay Mayadas" <vijay_at_winwin.com> wrote:
> Hi
>
> Is it possible to pass in an array of vchars into a PL/SQL stored
procedure.
> If not, are there any convenient work arounds?
>
> Thanks
>
> Vijay
>
>

--
Maurício B. Falleiros <mbf_at_uniconsult.com.br> Uniconsult Sistemas e Serviços

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 12:00:30 CDT