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
|
|
|
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.
|
|
|
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
|
|
|