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.

Answer 1

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:

1. Check for %NOTFOUND immediately after fetching
2. 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;