OraFAQ Forum: SQL & PL/SQL » Columns which begin with

Home » SQL & PL/SQL » SQL & PL/SQL » Columns which begin with _

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Columns which begin with _ [message #15392]

Wed, 06 October 2004 04:34

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

Report message to a moderator

Re: Columns which begin with _ [message #15394 is a reply to message #15392]

Wed, 06 October 2004 04:53

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.

Report message to a moderator

Re: Columns which begin with _ [message #15411 is a reply to message #15394]

Wed, 06 October 2004 21:36

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 28 17:31:32 CDT 2025