Dynamically creating and executing sql commands in oracle

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.

From stackoverflow

• 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;
  

  Kenneth J : 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 : You're welcome. Looks like StackOverflow can be a good resource :)

  APC : 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.

  Peter Lang : @APC: Good point, thanks.