iAS version and PL/SQL dilemma

From: Dan Looby <dan.looby_at_oit.gatech.edu>
Date: Tue, 9 Mar 2004 09:47:41 -0500
Message-Id: <p0602041fbc7386a0bf63@[130.207.163.55]>

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_at_oit.gatech.edu
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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Mar 09 2004 - 08:44:21 CST