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
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
