Use Flexible Parameter Passing to Avoid The Describe Overhead
Jon, June 06, 2004 - 6:29 pm UTC
Tom,
<quote>
I would avoid the flexible argument passing for the reasons ...
</quote>
I am surpise that you would avoid the flexible arugment for a PLSQL web application.
Oracle document states that using flexible argument can help to avoid the describe overhead.
</code>
http://download-west.oracle.com/docs/cd/A97329_03/core.902/a95102/optplsql.htm#1005048 <code>
Isn't using the flexible argument help to avoid the describe overhead?
Thanks.
Peace,
Jon
June 07, 2004 - 8:02 am UTC
read that paragraph. this describe "overhead" happens in the RARE case whereby you have an array to be sent but you only send one element.
There are better ways to avoid this IF an when it becomes an issue -- they are described right there as well.
The describe overhead can easily happen with "flexible" (as in generic and hard to manage in a coding environment as there are no formal named parameters) argument passing as well. Just have a page with one element on it -- it'll happen there as well.
I would not recommend the use of this feature to avoid this "overhead". Not by a long shot.
many applications require it
bob, June 06, 2004 - 8:23 pm UTC
If you ever have a form that edits database records for a variable number of records, and you use radio buttons for one of the form fields, flexible parameter passing is a requirement. Each set of radio buttons must have a unique parameter name (eliminating the opportunity to use owa_util.vc_arr, or your own equivalent type, so if the number of radio button sets is variable you have no other choice, unless you set an upper max value on the number of entries on the form, and code a formal parameter for every one up to the max number with a null default.
PL/SQL's strength is it's integration with SQL, its ability to do web stuff wasn't its original intent. HTMLDB sounds like it fixes all the things that were previously difficult to do in pl/sql web apps. I hope businesses can see the value in this pl/sql web framework.
I also hope it appeals to old and new generations of PL/SQL folks as a possible alternative to the popular Java frameworks for scalable apps.
June 07, 2004 - 8:10 am UTC
that is why you'll never see radio buttons (which I sort of don't like actually, i find them hard to use and take up way too much room on a page)..
I use simple drop down lists -- more compact, doesn't make me do things in a bad way.
Describe Overhead
A reader, June 07, 2004 - 2:55 pm UTC
Tom,
Quote document:
<quote>
In such cases, the first attempt to execute the PL/SQL procedure fails. mod_plsql issues a Describe call to get the signature of the PL/SQL procedure and binds each parameter based on the information retrieved from the Describe operation. The procedure is re-executed and results are sent back.
This Describe call occurs transparently to the procedure, but internally mod_plsql has encountered two extra round trips, one for the failed execute call and the other for the describe call.
</quote>
Does the subsequent HTTP request with similar parameters incur the same "Describe" overhead?
Does the debug or trace in mod_plsql tell us about the "Describe" overhead?
Thanks.
Peace,
Jon
P.S. HTMLDB is a great framework. I am slowly learning it. At the same time, I am also try to learn the mod_plsql and PLSQL web development because I am a developer who like how things work under the hood. Besides Oracle, are there many Oracle clients using PLSQL mod_plsql?
June 07, 2004 - 3:39 pm UTC
sql_trace would tell you. you'd see extra stuff in there.
I used to have caching in there -- before the code became mod_plsql. I found i had to disable it, particularly in a development environment! the cached version would become inconsistent with reality.
Truth be told -- it would be *rare* to hit and this and if you fear you are -- the other solutions are infinitely better.
Lots of people use mod_plsql -- without even knowing, parts of Oracle apps are built on it. but yes, there are lots of people using it "on purpose" as well.
Size of modplsql Array Parameter
robert, December 15, 2004 - 6:05 pm UTC
8.1.7.4 (Windows)
9.0.4.0.0 Oracle-HTTP-Server (Linux)
======================================
Tom,
can you confirm that array param (pl/sql table) is limited to 2000 items.
And if so, how would you deal with this limitation ?
thanks
CREATE OR REPLACE PACKAGE rc_test IS
PROCEDURE one ;
PROCEDURE two(p_big_array IN owa_util.ident_arr) ;
END rc_test;
CREATE OR REPLACE PACKAGE BODY rc_test IS
PROCEDURE top
IS
l_str VARCHAR2(32000) ;
BEGIN
l_str := '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">' || chr(10) ||
'<html>' || chr(10) ||
'<head>' || chr(10) ||
' <title>rc_test</title>' || chr(10) ||
'</head><body>' ;
htp.p(l_str);
END ;
PROCEDURE tail
IS
l_str VARCHAR2(32000) ;
BEGIN
l_str := '</body></html>' ;
htp.p(l_str);
END ;
PROCEDURE one
IS
p_big_array owa_util.ident_arr;
BEGIN
SELECT object_name BULK COLLECT INTO p_big_array
FROM all_objects ;
top;
htp.p('<FORM name="frmExport" method="post" action="rc_test.two">');
FOR i IN p_big_array.FIRST..2001 --<<-- ERROR IF > 2000
LOOP
htp.p('<INPUT TYPE="hidden" NAME="p_big_array" VALUE="' ||
p_big_array(i) || '">');
END LOOP;
htp.p('<INPUT type="submit" value="Print Results">') ;
htp.p('</FORM>') ;
tail ;
END ;
PROCEDURE two(p_big_array IN owa_util.ident_arr)
IS
BEGIN
top;
FOR i IN p_big_array.FIRST..p_big_array.LAST
LOOP
htp.p(' Value: ' || p_big_array(i) || '">');
END LOOP;
tail ;
END ;
END rc_test;
December 15, 2004 - 7:07 pm UTC
it would appear so:
mod_plsql: /pls/ask/demo_pkg.take_lots_of_fields HTTP-400 Too many arguments passed in. Got 5000 parameters. Upper limit is 2000
no idea why, it seems rather restrictive to me.
ho ho ho, new mod_plsql global directive
robert, December 23, 2004 - 4:47 pm UTC
>>
mod_plsql: /pls/ask/demo_pkg.take_lots_of_fields HTTP-400 Too many arguments
passed in. Got 5000 parameters. Upper limit is 2000
no idea why, it seems rather restrictive to me.
<<
solution to this was found NOWHERE else except
one single item on Metalink -
</code>
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=280011.1 <code>
>>
There is a new global directive which modifies the amount of paramters one can use. The name of the Parameter is PlsqlMaxParameters. The default value is 2000.
3) Add this new directive to the plsql.conf file
<<
funny search on "PlsqlMaxParameters" on Google, OTN came up nothing.
December 23, 2004 - 7:41 pm UTC
thanks so much -- appreciate the followup .
Prameter Passing
Nikhilesh, April 07, 2005 - 1:49 am UTC
April 07, 2005 - 9:16 am UTC
sorry, insufficient data to answer. Don't even know what an "access error" is.
The Error
Nikhilesh, April 08, 2005 - 5:35 am UTC
Dear Tom,
I get following error.When i call the procedure
as /pls/MK01_SP/rep_fun.list('Raghu')
------------------------------------------------------------
Forbidden
You don't have permission to access /pls/MK01_SP/rep_fun.list('Raghu') on this server.
Oracle HTTP Server Powered by Apache/1.3.22 Server at TIT
Port 8150
------------------------------------------------------------
It works fine when i call it as /pls/MK01_SP/rep_fun.list?name=Raghu.
But the previous case worked fine at the time of development and testing, on live it gives error.
Our DBA says Apache version on testing database was old one i.e. comes with Oracle8i and live is newer Oracle9i version.
So is there any setting in apache cofig file that can make my code work without changing it.
Thanks
April 08, 2005 - 7:30 am UTC
well, it looks like rep_fun.list('Raghu') -- which is a javascript call -- is not being run as javascript by your browser.
/pls/MK01_SP/rep_fun.list('Raghu')
would in fact be what the BROWSER sent to us and would be invalid.
You'll need to figure out why your javascript is not being run as javascript, this does not have anything to do with plsql or apache as far as I can see, it is all about what the browser sent to us.
flexible parameters
A reader, April 28, 2008 - 6:42 pm UTC
pl/sql paraemters
A reader, March 19, 2009 - 12:22 pm UTC
March 19, 2009 - 12:25 pm UTC
smk - is "what" related?
plsql
A reader, March 19, 2009 - 5:27 pm UTC
TOm:
this error by mod_plsql.
Too many arguments passed in. Got 3607 parameters. Upper limit is 2000
DAD name: inex
PROCEDURE : save_items_list
URL :
http://xxx.xx.xxxx/inex/save_items_list PARAMETERS :
===========
parameters size/number
A reader, February 15, 2012 - 12:07 pm UTC
Hi Tom:
In Oracle DBMS 9i (and 11g) is there any limitation on the number of parameters or size of parameters passed to a PL/SQL procedure invoked by mod_plsql?
I am talking about the procedure itself which is different than the web server(mod_plsql) parameter setting for "plsqlmaxparameters".
February 15, 2012 - 1:41 pm UTC
there are no documented limits on the number of parameters a procedure may take. I've tested well into the hundreds - which should be sufficient for anything...