Oracle Table Name – Handling Table Names with Special Characters

oracle

I have a table in database(oracle sql) which somehow contains one special character at the end which I dont know.I am trying to get that special character from its entry in table tab.

SQL> SELECT tname from tab where tname='OPERATION';

no rows selected

SQL> SELECT tname from tab where tname like 'OPERATION%';

TNAME
--------------------------------------------------------------------------------
OPERATION

SQL> SELECT length('OPERATION') from tab where tname like 'OPERATION%';

LENGTH('OPERATION')
------------------------
                       9

SQL> SELECT length(tname) from tab where tname like 'OPERATION%';

LENGTH(TNAME)
-------------
           10

So , there is one special character after char N in string OPERATION .
I tried getting the last character using substr but that did not help.

How can I find this special charater ( or even ascii value of this character ) ?

And how can I run select on the data in this table ?

Best Answer

You can try the dump function to get the actual byte values stored. For example, I just created a table "OOUPS ", with a space at the end.

> select table_name, dump(table_name) from user_tables;
...
OOUPS   Typ=1 Len=6: 79,79,85,80,83,32
...

You can see 32 (0x20 hex) as the last char - that's an ASCII space.

Once you've found what the extra character is, you can query the table using double quotes around the name. This is case-sensitive, so make sure you match that too. e.g.:

create table "OOUPS " (a int);
insert into "OOUPS " (a) values (42);
commit;
select * from "OOUPS ";

And of course:

alter table "OOUPS " rename to more_sane_name;

If you can't type the strange character easily, simplest way would be to create an SQL script, or pipe the exact character sequence to SQL*Plus:

OOUPS   Typ=1 Len=6: 79,79,85,80,83,13

$ echo -e 'alter table "OOUPS\r" rename to saner_name;\nexit;' | sqlplus user/pass
...
Table altered.
Related Question