title: PL/SQL DDL Execute Immediate
body: <pre><code>ACCEPT p_username PROMPT 'Enter Username : '
  ACCEPT p_password PROMPT 'Enter New Password for Username : '
  VARIABLE g_output VARCHAR2(4000)
  DECLARE
     CURSOR NAME IS SELECT TABLE_NAME FROM DBA_TABLES 
        WHERE OWNER LIKE '%&amp;p_username%';
     DDL_DROP VARCHAR2(200);
  BEGIN
     FOR TNAME IN NAME
     LOOP
         BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE' || ' ' || TNAME.TABLE_NAME;
           :g_output := :g_output || ' ' || TNAME.TABLE_NAME;
         END;
     END LOOP;
  END;
  /
  PRINT g_output
  </code></pre>
  
  <p>Hello,  I'm new to PL/SQL and trying to make a script to drop the user's table and ultimately change their password later after dropping their tables.  I am having difficulty with the EXECUTE IMMEDIATE command.  The script works if I remove the EXECUTE IMMEDIATE line.  I tested it by printing the table names inside the loop and I get the right # of tables and their corresponding names.</p>
  
  <p>Any help is appreciated, thanks.</p>
  
  <p>Edited the code to reflect the suggestion but still didn't work.  Getting the same error.</p>
  
  <pre><code>ACCEPT p_username PROMPT 'Enter Username : '
  ACCEPT p_password PROMPT 'Enter New Password for Username : '
  VARIABLE g_output VARCHAR2(4000)
  DECLARE
     NAME SYS_REFCURSOR;
     DDL_WORD VARCHAR2(200);
  BEGIN
     OPEN NAME FOR SELECT TABLE_NAME FROM DBA_TABLES 
        WHERE OWNER LIKE '%&amp;p_username%';
     LOOP
           FETCH NAME INTO DDL_WORD;
           EXIT WHEN NAME%NOTFOUND;
           EXECUTE IMMEDIATE 'DROP TABLE "' || DDL_WORD || '" CASCADE CONSTRAINTS';
           :g_output := :g_output || ' ' || DDL_WORD;
     END LOOP;
     CLOSE NAME;
  END;
  /
  PRINT g_output
  </code></pre>