Geek asks and answers

In Oracle SQL, how do I avoid hyphens in a column name or retrieve a column by its position?

I have an imported table in Oracle that has hyphenated columns in it. Obviously, this is a bad idea, but there is a lot of code that depends on these exact names, and we cannot reorganize it at this time. I had the following problem in SQL Developer:

Suppose our circuit is called FOO and we define a substitution variable on it

def MYSCHEMA = FOO;

We create a table with WTF column names and populate it

CREATE TABLE "HYPHENTEST" ( 
  "COL--A" VARCHAR2(100 BYTE), 
  "COL -- B" VARCHAR2(100 BYTE),
  "COL C" VARCHAR(100 BYTE),
  "COL-D" VARCHAR(100 BYTE),
  "COL - E" VARCHAR(100 BYTE)
  );

insert into HYPHENTEST values ('t','u','v','w','x');
insert into HYPHENTEST values ('v','w','x','y','z');

These requests fail with ORA-00903: invalid table name

select "COL--A" from &MYSCHEMA..HYPHENTEST;
select "COL -- B" from &MYSCHEMA..HYPHENTEST;

But all these requests are successful

select "COL C" from &MYSCHEMA..HYPHENTEST;
select "COL-D" from &MYSCHEMA..HYPHENTEST;
select "COL - E" from &MYSCHEMA..HYPHENTEST;
select * from &MYSCHEMA..HYPHENTEST;

, - , , , ( , ). , , .

select "COL--A" from FOO.HYPHENTEST;
select "COL -- B" from FOO.HYPHENTEST;
select "COL C" from FOO.HYPHENTEST;
select "COL-D" from FOO.HYPHENTEST;
select "COL - E" from FOO.HYPHENTEST;
select * from FOO.HYPHENTEST;

, - (, , ) ? , , (ugh, )?

, ( , ).

+3
1

, SQL Developer 3.2.20:

Error starting at line 15 in command:
select "COL--A" from &MYSCHEMA..HYPHENTEST
Error at Command Line:15 Column:22
Error report:
SQL Error: ORA-00903: invalid table name
00903. 00000 -  "invalid table name"
*Cause:    
*Action:

Error starting at line 16 in command:
select "COL -- B" from &MYSCHEMA..HYPHENTEST
Error at Command Line:16 Column:24
Error report:
SQL Error: ORA-00903: invalid table name
00903. 00000 -  "invalid table name"
*Cause:    
*Action:

&MYSCHEMA, FOO, old/new set verify on, , :

old:select "COL C" from &MYSCHEMA..HYPHENTEST
new:select "COL C" from FOO.HYPHENTEST
COL C    
----------
v          
x

, , SQL Developer. , , , , ( , ). @OracleUser, , SQL * Plus. , SQL Developer, script SQL-, , , .

SQL Developer 4.0.0.13 :

old:select "COL--A" from &MYSCHEMA..HYPHENTEST 
new:select "COL--A" from FOO.HYPHENTEST 
COL--A   
----------
t          
v          

old:select "COL -- B" from &MYSCHEMA..HYPHENTEST 
new:select "COL -- B" from FOO.HYPHENTEST 
COL -- B 
----------
u          
w          

old:select "COL C" from &MYSCHEMA..HYPHENTEST
new:select "COL C" from FOO.HYPHENTEST
COL C    
----------
v          
x

, , , , , .

, , :

select "COL--A"
from &MYSCHEMA..HYPHENTEST;
select "COL -- B"
from &MYSCHEMA..HYPHENTEST;

old:select "COL--A"
from &MYSCHEMA..HYPHENTEST
new:select "COL--A"
from FOO.HYPHENTEST
COL--A   
----------
t          
v          

old:select "COL -- B"
from &MYSCHEMA..HYPHENTEST
new:select "COL -- B"
from FOO.HYPHENTEST
COL -- B 
----------
u          
w

, - - , , , , . , , ; , , - , , , .

@a_horse_with_no_name, current_schema alter session , , ; , . ( , current_schema).

+1

More articles:


All Articles