I have the following PL/SQL:
declare
i_cnt number;
begin
select count(1) into i_cnt
from dba_tables
where table_name = upper('foo')
and owner = upper('bar');
if i_cnt > 0 then
drop table foo; -- <--- error this line
end if;
end;
From which I get this error.
ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
( begin case declare 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
How can I drop a table in a procedure?
The DROP TABLE statement deletes the specified table, and any data associated with it, from the database. The IF EXISTS clause allows the statement to succeed even if the specified tables does not exist. If the table does not exist and you do not include the IF EXISTS clause, the statement will return an error.
Another way to check whether a table already exists is to query the information_schema. tables view: IF EXISTS ( SELECT * FROM information_schema. tables WHERE table_schema = 'dbo' AND table_name = 't1') DROP TABLE dbo.
IF EXISTS is used to prevent an error from occurring if the database does not exist. If the default database is dropped, the default database is unset (the DATABASE() function returns NULL ). If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted.
If we try to drop a table that does not exist, we get the following error message. We do not want any error in executing queries, especially during the execution of a bunch of code. Before SQL Server 2016, developers use the IF EXISTS statement and check for the object existence before dropping it.
You can't directly execute DDL statements from a PL/SQL block - you'll have to use EXECUTE IMMEDIATE instead:
declare
i_cnt number;
begin
select count(1) into i_cnt
from dba_tables where table_name=upper('foo') and owner=upper('bar');
if i_cnt > 0 then
execute immediate 'drop table foo';
end if;
end;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With