Re: How to pass multiple selections from Web page to PL/SQL agent?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 25 May 1998 13:02:10 GMT
Message-ID: <356a6b48.39687207@192.86.155.100>

A copy of this was sent to Zeus Ng <zeusng_at_ibm.net> (if that email address didn't require changing) On Mon, 25 May 1998 15:13:52 +1000, you wrote:

>Hi all,
>
>I want to pass a multiple selections item back to the PL/SQL agent from
>the Web page. As the return string will be like this
>'.../display_city?p1=Chicago&p1=New+York&p1=London', how can PL/SQL
>procedure capture all values.
>
>The procedure will be like this.
>
>PROCEDURE DISPLAY_CITY (
>p1 varchar2(50) ) IS
>v_qry varchar2(4096);
>v_i number;
>
>BEGIN
> v_qry := 'select city_name from city
> where city_name in :city_name';
> v_i := owa_util.bind_variables(v_qry, ':city_name', p1);
> ....
>END;
>
>All I can archive in this example is Chicago, not New York or London.
>Anyone can point me to the right direction to do this?
>

Use a pl/sql table, 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;
/

You can use the builtin tables owa.vc_arr (varchar2(2000) in size) or owa_util.ident_arr (varchar2(30) in size) but you can't default them as I did above unless you create a package with an 'empty' one.

I prefer to use my own 'types' packge for this as I can control what the array looks like sizewise..

>TIA,
>
>Zeus Ng
>
 

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 Mon May 25 1998 - 08:02:10 CDT