Re: PL/SQL Tables (arrays)

Kevin J. Kirkpatrick wrote:
>
> I just constructed a package that contains two PL/SQL tables. I am
> developing with Oracles WebServer and what I am trying to do is pass an
> array from one procedure to the next. I can get the array loaded
> correctly but when I try to pass it to the next procedure but it crashes
> saying:
>
> PLS-00306: wrong number or types of arguments in call to '||'
>
> What I did was to pass the array thru a <form> similar to:
>
> <FORM NAME = profs ACTION =
> "http://........');
> htp.print(' <INPUT TYPE = hidden NAME = p_emp_no VALUE =
> '||p_emp_no||'>
> <input type = hidden name = "v_prof" value = '||v_prof||'>
> <input type = hidden name = "w_prof" value = '||w_prof||'>
> <INPUT TYPE = hidden NAME = p_discipline_id VALUE =
> '||p_discipline_id||'>
> </FORM>');
>
> Where v_prof is the actual array. I think the problem is that v_prof on
> the NAME side is a single element field whereas the value is multiple.
> Any insite on how to get this array passed to another procedure??

You need to loop through the values of v_prof and print each one as a separate name/value pair.

e.g., for a sequential table,
-- assuming v_prof_max defined above (or, in PL/SQL 2.3 and later, -- v_prof.count should work)
for v_prof_row in 1 .. v_prof_max
  htp.print(' <input type = hidden name = "v_prof" value = '

                 ||v_prof(v_prof_row)||'>');

Two notes:
1) If you pass PL/SQL tables directly from one procedure to another, the tables must be defined as the same type. It won't work for two different types even if they are based in the same underlying datatype. (this doesn't apply to these sorts of html transfers, but I thought I'd mention it, since many people here have been bitten by this). 2) When you pass arguments through html, they are all converted to VARCHAR2. Oracle will automatically convert some datatypes, but not if they are declared in a PL/SQL table. So if you want to pass a table of numbers through html, you have to declare the receiving tabel as varchar2, and either do the conversion yourself or trust to automatic conversion for each element. (ditto parenthetical comment above).

>
> thanks in advance..
> kevin

HTH
--
Chris Sarnowski csarnows @ curagen d.o.t com CuraGen Corporation
(203)401-3330, ext 227 Received on Wed Jul 15 1998 - 07:57:17 CDT