Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

iAS version and PL/SQL dilemma

Dan Looby

2004-03-09

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@(protected)
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@(protected)
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
-----------------------------------------------------------------