Oracle PL/SQL Tutorial/Table/Drop Table

Drop only if table exists.

SQL> BEGIN
  2
  3    FOR i IN (SELECT table_name FROM user_tables WHERE table_name = "SHARED_ALL") LOOP
  4      EXECUTE IMMEDIATE "DROP TABLE shared_all";
  5    END LOOP;
  6
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>

Dropping a Table

SQL>
SQL> -- create demo table
SQL> create table myTable(
  2    id           NUMBER(2),
  3    value        NUMBER(6,2)
  4  )
  5  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into myTable(ID,  value)values (1,9)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (2,2.11)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (3,3.44)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (4,-4.21)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (5,10)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (6,3)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (7,-5.88)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (8,123.45)
  2  /
1 row created.
SQL> insert into myTable(ID,  value)values (9,98.23)
  2  /
1 row created.
SQL>
SQL> select * from myTable
  2  /
        ID      VALUE
---------- ----------
         1          9
         2       2.11
         3       3.44
         4      -4.21
         5         10
         6          3
         7      -5.88
         8     123.45
         9      98.23
9 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table myTable
  2  /
Table dropped.
SQL>

DROP TABLE with CASCADE CONSTRAINTS

SQL>
SQL> CREATE TABLE ONSITE_SCHEDULE (
  2    SALESMAN_ID   NUMBER,
  3    VISIT_DATE    DATE,
  4    COMPANY_NAME  VARCHAR2(50),
  5    COMPANY_URL   VARCHAR2(200) ) ;
Table created.
SQL>
SQL> DROP TABLE ONSITE_SCHEDULE CASCADE CONSTRAINTS ;
Table dropped.
SQL>

ORA-04091: table JAVA2S.DETAIL_TABLE is mutating, trigger/function may not see it

SQL>
SQL> CREATE TABLE master_table
  2  (master_id NUMBER NOT NULL PRIMARY KEY);
Table created.
SQL>
SQL> CREATE TABLE detail_table
  2  (detail_id NUMBER NOT NULL,
  3   master_id NUMBER NOT NULL,
  4     CONSTRAINT detail_to_emp
  5     FOREIGN KEY (master_id)
  6     REFERENCES master_table (master_id)
  7     ON DELETE CASCADE);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER after_delete_master
  2  AFTER DELETE ON master_table
  3  FOR EACH ROW
  4  DECLARE
  5    CURSOR curs_count_detail IS
  6    SELECT COUNT(*)
  7      FROM detail_table;
  8    v_detail_count NUMBER;
  9  BEGIN
 10    OPEN curs_count_detail;
 11    FETCH curs_count_detail INTO v_detail_count;
 12    CLOSE curs_count_detail;
 13  END;
 14  /
Trigger created.
SQL>
SQL> BEGIN
  2    FOR master_counter IN 1..10 LOOP
  3      INSERT INTO master_table
  4      VALUES(master_counter);
  5      FOR detail_counter IN 1..2 LOOP
  6        INSERT INTO detail_table
  7        VALUES(detail_counter,
  8               master_counter);
  9      END LOOP;
 10    END LOOP;
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM master_table;
 MASTER_ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.
SQL>
SQL> SELECT * FROM detail_table;
 DETAIL_ID  MASTER_ID
---------- ----------
         1          1
         2          1
         1          2
         2          2
         1          3
         2          3
         1          4
         2          4
         1          5
         2          5
         1          6
 DETAIL_ID  MASTER_ID
---------- ----------
         2          6
         1          7
         2          7
         1          8
         2          8
         1          9
         2          9
         1         10
         2         10
20 rows selected.
SQL>
SQL> DELETE master_table;
DELETE master_table
       *
ERROR at line 1:
ORA-04091: table sqle.DETAIL_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "sqle.AFTER_DELETE_MASTER", line 3
ORA-06512: at "sqle.AFTER_DELETE_MASTER", line 7
ORA-04088: error during execution of trigger "sqle.AFTER_DELETE_MASTER"

SQL>
SQL> SELECT * FROM master_table;
 MASTER_ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.
SQL>
SQL> SELECT * FROM detail_table;
 DETAIL_ID  MASTER_ID
---------- ----------
         1          1
         2          1
         1          2
         2          2
         1          3
         2          3
         1          4
         2          4
         1          5
         2          5
         1          6
 DETAIL_ID  MASTER_ID
---------- ----------
         2          6
         1          7
         2          7
         1          8
         2          8
         1          9
         2          9
         1         10
         2         10
20 rows selected.
SQL>
SQL> DROP TABLE detail_table;
Table dropped.
SQL> DROP TABLE master_table;
Table dropped.
SQL>
SQL>
SQL>