Home » SQL & PL/SQL » SQL & PL/SQL » Columns which begin with _
Columns which begin with _ [message #15392] Wed, 06 October 2004 04:34 Go to next message
Gilles
Messages: 4
Registered: August 2000
Junior Member
Hi, I must create an oracle column which begins with the _ character. Unfortuntly SQL*PLUS won't let me.

I have checked the Oracle documentation and it says the the _ character is used for the LIKE operator, the documentation also says that _ is a valid character for a field name.

Can a field start with _ ?
If so how can I make it so ?

P.S. : I know I could simply start the field with another name, but I have to convert an existing database of another type and keep the exact field names.

TIA
Re: Columns which begin with _ [message #15394 is a reply to message #15392] Wed, 06 October 2004 04:53 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
From the Oracle 9i SQL Reference, "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.

----------------------------------------------------------------------
Hence, you can do it, but, you will be forever resigned to
  • surrounding these column names with double quotes;
  • case-sensitivity of these column names,
as the following example shows:
SQL> CREATE TABLE t (_starts_with_underscore VARCHAR2(1));
CREATE TABLE t (_starts_with_underscore VARCHAR2(1))
                *
ERROR at line 1:
ORA-00911: invalid character
 
 
SQL> CREATE TABLE t ("_STARTS_WITH_UNDERSCORE" VARCHAR2(1));
 
Table created.
 
SQL> INSERT INTO t VALUES ('X');
 
1 row created.
 
SQL> SELECT t._starts_with_underscore -- will fail since no double quotes
  2  FROM   t
  3  /
SELECT t._starts_with_underscore
         *
ERROR at line 1:
ORA-00911: invalid character
 
 
SQL> SELECT t._STARTS_WITH_UNDERSCORE -- will fail since no double quotes
  2  FROM   t
  3  /
SELECT t._STARTS_WITH_UNDERSCORE
         *
ERROR at line 1:
ORA-00911: invalid character
 
 
SQL> SELECT t."_starts_with_underscore" -- will fail since case is wrong
  2  FROM   t
  3  /
SELECT t."_starts_with_underscore"
         *
ERROR at line 1:
ORA-00904: invalid column name
 
 
SQL> SELECT t."_STARTS_WITH_UNDERSCORE" -- the only correct way to query this column
  2  FROM   t
  3  /
 
_
-
X
 
SQL>
If you decide to go this route, Gilles, my strong recommendation to you would be to adopt a strict naming convention (e.g., column names starting with underscores must always be created in all uppercase) and adhere to it unflinchingly.
Re: Columns which begin with _ [message #15411 is a reply to message #15394] Wed, 06 October 2004 21:36 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I agree with Art, but I want to add another option:
Create a table using 'normal' column-names and create a view using the quoted column-names.
This way, you will be able to address the table in a normal way.

Just my 2 cts
Previous Topic: How to run an Oracle report to generate a PDF file from within a PL/SQL packag
Next Topic: Help on substitution variables
Goto Forum:
  


Current Time: Mon Jul 28 16:47:02 CDT 2025