RE: 9.2.0.8 issues?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 25 Sep 2006 18:26:18 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9A99@ALVMBXW05.prod.quest.corp>

From the Fine Manual:

---

Oracle9i SQL Reference, Release 2 (9.2), Part Number A96540-02 Schema Object Names and Qualifiers

...  

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.  

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle Corporation strongly discourages you from using $ and #.

---

 

Easy way:
Try to use the name in a SQL statement, and see if you get an "invalid statement" or "invalid name" type of error. In my example below the first statement says "statement not terminated correctly" and the second says "sequence does not exist". So the first name is an invalid identifier and the second name is a valid identifier.

SQL> drop sequence LENG-20060925 ;
drop sequence LENG-20060925

                  *

ERREUR à la ligne 1 :
ORA-00933: La commande SQL ne se termine pas correctement

SQL> drop sequence LENG#20060925 ;
drop sequence LENG#20060925

              *
ERREUR à la ligne 1 :
ORA-02289: la séquence n'existe pas         

---

De la part de Leng Kaing

Actually, problem #3 has been identified as bug 5548043 - 'STATID is intended to be an "identifier" type parameter but the value passed in here is not a valid identifier

and so reports ORA-6502 in 9.2 and ORA-20001 "xxx is an invalid identifier" in 10.2 '

So how do you go about finding out about valid vs invalid identifiers?

---

From: Leng Kaing

Just wondering if anyone has applied this patch yet and whether there are any gotchas to watch out for... We upgraded from 9.2.0.6 to 9.2.0.8 and so far have found 3 issues:  

1. libraries in lib32 and lib still don't have read and execute permissions to the world. Problem existed in 9.2.0.7 as well!
2. SQR failed with ORA-1790 with this simple sql statement:

SELECT :G_InvoiceID

                         FROM DUAL

                         UNION

                         SELECT ss.siteinvoiceid

                         FROM vw_inv_sitesummary ss

                         WHERE ss.invoiceid = :G_InvoiceID

 

The program compiled fine against the 9.2.0.6 database. We had to do a TO_NUMBER against the select from DUAL  

3) dbms_stats.export_schema_stats fails with "ORA-06502: PL/SQL: numeric or value error" if statid had the hyphen or period included, eg. LENG-20060925 would fail, but LENG#20060925 would work  

I've logged an SR about dbms_stats but priority is at a 4, and have not heard from anyone...

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 25 2006 - 20:26:18 CDT