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);Horrible! And what's even more horrible is that people actually do this.
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
My Fruit Table TABLE
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 onHandy.
-- 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
6 comments:
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
amazing information . i was in the for that info
reckless driving virginia
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
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
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
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
Post a Comment