RE: issues?

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: <Leng.Kaing@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Sep 2006 18:26:18 -0700

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 to 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 as well!


2)      SQR failed with ORA-1790 with this simple sql statement:


                         FROM DUAL


                         SELECT ss.siteinvoiceid

                         FROM vw_inv_sitesummary ss

                         WHERE ss.invoiceid = :G_InvoiceID


The program compiled fine against the 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...

Other related posts: