7 Replies Latest reply: Nov 10, 2008 4:04 AM by BluShadow RSS

    PL/SQL drop table, compiler error

    536364
      I am trying PL/SQL to delete all tables whose owener is 'TEST' and starting with 'F'.
      DECLARE
                     CURSOR TblCursor IS (SELECT table_name FROM all_tables WHERE owner='TEST' and table_name LIKE 'F%');    
      BEGIN               
                     FOR tbl IN TblCursor LOOP                    
                                    dbms_output.put_line (tbl.table_name);     
                                    drop table tbl.table_name;          
                     END LOOP;                    
                     commit;
      END;
      
      .
      run;
      and I got compile error.
      Error starting at line 1 in command:
      DECLARE
                     CURSOR TblCursor IS (SELECT table_name FROM all_tables WHERE owner='TEST' and table_name LIKE 'F%');    
      BEGIN               
                     FOR tbl IN TblCursor LOOP                    
                                 dbms_output.put_line (tbl.table_name);     
                                 drop table tbl.table_name;          
                     END LOOP;                    
                     commit;
      END;
      Error report:
      ORA-06550: line 6, column 11:
      PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
      
         begin case declare end exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe
      The symbol "lock was inserted before "DROP" to continue.
      ORA-06550: line 6, column 36:
      PLS-00103: Encountered the symbol ";" when expecting one of the following:
      
         . , @ in <an identifier>
         <a double-quoted delimited-identifier> partition subpartition
      06550. 00000 -  "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:
      Please help me on this.

      and further, if I just want to delete the table which is starting with "F" and digit number, like 'F102', 'F102_aaa'... how to do it ?

      Thanks.

      Edited by: user533361 on Nov 9, 2008 4:45 PM
        • 1. Re: PL/SQL drop table, compiler error
          SomeoneElse
          You can't do DDL (i.e. drop, create, alter...) directly in a PL/SQL block.

          You have to use dynamic sql, EXECUTE IMMEDIATE.
          • 2. Re: PL/SQL drop table, compiler error
            536364
            Thank you for reply. Then I modify it to this
            DECLARE
                           CURSOR TblCursor IS (SELECT table_name FROM all_tables WHERE owner='TEST' and table_name LIKE 'F%'); 
                                    sql_stmt VARCHAR2(255);
            BEGIN               
                           FOR tbl IN TblCursor LOOP        
                                    sql_stmt := 'drop table :1';                    
                              --dbms_output.put_line (sql_stmt);     
                             EXECUTE IMMEDIATE sql_stmt USING tbl.table_name;       
                           END LOOP;                    
                           commit;
            END;
             
            .
            run;
            I still get error : "ORA-00903: invalid table name". Could you help me on this ?

            Thanks.
            • 3. Re: PL/SQL drop table, compiler error
              SomeoneElse
              You can't use bind variables in DDL statements.
              • 4. Re: PL/SQL drop table, compiler error
                536364
                I got this from the link : http://www.orafaq.com/wiki/PL/SQL_FAQ. I am using Oracle XE...

                Thanks.

                ////////////////////////
                Can one use dynamic SQL statements from PL/SQL?
                Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that the statements within quotes are NOT semicolon terminated:

                -- Using bind variables...'
                sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
                EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

                -- Returning a cursor...
                sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
                EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
                • 5. Re: PL/SQL drop table, compiler error
                  pl/sql novice
                  All examples you're showing are using bind variables (:varname) to represent column values in a DML statement. If you use bind variables to represent column names or table names, you'll get ORA error. If you need to use Dynamic SQL with table/column names, use concat instead.
                  -- Note: this code is for illustration only, not tested
                  create or replace procedure test1 (
                    col_name varchar2,
                    tab_name varchar2)
                  as
                    stmt varchar2(4000) := '';
                  begin
                    stmt := 'select ' || col_name || ' from ' || tab_name || ' where rownum <= 10';
                    execute immediate stmt;
                  end;
                  /
                  • 6. Re: PL/SQL drop table, compiler error
                    628524
                    TEST on ORACLE> CREATE TABLE EMP1 AS SELECT * FROM EMP;

                    Table created.

                    CREATE TABLE EMP2 AS SELECT * FROM EMP
                    TEST on ORACLE> /

                    Table created.

                    CREATE TABLE EMP3 AS SELECT * FROM EMP
                    TEST on ORACLE> /

                    Table created.

                    CREATE TABLE EMP4 AS SELECT * FROM EMP
                    TEST on ORACLE> /

                    Table created.

                    TEST on ORACLE> SELECT TNAME FROM TAB
                    2 WHERE TNAME LIKE 'EMP_%'
                    3 /

                    TNAME
                    ------------------------------
                    EMP1
                    EMP2
                    EMP3
                    EMP4


                    DECLARE
                    CURSOR C1 IS SELECT TNAME FROM TAB WHERE TNAME LIKE 'EMP_%';
                    DDL_STMT VARCHAR2(200);
                    BEGIN
                    FOR I IN C1 LOOP
                    DDL_STMT:='DROP TABLE'||' '||I.TNAME;
                    EXECUTE IMMEDIATE DDL_STMT;
                    END LOOP;
                    END;


                    TEST on ORACLE> /

                    PL/SQL procedure successfully completed.

                    TEST on ORACLE> SELECT TNAME FROM TAB WHERE TNAME LIKE 'EMP_%'
                    2 /

                    no rows selected




                    Regards
                    MS
                    • 7. Re: PL/SQL drop table, compiler error
                      BluShadow
                      user533361 wrote:
                      I am trying PL/SQL to delete all tables whose owener is 'TEST' and starting with 'F'.
                      The real issue should be... Why are you wanting to dynamically drop objects within your code?

                      If you have a need for temporary tables then you should use Global Temporary Tables which exist permanently and allow for data to be session or transaction persistent.

                      If the data needs to exist across sessions then you should consider either using a regular table and truncating the data rather than creating/dropping tables or use a materialized view on the source of the data to update it as required.

                      Dynamic objects creation and dropping indicates poor database design.