Wednesday, February 20

How to delete an object with a special character in Oracle

There are some things in Oracle that are possible but shouldn't be possible.
One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it.
I.e.:
SQL> create table "My Fruit Table" (id number);

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
My Fruit Table TABLE
Horrible! And what's even more horrible is that people actually do this.

Now to the problem, a user created a table with a special character in the name. Not even sure what character but I need a way to drop it. PL/SQL to the rescue.
Example:
SQL> set serveroutput on
-- Lets create a dummy table with a bogus character in the name
SQL> declare
a varchar2(500);
begin
a:='create table "abctest'||chr(150)||'" (id number)';
execute immediate a;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
abctest? TABLE

SQL> desc "abctest?";
ERROR:
ORA-04043: object "abctest?" does not exist

-- And a bit of PL/SQL to drop it
-- change the abctest% string to something matching your single table.
SQL> declare
a varchar2(500);
tname varchar2(50);
begin
a:='select table_name from user_tables where table_name like ''abctest%''';
execute immediate a into tname;
dbms_output.put_line('Table name is: '||tname);
execute immediate 'drop table "'||tname||'"';
end;
/

Table name is: abctest?

PL/SQL procedure successfully completed.

SQL> select * from tab;

no rows selected
Handy.

6 comments:

King Bayern Munich said...

We supply many styles of sofas,such as living-room-sofa, Modern leather sofa . soft sofa. Lounge-sofa .
e supply all kinds of stone product,such as china tombstones, Modern-stone-sculpture, paving-stone Glazed-Vitrified-Tiles

mannan said...

amazing information . i was in the for that info
reckless driving virginia

somnus11258 said...

I wanted to thank you for this great read!! I definitely enjoyed every little bit of it, I have you bookmarked to check out all the new stuff you post.I am a china tour lover,You can learn more: travel to Beijing | Guilin Tourist Attractions | What to See in Guilin

Unknown said...

One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it.
WOW Gold Kaufen
World of Warcraft Gold

Unknown said...

This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something
Signature:
Versión en facebook en español descargar a los países hablan Español: facebook entrar direto agora , facebook en español descargar , facebook entrar direto agora

Unknown said...

Simply want to say your article is impressive. The clarity in your post is simply impressive and i can assume you are an expert on this subject.
SEO Expert In Pakistan