Question: I need to write code to test if
an Oracle table exists ands then drop and re-create the table:
if
table1 exists
drop
table1
create table1...
How do I write code this in PL/SQL?
Answer:
First, I'm concerned because tables are supposed to
be permanent, and normally are not dropped and re-created
frequently!
I would recommend checking-out the “truncate” command. It
serves the same purpose as dropping/recreating a table, and it is
super-fast because all it does is set the table high water mark to
zero. See these notes on
using the truncate command.
If you want to write this code in PL/SQL you have to check
for the known error code from the existence check (zero). This
is PL/SQL pseudocode (not tested):
when others then . .
. - exit with any non-zero status code
declare cursor tab_exists
as select table_name from user_tables where table_name = ‘FRED’:
BEGIN
open cursor tab_exists
fetch tab_exists into :mytabname;
-- at this point you will
have aborted if the fetch was not successful
drop table mytabname;
create table mytabname
tablespace . . . ;
close cursor tab_exists
END
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
