Is this a bug?

Links

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

Is this a bug?

Is this a bug?

2006-02-24 - By Bobak, Mark

Reply: 1 2 3

Consider this:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data
Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create table test_fbi(a varchar2(4000));

Table created.

SQL> create index test_fbi_length on test_fbi(upper(a)) online compute
statistics;
create index test_fbi_length on test_fbi(upper(a)) online compute
statistics
*
ERROR at line 1:
ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1
ORA-01450 (See ORA-01450.ora-code.com): maximum key length (3215) exceeded

When I ran into this, I had an index created in my preprod environment,
and Oracle was refusing to let me create it in my identical prod
environment....I was stumped, how could it error on this here, but exist
in preprod, which is identical in terms of version, O/S, block size,
etc?

Now, I'm aware that any function that returns varchar2 implicitly
returns varchar2(4000). Tom Kyte has a write up on this available here
http://asktom.oracle.com/~tkyte/article1/index.html that explains it,
and demonstrates how to work around it by wrapping the output in a
SUBSTR() and then hiding that inside of a view. That's all fine and
well and good. What I didn't understand is HOW I was ever allowed to
create the index in preprod.....and then I discovered this:

SQL> create index test_fbi_length on test_fbi(upper(a)) online compute
statistics;
create index test_fbi_length on test_fbi(upper(a)) online compute
statistics
*
ERROR at line 1:
ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1
ORA-01450 (See ORA-01450.ora-code.com): maximum key length (3215) exceeded

SQL> c/online/
1* create index test_fbi_length on test_fbi(upper(a)) compute
statistics
SQL> /

Index created.

Why does it work when you drop the ONLINE keyword?? In fact, it doesn't
even need to be an FBI to demonstrate this behavior.
Observe:

SQL> create index test_ind_key_length on test_fbi(a) online compute
statistics;
create index test_ind_key_length on test_fbi(a) online compute
statistics
*
ERROR at line 1:
ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1
ORA-01450 (See ORA-01450.ora-code.com): maximum key length (3215) exceeded

SQL> c/online/
1* create index test_ind_key_length on test_fbi(a) compute statistics
SQL> /

Index created.

Anyone else think this is a bit weird, not to mention inconsistent,
behavior?

Any thoughts/ideas?

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"Exception: Some dividends may be reported as qualified dividends but
are not qualified dividends. These include:

* Dividends you received on any share of stock that you held for less
than 61 days during the 121-day period that began 60 days before the
ex-dividend date. The ex-dividend date is the first date following the
declaration of a dividend on which the purchaser of a stock is not
entitled to receive the next dividend payment. When counting the number
of days you held the stock, include the day you disposed of the stock
but not the day you acquired it. See the examples below. Also, when
counting the number of days you held the stock, you cannot count certain
days during which your risk of loss was diminished. See Pub. 550 for
more details."
--IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>Is this a bug?</TITLE>
</HEAD>
<BODY>


Consider this:


Connected to:

 Oracle9i Enterprise Edition Release 9.2.0.6.0 -
64bit Production

 With the Partitioning, Real Application Clusters,
OLAP and Oracle Data Mining options

 JServer Release 9.2.0.6.0 - Production


SQL> create table test_fbi(a varchar2(4000));


Table created.


SQL> create index test_fbi_length on test_fbi
(upper(a)) online compute statistics;

 create index test_fbi_length on test_fbi(upper(a)
) online compute statistics

         
             
             
             *

 ERROR at line 1:

 ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level
1

 ORA-01450 (See ORA-01450.ora-code.com): maximum key length (3215) exceeded

 
 

When I ran into this, I had an index created in my
preprod environment, and Oracle was refusing to let me create it in my
identical prod environment….I was stumped, how could it error on this
here, but exist in preprod, which is identical in terms of version, O/S, block
size, etc?

Now, I'm aware that any function that returns
varchar2 implicitly returns varchar2(4000).  Tom Kyte has a write up on
this available here <A HREF="http://asktom.oracle.com/~tkyte/article1
/index.html">http://asktom.oracle
.com/~tkyte/article1/index.html</A> that
explains it, and demonstrates how to work around it by wrapping the output in a
SUBSTR() and then hiding that inside of a view.  That's all fine and well
and good.  What I didn't understand is HOW I was ever allowed to create
the index in preprod…..and then I discovered this:

SQL> create index test_fbi_length on test_fbi
(upper(a)) online compute statistics;

 create index test_fbi_length on test_fbi(upper(a)
) online compute statistics

         
             
             
             *

 ERROR at line 1:

 ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level
1

 ORA-01450 (See ORA-01450.ora-code.com): maximum key length (3215) exceeded

 

SQL> c/online/

   1* create index test_fbi_length on test
_fbi(upper(a))  compute statistics

 SQL> /


Index created.


Why does it work when you drop the ONLINE keyword?
?  In fact, it doesn't even need to be an FBI to demonstrate this behavior.


Observe:


SQL> create index test_ind_key_length on test
_fbi(a) online compute statistics;

 create index test_ind_key_length on test_fbi(a)
online compute statistics

         
             
             
  *

 ERROR at line 1:

 ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level
1

 ORA-01450 (See ORA-01450.ora-code.com): maximum key length (3215) exceeded

 

SQL> c/online/

   1* create index test_ind_key_length on
test_fbi(a)  compute statistics

 SQL> /


Index created.

 

Anyone else think this is a bit weird, not to
mention inconsistent, behavior?


Any thoughts/ideas?


-Mark


--

 Mark J. Bobak

 Senior Oracle Architect

 ProQuest Information &
Learning


"Exception:  Some dividends may be
reported as qualified dividends but are not qualified dividends.  These
include:


• Dividends you received on any share
of stock that you held for less than 61 days during the 121-day period that
began 60 days before the ex-dividend date.  The ex-dividend date is the
first date following the declaration of a dividend on which the purchaser of a
stock is not entitled to receive the next dividend payment. When counting the
number of days you held the stock, include the day you disposed of the stock
but not the day you acquired it. See the examples below. Also, when counting
the number of days you held the stock, you cannot count certain days during
which your risk of loss was diminished.  See Pub. 550 for more details.&
#8221;

  --IRS, Form 1040-A Instruction
Booklet, Line 9b:  Qualified Dividends


</BODY>
</HTML>