Re: OWS and PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Jul 1998 14:38:00 GMT
Message-ID: <359a48a0.1281582_at_192.86.155.100>

A copy of this was sent to bubbaqbeef_at_hotmail.com (Jack Erwin) (if that email address didn't require changing) On Wed, 01 Jul 1998 07:15:38 GMT, you wrote:

>On Tue, 30 Jun 1998 21:08:17 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
>wrote:
>
>>A copy of this was sent to bubbaq_at_my-dejanews.com
>>(if that email address didn't require changing)
>>On Tue, 30 Jun 1998 17:57:28 GMT, you wrote:
>
>
>>the plsql cartridge takes a URL like:
>>
>>http://hostname/dcdname/owa/some_procedure?z=5&x=10&a=Hello+World
>
> This looks like "GET" data. How does it handle "POST" data??
>

GET and POST are handled by the pl/sql cartridge transparently -- your plsql routine can't tell the difference between get and post. my example could have been:

<form action=some_procedure method=post>
<input type=text name=z value=5>
<input type=text name=x value=10>
<input type=text name=a value="Hello World">
<input type=submit>
</form>

and it will work the same...

>>And maps that to a database call like:
>>
>>begin
>> some_procedure( a => :a, x => :x, z => :z );
>>end;
>
> My form has 80+ fields. (Don't worry, about 75 of them are
>generated by a C program :) Do I really have to have an 80+ long
>parameter list?? I saw an earlier reply of yours about passing in
>parameters as a PL/SQL table. Since I have so many fields, this was
>the best option.
>

If each of the input fields have different names, Yes, you must have 80 formal parameters. If each of the input fields can have the same name, then you can get away with 2 inputs using pl/sql tables.

For example, the above form could be re-written as:

<form action=some_procedure method=post>
<input type=hidden name=p_names value=z>
<input type=text   name=p_vals  value=5>

<input type=hidden name=p_names value=x> <input type=text name=p_vals value=10>

<input type=hidden name=p_names value=a> <input type=text name=p_vals value="Hello World">

<input type=submit>
</form>

And then, you could code in pl/sql (note: you only need the types package ONCE per database, not for each and every procedure)

create or replace package types
as

    type array is table of varchar2(2000) index by binary_integer;     empty array;
end;

create or replace procedure some_procedure( p_names in types.array, p_vals in types.array )
as
begin

    for i in 1 .. p_names.count loop

       htp.p( p_names(i) || ' = ' || p_vals(i) || htf.br );     end loop;
end;

You would probably build a convienence routine into the types package that would do easier lookups on the table (eg a routine like:

procedure getval( p_names in array, p_vals in array, p_theName in varchar2 ) return varchar2;

so you could code:

    myData := types.getval( p_names, p_vals, 'A' );

Yes, this method does have some repercussions. For example, it precludes the use of checkboxes since when a checkbox is NOT checked, it sends no value (hence the names and values arrays would get out of sync). It also makes it hard to use more then one radio group per form... I work around the checkbox issue by never using checkboxes -- choosing to use a single select drop down list with Yes and No in it instead. For radio groups, if you must have >1 in the form then you will have to use formal named parameters for them (for now)

>>OAS4.x will allow you to instead have the variables saved in a pair of arrays,
>>sort of like an associative array, and have a procedure some_procedure that
>>recieves these arrays instead of named formal parameters -- your procedure would
>>get an array of names and an array of values passed to it.
>
>Would this be simply 2 PL/SQL tables in the param list for my entry
>procedure??
>

Your procedure will get 3 inputs, the number of arguments, an array of the names of arguments, and the corresponding array of values to go with the names. You'll be able to use checkboxes and radio groups and all.

>Thanks again,
>Jack Erwin
>erwinja_at_mscd.edu
>
>P.S.
>Any plans for real associative arrays in PL/SQL?

No, not really, its pretty easy to implement using hash tables. Here is an example (not *fully* tested but it seems to work)

create or replace package hash_pkg
as

        pragma restrict_references( hash_pkg, wnds, rnds, wnps, rnps );

        procedure add( p_key in varchar2, p_val in varchar2 );

        procedure empty;

	function get( p_key in varchar2 ) return varchar2;
	pragma restrict_references( get, wnds, rnds, wnps );

	hash_table_full exception;

end;
/
show errors;

create or replace package body hash_pkg
as

type hash_record

is record ( key		varchar2(30),
            val		varchar2(255)  );

type	hash_table is table of hash_record index by binary_integer;

g_hash_size     constant number default  32768;
g_hash_base		constant number default 0;
g_hash_table	hash_table;
g_empty_hash	hash_table;

function get( p_key in varchar2 ) return varchar2 is

	l_hash_key	number default
	            dbms_utility.get_hash_value( p_key, g_hash_base, g_hash_size );
	l_idx		number default NULL;
begin
	for i in l_hash_key .. l_hash_key+1000 loop
		exit when l_idx is NOT NULL;
		if ( g_hash_table(i).key = p_key )
		then
			l_idx := i;
		end if;
	end loop;

	if ( l_idx is NULL ) then
		return NULL;
	end if;
	return g_hash_table(l_idx).val;
exception 
	when no_data_found then 
		return NULL;

end get;

procedure add( p_key in varchar2, p_val in varchar2 ) is

	l_hash_key	number default
	            dbms_utility.get_hash_value( p_key, g_hash_base, g_hash_size );
	l_idx		number default NULL;
begin
	for i in l_hash_key .. l_hash_key+1000 loop
		exit when l_idx is NOT NULL;
		begin
			if ( g_hash_table(i).key = p_key )
			then
				l_idx := i;
			end if;
		exception
			when no_data_found then
				l_idx := i;
		end;
	end loop;

	if ( l_idx is NULL ) then
		raise HASH_TABLE_FULL;
	end if;
	g_hash_table(l_idx).key := p_key;
	g_hash_table(l_idx).val := p_val;

end add;

procedure empty
is
begin

        g_hash_table := g_empty_hash;
end;

end hash_pkg;
/

exec hash_pkg.add( 'MyKey', 'MyValue' ); exec dbms_output.put_line( hash_pkg.get('MyKey') );

begin

	for x in ( select username, created from all_users ) loop
		hash_pkg.add( x.username, to_char(trunc(x.created)) );
	end loop;

end;
/

select username, created, hash_pkg.get(username) from all_users where to_char(trunc(created)) <> nvl( hash_pkg.get(username), 'x' ) /
REM the above will return 0 rows if it works...  

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 Wed Jul 01 1998 - 16:38:00 CEST