CODEWITHSUNDEEP
sqloracle-databaseescapingsqlplus
bokov
bokov

Reputation: 3534

In Oracle SQL how do I escape hyphens in a column name or extract a column by its position?

I have an imported table in Oracle that has columns with hyphens in their names. Obviously this is a bad idea, but there is a large amount of code that depends on those precise names, and we cannot refactor it at this time. I am having the following problem in SQL Developer:

Let's suppose our schema is named 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 queries fail with ORA-00903: invalid table name

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

But all these queries succeed

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

So there is something about having specifically two hyphens in a row that makes this break when a substituted variable is used to identify the schema (the double periods are intentional, the first one delimits the variable name). On the other hand, if I hardcode the schema, all the queries work.

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;

So, does anybody know a foolproof way to escape special characters (or at least double hyphens) in column names? Or at least a way to reference columns by position (ugh, I know)?

What isn't an option for our code is using static schema names (because the schemas on the real thing are going to vary, and not always be the schema that is currently logged in).

Upvotes: 4

Views: 5620

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

I can replicate what you see in 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:

The error shows &MYSCHEMA, not the substituted FOO value, and I don't get the old/new information from set verify on like I do on subsequent queries:

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

So it seems the double-hyphen is confusing SQL Developer. As that is a comment indicator, perhaps the substitution mechanism in that version is stopping when it see that, which it doesn't do elsewhere (and shouldn't do here, since it's inside a double-quoted string). @OracleUser posted but then removed an example of the same queries working SQL*Plus. It's nothing to do with the queries as such, it's SQL Developer's interpretation of them as it parses your script into SQL statements that seems to be broken.

It is fixed in 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

I can't spot anything obvious about this on their forums, so I'd guess it just happens to work after their rewrite rather than having been targeted as a specific problem to correct, but who knows.

Upgrading to the newer version seems like a sensible idea anyway, but if you can't or don't want to, just moving the substitution onto a different line works around it in the old version:

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

I don't think you will find any way to escape the double-hyphen - you can't change the comment string, which I imagine is what is confusing it. This isn't something you should need to escape since it shouldn't behave like this; and as far as I'm aware you can't escape anything within a quoted identifier, but you should never need to as the quotes should be protecting whatever they enclose.

I agree with @a_horse_with_no_name though, changing your current_schema with an alter session call means you don't have to prefix all the tables with the schema name at all, as long as they are all in a single schema of course; which makes your code easier to read and maintain anyway, in my experience. (Another alternative might be to use synonyms, but I much prefer setting current_schema).

Upvotes: 1

Related Questions