Re: Passing array values into pl/sql procedure via a URL

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 26 Apr 2006 14:22:05 -0400
Message-ID: <W7ydnZLkvcLQJ9LZnZ2dneKdnZydnZ2d@comcast.com>

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:e2oddl$gqk$1_at_news4.zwoll1.ov.home.nl...
: Jeremy wrote:
: > I thought I had this all sorted out.....
: >
: > Created a type:
: >
: > create type vc2000_array as table of varchar2(2000)
: > /
: >
: > Then I defined a procedure thus:
: >
: > procedure campaign_form
: > (p_web_site_id in number,
: > p_camp_code in varchar2 default null,
: > p_style_id in number default null,
: > p_title in varchar2 default null,
: > p_name in vc2000_array default vc2000_array(),
: > p_value in vc2000_array default vc2000_array());
: >
: >
: > This is to be executed via web browser through mod_plsql
: >
: > If I call it like this:
: >
: > <hostname etc.>/campaign_form?p_web_site_id=304&p_camp_code=XX
: >
: > It executes.
: >
: > But if it is called like this
: >
: > <hostname etc.>/campaign_form?p_web_site_id=304
: > &p_camp_code=XX&p_name=nam1
: >
: > It fails with an error in the log file
: >
: > [Wed Apr 26 18:38:55 2006] [error] mod_plsql: /plql/campaign_form HTTP-
: > 404 ORA-06550: line 10, column 2:
: > PLS-00306: wrong number or types of arguments in call to 'CAMPAIGN_FORM'
: > ORA-06550: line 10, column 2:
: > PL/SQL: Statement ignored
: >
: > In I had thought I would be able to pass in multiple values of p_name in
: > the URL like:
: > &p_name=name1&p_name=name2 etc
: > but find I cannot even pass just one in :(
: >
: > Any ideas anyone?
: >
: quote:

:
: To invoke mod_plsql in a Web browser, input the URL in the following
format:
:

protocol://hostname[:port]/DAD_location/[[!][schema.][package.]proc_name[?query_string]]

:

: ! character

:

: Indicates to use the flexible parameter passing scheme. See
: Section 1.6.2, "Flexible Parameter Passing" for more
: information.
: /quote

:

: Quoted from the mod_plsql Users Guide (B12303-01)
: Basically, you can pass a name array, as well as a value array.
: Think this is what you want
: --
: Regards,
: Frank van Bortel

:
: Top-posting is one way to shut me up...

agree with Frank -- one slight technicality/clarification: it is the mod_plssql module that actually packages up all URL parameters into the two arrays; the URL syntx is unchanged except for the '!' in from of the proc name. as the reference will show, it's the spec of the proc that is the must be set up to receive the two arrays.

real nice way to go, especially if you use associative arrays in your code to manage the values passed

++ mcs Received on Wed Apr 26 2006 - 13:22:05 CDT