From oracle-l-bounce@freelists.org Tue Mar 9 08:44:21 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i29EiLr14726 for ; Tue, 9 Mar 2004 08:44:21 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i29EiIo14713 for ; Tue, 9 Mar 2004 08:44:18 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1A1A139492E; Tue, 9 Mar 2004 09:43:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Mar 2004 09:42:29 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from hellmouth2.gatech.edu (hellmouth2.gatech.edu [130.207.165.162]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0B5D7394DD5 for ; Tue, 9 Mar 2004 09:42:27 -0500 (EST) Received: from hellmouth2.gatech.edu (localhost [127.0.0.1]) by hellmouth2.gatech.edu (Postfix) with SMTP id F0B12A540 for ; Tue, 9 Mar 2004 09:47:14 -0500 (EST) (envelope-from dan.looby@oit.gatech.edu) Received: from mailprx4.gatech.edu (mailprx4.prism.gatech.edu [130.207.171.18]) (using TLSv1 with cipher EDH-RSA-DES-CBC3-SHA (168/168 bits)) (Client CN "smtp.mail.gatech.edu", Issuer "RSA Data Security? Inc." (verified OK)) by hellmouth2.gatech.edu (Postfix) with ESMTP id D1BDAA48D for ; Tue, 9 Mar 2004 09:47:14 -0500 (EST) (envelope-from dan.looby@oit.gatech.edu) Received: from [130.207.163.55] (eis355.oit.gatech.edu [130.207.163.55]) (using SSLv2 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) (sasl: method=PLAIN, username=iadanlo, sender=n/a) by mailprx4.gatech.edu (Postfix) with ESMTP id 6D4803A5EE for ; Tue, 9 Mar 2004 09:47:14 -0500 (EST) (envelope-from dan.looby@oit.gatech.edu) Mime-Version: 1.0 X-Sender: iadanlo@oit.gatech.edu Message-Id: Date: Tue, 9 Mar 2004 09:47:41 -0500 To: oracle-l@freelists.org From: Dan Looby Subject: iAS version and PL/SQL dilemma Content-Type: text/plain; charset="us-ascii" ; format="flowed" X-archive-position: 176 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dan.looby@oit.gatech.edu Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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(''); htp.p(''); htp.p(''); 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(''); to read: htp.p(''); 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@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@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 -----------------------------------------------------------------