Re: Help, How to pass array in PL/sql

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/04/14
Message-ID: <35367661.5954401_at_192.86.155.100>#1/1

A copy of this was sent to "Divakar" <kimmane_at_email.msn.com> (if that email address didn't require changing) On Sat, 11 Apr 1998 11:25:21 -0400, you wrote:

Simply use a table of VARCHAR. the OWA package has a type defined called OWA.VC_ARR which is a table of varchar2(2000) and OWA_UTIL has a type called OWA_UTIL.IDENT_ARR which is a table of varchar2(30) that you can use. I myself prefer to use my own type, which I control and can default. For example:

create or replace package types
as

    type Array is table of varchar2(2000) index by binary_integer;     empty_Array Array;
end;
/

create or replace
procedure demo_list( p_list in types.Array default types.empty_Array ) as
begin

    htp.uListOpen;

    for i in 1 .. 100000 loop
    begin

        htp.listItem( p_list(i) );
    exception

        when no_data_found then exit;
    end;
    end loop;

    htp.uListClose;

    htp.formOpen( 'demo_list' );
    htp.formSelectOpen( cname=>'p_list', nsize => 5, cattributes=>'Multiple');

    for x in ( select username from all_users where rownum < 100 ) loop

        htp.formSelectOption( x.username );     end loop;

    htp.formSelectClose;
    htp.formSubmit;
    htp.formClose;

end;
/

>Help .
>Dos anyone know how to pass array as a parameter in pl?sql.
>The requirement goes like this.
>I am using Oracle web server 3.0. Where I have html form which has
> multile select box.what that means is that. List box shows n numbers of
>options and you can choose more than one orgument. When you submit this form
>recieving procedure shoul have a parament which is array to have this.
>sending procedure
>htp.p('<form action="PK_CONTACT.SP_VALIDATE" method="POST">')
>
> htp.p('<tr>
> <td align="right" valign="top">Operations/&nbsp;<br>Coverage&nbsp;<br>
> Location(s):</td>
> <td align="left" colspan="3">
> <select name="LocationList" multiple size="5">');
> IF NOT location_csr%ISOPEN
> THEN
> OPEN location_csr;
> END IF;
> LOOP FETCH location_csr into location_rec;
> IF location_csr%FOUND THEN
> l_location_loop_count := l_location_loop_count + 1;
> IF l_location_loop_count >= 1 then
> l_location_cd := location_rec.location_cd;
> l_location_tx := location_rec.location_tx;
> htp.p('<option value='||'"'||l_location_cd||'">'||l_location_tx); END
>IF;
> END LOOP;
> htp.formSelectClose;
> htp.p('</tr>');
>REcining Procedure
> Procedure SP_VALIDATE
>
>
> LocationList IN varchar2 DEFAULT NULL, -- Multiple select
> Comments IN varchar2 DEFAULT NULL) -- comments
> IS
>
>Problem here is that even if you select more that one item in the list,
>on the recieving end how do you receive that data. In Because PL?SQL
>I guess you can pass only number, integer and Varchar2 as parameters.
>Plese help.
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Apr 14 1998 - 00:00:00 CEST