iAS version and PL/SQL dilemma

From: Dan Looby <dan.looby@xxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Tue, 9 Mar 2004 09:47:41 -0500

A vendor has provided a PL/SQL package that builds dynamic web pages that allows individuals to update addresses/telephones via the web. We have iAS 1.0.2.2.2 on Sun boxes with Solaris 2.9 and an Oracle 9.2.0.3.0 database. But too often the package returns a 'ORA-06502: PL/SQL numeric or value error: host bind array too small' error.

Here is a package/procedure that emulates the problem:

create or replace procedure arrayTest as
begin
htp.p('<HTML><HEAD></HEAD><BODY>');

htp.p('<FORM ACTION="seqnoTest.p_update" METHOD="POST">');
htp.p('<INPUT TYPE=HIDDEN NAME="arr" VALUE="9">');
htp.p('<INPUT TYPE=HIDDEN NAME="arr" VALUE="10">');
htp.p('<INPUT TYPE=HIDDEN NAME="arr" VALUE="1">');
htp.p('<INPUT TYPE="SUBMIT" VALUE="SUBMIT">');
htp.p('</FORM>');

htp.p('</BODY></HTML>');
end;
/

create or replace package seqnoTest as

  /* Declare a generic varchar2 table type */
  type varchar2_tabtype is table of varchar2(1000)
    index by binary_integer;

 procedure p_update(arr IN OUT varchar2_tabtype);
 procedure p_updateSeq(arr IN OUT varchar2_tabtype);

end seqnoTest;
/

create or replace package body seqnoTest as

 procedure p_update(arr IN OUT varchar2_tabtype) is
 begin
  p_updateSeq(arr);
 end p_update;

 procedure p_updateSeq(arr IN OUT varchar2_tabtype) is
 begin
  htp.p('Entering P_UpdateSeq');
  htp.br;

  for i in 1 .. arr.COUNT LOOP
   htp.p('BEFORE arr('||i||') = '||arr(i));
   htp.br;
--   arr(i) := f_getNewSeqno;
   arr(i) := 123;
   htp.p('AFTER arr('||i||') = '||arr(i));
   htp.br;

end LOOP;

 exception when others then
  htp.bold('Exception in P_UpdateSeq');
 end p_updateSeq;
end seqnoTest;

/
show errors

The web page has a simple 'SUBMIT' button. When the user clicks on it the result is:

Tue, 9 Mar 2004 14:29:23 GMT

ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 7

  DAD name: bdevl
  PROCEDURE  : seqnoTest.p_update
  URL        : http://xxmach.acms.gatech.edu:7777/pls/bdevl/seqnoTest.p_update
  PARAMETERS :
  ============
  arr:
    9

ENVIRONMENT: ============ PLSQL_GATEWAY=WebDb GATEWAY_IVERSION=2 SERVER_SOFTWARE=Apache/1.3.12 (Unix) ApacheJServ/1.1 mod_perl/1.22 GATEWAY_INTERFACE=CGI/1.1 SERVER_PORT=7777 SERVER_NAME=xxmach.acms.gatech.edu REQUEST_METHOD=POST QUERY_STRING= PATH_INFO=/pls/bdevl/seqnoTest.p_update SCRIPT_NAME=/pls REMOTE_HOST= REMOTE_ADDR=XXX.XXX.XXX.XXX SERVER_PROTOCOL=HTTP/1.1 REQUEST_PROTOCOL=HTTP REMOTE_USER= HTTP_CONTENT_LENGTH=18 HTTP_CONTENT_TYPE=application/x-www-form-urlencoded HTTP_USER_AGENT=Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040206 Fi refox/0.8 HTTP_HOST=zzmach.acms.gatech.edu:7777

HTTP_ACCEPT=text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0
.8,video/x-mng,image/png,image/jpeg,image/gif;q=0.2,*/*;q=0.1
    HTTP_ACCEPT_ENCODING=gzip,deflate
    HTTP_ACCEPT_LANGUAGE=en-us,en;q=0.5
    HTTP_ACCEPT_CHARSET=ISO-8859-1,utf-8;q=0.7,*;q=0.7
    HTTP_COOKIE=SESSID=RFJOUDBFMTI1NDA=; TESTID=set
    Authorization=
    HTTP_IF_MODIFIED_SINCE=

Change the line that reads:

htp.p('<INPUT TYPE=HIDDEN NAME="arr" VALUE="9">');

to read:

htp.p('<INPUT TYPE=HIDDEN NAME="arr" VALUE="576">');

replace the package and click on SUBMIT and you get:

Entering P_UpdateSeq
BEFORE arr(1) = 576
AFTER arr(1) = 123
BEFORE arr(2) = 10
AFTER arr(2) = 123
BEFORE arr(3) = 1
AFTER arr(3) = 123

Oracle actually created a defect (3192585) for this problem. The vendor says they can't do anything until Oracle fixes the defect. Problem is: Oracle says the defect is resolved...in iAS 9.0.2.0.1...which vendor says we can't go to since they still serve version 6 forms up via the web.

Looking for suggestions/workarounds.

Thanks!

Dan


--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Daniel P. Looby                     email: dan.looby@xxxxxxxxxxxxxx
Lead Systems Analyst
Enterprise Information Systems/OIT  A meeting is an event at
Georgia Institute Of Technology       which minutes are kept
845 Marietta Street                   and hours are lost!
Atlanta, GA 30332-0305
Office Phone: 404-894-9587
         Fax: 404-894-8945
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

iAS version and PL/SQL dilemma

Other related posts: