Re: PL/SQL: table, varray etc. as a procedure parameter?? (newbie)

From: Bill Coulam <bcoulam_at_DELETECAPSusa.net>
Date: 2000/02/17
Message-ID: <g56r4.1941$x3.3212@wormhole.dimensional.com>

Are you not using the OAS v.3 or v.4 + the PL/SQL Web Toolkit? If not how are you generating HTML out of PL/SQL. Assuming you are using the above, you have two options. Oracle implicitly converts the fields from an HTML form. I find it almost easier to make everything on the receiving end (in your case, "second"), of datatype VARCHAR2 DEFAULT NULL. However, for your checkboxes, the datatype, as you've already figured out must a PL/SQL table. I think it will work whether you make the scalar base of the PL/SQL table a varchar2 or number, but I always use varchar2.

To answer your question, the place to put your PL/SQL table type definition is in a package spec that your procedure can use. If you're not using packages yet, start. Read Feuerstein's PL/SQL books. He's a package bigot, for good reason. In our case we have a generic package that holds many of the common datatypes our PL/SQL-based web applications use. Many of these are PL/SQL table of varchar2(<length>) to handle many scenarios. We also have corresponding PL/SQL tables that are used in place of DEFAULT NULL. Without any compilation or testing (I'm at home), here is what I mean:

create or replace package mytypes as
type vc5table is table of varchar2(5) index by binary_integer; type empty_vc5table is table of varchar2(5) index by binary_integer; end mytypes;
/

create or replace package mytest as
procedure proc1;
procedure proc2
(
is_myname in varchar2 default null, ias_mychoices in vc5table default empty_vc5table );
end mytest;
/

create or replace package body mytest as procedure proc1 is
begin
  <usual htp calls to open body and form. form has caction (or is that curl?) => 'mytest.proc2')>
  htp.formHidden('is_myname','John Doe');   htp.formCheckbox(<I don't have the syntax with me, but you would have many of these in your multiple checkbox scenario, each with the same cname => 'ias_mychoices', but each possibly with a different value to distinguish the choices checked.>);
  htp.formSubmit(<cname => NULL (I find I hardly ever need to name the Submit button or handle it in the receiving proc) cvalue => 'Save Choices'>);
  htp.formClose; htp.bodyClose; htp.htmlClose; end proc1;

procedure proc2
(
  is_myname in varchar2 default null,   ias_mychoices in vc5table default empty_vc5table ) is
begin
  <again open html and body>
  htp.p('User '||is_myname||' has made the following choices:'); htp.br;   for i in 1..ias_mychoices.COUNT loop

do something useful with the user's choices here, like inserting into a persistent table insert into bogustable values (bogustab_sequence.NEXTVAL, is_myname, ias_mychoices(i));
give them some feeback htp.p('Choice '||to_char(i)||': ['||ias_mychoices(i)||']'); end loop; end proc2; end mytest;
/

Long-winded, I know. By the way, the OAS package also has a built in PL/SQL table you can use called varray or something like that, I've never used it. I think it is a table of varchar2(2000) (must manually change to 4000 if you have OAS 4.0.8.1). Good luck! Sorry if there are errors in this hastily-constructed script.
- bill c.

"Do not reply" <Do_not_reply_at_hotmail.com> wrote in message news:88b7c4$uec$1_at_nntp.hut.fi...

> Hi,
>
> after going through a couple of thick manuals (Oracle 8 SQL Reference,
> PL/SQL User's Guide and Reference) I couldn't find a solution.
>
> My procedure "first" creates a dynamical html file with a form. In the

form

> there's n checkbox fields that should be submitted to another procedure
> "second" (names changed). How do I define "second" when I don't know how
> many parameters it is going to receive?
>
> I tried to name the checkbox fields as f(1), f(2) and so on, and define
> "second" to have "m MTABLE"  as a parameter. But where should I introduce
> MTABLE, which is "table of number index by binary_integer"? As a

parameter,

> "m is table of..." doesn't work.
>
> Any ideas? I would prefer not to use javascript or anything like that...
> could "first", when creating the form, create "second" as well, since the
> number of parameters would be correct. Or is there global variables in
> PL/SQL I could use?
>
> Thanks in advance.
>
> Jay
>
>

Received on Thu Feb 17 2000 - 00:00:00 CST