Dynamically creating and executing sql commands in oracle

Question

I am taking a database class and at the beginning of the lab section of the class we usually have to drop all the tables in the database created previously. I wanted to be able to run a script that does this dynamically, but cannot seem to get it to work. Here is the code I have so far.

declare tname string(50);
cursor ctable is select table_name from user_tables;

begin
 open ctable;
 LOOP
   FETCH ctable into tname;
   if tname != ''  then
     execute immediate 'drop table ' || tname;
   END if;
   EXIT WHEN ctable%NOTFOUND;
 END LOOP;
 close ctable;
end;

If someone could point me in the right direction as to what I am doing wrong that would great. Thanks.

Peter Lang · Accepted Answer · 2010-02-17 20:38:41Z

5

Oracle's VARCHAR2 treats empty strings as NULL.
So

if tname != '' then

is the same as

if tname != NULL then

which will return NULL instead of TRUE since it is not defined.

You can check for NULL by tname IS NOT NULL.

table_name is mandatory in user_tables though, so there is no need for this check.

Two more things:

Check for %NOTFOUND immediately after fetching
Use column-references for variable declarations if possible ( user_tables.table_name%TYPE)

So your code could look like that:

DECLARE
  tname user_tables.table_name%TYPE;
  CURSOR ctable IS SELECT table_name FROM user_tables;
BEGIN
  OPEN ctable;
  LOOP
    FETCH ctable INTO tname;
    EXIT WHEN ctable%NOTFOUND;
    EXECUTE IMMEDIATE 'drop table ' || tname;
  END LOOP;
  CLOSE ctable;
END;

You could also use an implicit cursor for better readability:

BEGIN
  FOR cur IN ( SELECT table_name FROM user_tables ) LOOP
    EXECUTE IMMEDIATE 'drop table ' || cur.table_name;
  END LOOP;
END;

edited Feb 17, 2010 at 20:38

answered Feb 17, 2010 at 20:23

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kenneth J Over a year ago

You make look so easy. I debated whether or not to post this question because I knew it would be something simple. There are just no good resources online for pl-sql. Thank you.

Peter Lang Over a year ago

You're welcome. Looks like StackOverflow can be a good resource :)

APC Over a year ago

If you are using foreign key constraints you will need to append ` cascade constraints` to the generated command, otherwise the DROP command will fail for referenced tables.

Collectives™ on Stack Overflow

Dynamically creating and executing sql commands in oracle

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related