Jim Horn
asked on
Oracle: How to drop a table if it exists
Hi guys
In SQL Server if we want to drop a table only if it exists then here's the code:
Thanks in advance.
Jim
In SQL Server if we want to drop a table only if it exists then here's the code:
IF EXISTS(SELECT name FROM sys.tables WHERE name='TABLE_NAME')
DROP TABLE TABLE_NAME
-- The rest of the code goes here
What's the equivalent Oracle code? Thanks in advance.
Jim
declare
v_exist number;
begin
select count(*) into v_exist
from user_tables
where table_name = 'TABLE_NAME'
if cnt = 1 then
execute immediate 'drop table TABLE_NAME';
end if;
end;
I used the user_tables view because you may have select access to another schema table but not a drop table privilege.
v_exist number;
begin
select count(*) into v_exist
from user_tables
where table_name = 'TABLE_NAME'
if cnt = 1 then
execute immediate 'drop table TABLE_NAME';
end if;
end;
I used the user_tables view because you may have select access to another schema table but not a drop table privilege.
do the drop and then capture the exception, and you might want to check out the PURGE option
only keep one of the execute immediate lines
only keep one of the execute immediate lines
BEGIN
EXECUTE IMMEDIATE 'drop table table_name';
--EXECUTE IMMEDIATE 'drop table table_name purge'; --- purge prevents saving it in the recyclebin
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
I wouldn't try to verify that the table exists with a select. That is a totally unnecessary step. Just drop the table and catch the error. The drop checks for existence for you.
DECLARE
not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(not_exist, -942);
BEGIN
...
BEGIN
EXECUTE IMMEDIATE 'drop table table_name';
EXCEPTION
WHEN not_exist THEN
NULL;
END;
....
END;
I don't like to use WHEN OTHERS because that could ignore errors that you really shouldn't be ignoring. Ignore the message that you want to ignore.
the when others above doesn't ignore any errors -it reports all of them.
but, I totally agree with the sentiment.
I was simply illustrating the basic structure of an exception handler to address the main question,
but, I totally agree with the sentiment.
I was simply illustrating the basic structure of an exception handler to address the main question,
ASKER
When I execute the below code in DbVisualizer it generates an error, and there's a red squiggly below the SELECT in the third line.
@delimiter ++;
declare v_exist number(1);
SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
if v_exist = 1 then
execute immediate 'drop table FOO';
end if;
CREATE TABLE FOO (bar number(1));
SELECT bar FROM FOO;
14:43:01 [@DELIMITER - 0 row(s), 0.000 secs] Command processed
14:43:01 [DECLARE - 0 row(s), 0.071 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "SELECT" to continue.
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
... 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.071/0.000 sec [1 successful, 0 warnings, 1 errors]
You need a begin statement. You'll also need an end statement plus you need to use an execute immediate statement for ddl commands (i.e. create table foo).
Need a begin between declaring your variables and actually executing code:
FYI:
PL/SQL is pretty much Ada.
FYI:
PL/SQL is pretty much Ada.
declare
v_exist number(1);
begin
SELECT...
ASKER
Okay. Revised code, new error message
@delimiter ++;
declare v_exist number(1);
begin
SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
if v_exist = 1 then
execute immediate 'drop table FOO';
end if;
end;
execute immediate 'CREATE TABLE FOO (bar number(1))';
SELECT bar FROM FOO;
14:49:38 [@DELIMITER - 0 row(s), 0.000 secs] Command processed
14:49:38 [DECLARE - 0 row(s), 0.071 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 13, column 9:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
;
... 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.071/0.000 sec [1 successful, 0 warnings, 1 errors]
The last execute immediate is outside the code block.
It is a PL/SQL command and needs to be inside a block of code.
an alternative:
exec execute immediate 'CREATE TABLE FOO (bar number(1))';
Which is a shortcut to:
begin execute immediate 'CREATE TABLE FOO (bar number(1))'; end;
It is a PL/SQL command and needs to be inside a block of code.
@delimiter ++;
declare v_exist number(1);
begin
SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
if v_exist = 1 then
execute immediate 'drop table FOO';
end if;
execute immediate 'CREATE TABLE FOO (bar number(1))';
end;
SELECT bar FROM FOO;
an alternative:
exec execute immediate 'CREATE TABLE FOO (bar number(1))';
Which is a shortcut to:
begin execute immediate 'CREATE TABLE FOO (bar number(1))'; end;
To get all to work -
@delimiter ++;
declare v_exist number(1);
BEGIN
SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
if v_exist = 1 then
execute immediate 'drop table FOO';
end if;
execute immediate 'CREATE TABLE FOO (bar number(1))';
SELECT bar into something FROM FOO;
END;
++
@delimiter;++
@delimiter ++;
declare v_exist number(1);
BEGIN
SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
if v_exist = 1 then
execute immediate 'drop table FOO';
end if;
execute immediate 'CREATE TABLE FOO (bar number(1))';
SELECT bar into something FROM FOO;
END;
++
@delimiter;++
ASKER
@slightv - Didn't work, red squiggly under 'SELECT' on last line, error message
[Error Code: 6550, SQL State: 65000] ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "SELECT"
awking00 - Also didn't work, not sure what you meant by SELECT bar INTO something, when I removed the 'SELECT bar into something' line it gives a red squiggly under the declare on line 2 and returns and error message
[Error Code: 1031, SQL State: 42000] ORA-01031: insufficient privileges
ORA-06512: at line 5
[Error Code: 6550, SQL State: 65000] ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "SELECT"
awking00 - Also didn't work, not sure what you meant by SELECT bar INTO something, when I removed the 'SELECT bar into something' line it gives a red squiggly under the declare on line 2 and returns and error message
[Error Code: 1031, SQL State: 42000] ORA-01031: insufficient privileges
ORA-06512: at line 5
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looks like it is a tool specific thing. I'm out.
ASKER
@slightwv - Your code works in SQL developer, so I guess we're good.
@awking00 - Your code works as well in SQL Developer without the @delimeter code, so good there too.
This is way simpler in SQL Server.
@awking00 - Your code works as well in SQL Developer without the @delimeter code, so good there too.
This is way simpler in SQL Server.
In PL/SQL when issuing a select statement, it must be selected into a variable If there had been a variable "something" declared as a number the code in ID: 41458866 would have worked.
Just drop it and ignore the message that it didn't exist in the first place.
For logic, you need to be in PL/SQL and you cannot to DDL natively in there so you need to use the execute immediate like you have in other questions.
Just execute the drop and trap the exception?
Like in your previous question: You could select from user_tables/all_tables/dba
Now the bigger question:
Why drop the table in the first place? When questions come into Oracle from other database products and I see "drop table" in "code", I think temp tables. You really shouldn't do that in Oracle. There are other ways...