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:

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

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

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

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

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

    ReplyDelete