Oracle- creating dynamic function for deleting tables based on cursor

Question

I'm trying to build a dynamic function in Oracle using a cursor for all the tables that need to be dropped and re-created again. For example, I have the following example table structure:

CREATE TABLE All_tmp_DATA AS
(SELECT 'T_tmp_test1' As Table_NM, 'TEST1' As Process_name FROM DUAL UNION ALL
 SELECT 'T_tmp_test2' As Table_NM, 'TEST1' As Process_name FROM DUAL UNION ALL
 SELECT 'T_tmp_test3' As Table_NM, 'TEST1' As Process_name FROM DUAL)

The above tables starting with "T_tmp" represent all the tables in the database which needs to be dropped if their counts are >1 when starting the TEST1 process. I really need a function to pass in the parameter Process_name where I can input "TEST1", and build a loop using a cursor by binding it to the Table_NM from All_tmp_DATA and inserting it into table_name in the following code:

BEGIN  
  SELECT count(*)
    INTO l_cnt
    FROM user_tables
    WHERE table_name = 'MY_TABLE';
  IF l_cnt = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE my_table';
  END IF;
END;

[Drop all tables whose names begin with a certain string](https://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string) — Abra, Apr 07 '20 at 01:33
I've never heard of Oracle 13 before. Are you sure that version number is correct? Also, `SELECT CREATE TABLE...` is not valid Oracle syntax. Please clarify the database system you're actually using. — Bob Jarvis - Слава Україні, Apr 07 '20 at 02:43
Hi Bob, sorry about the typos. If you get rid of the SELECT in the first line, it should create the temp table. And It's ORACLE TOAD version 13. Thank you, Yen — Yen Ching, Apr 07 '20 at 02:51
TOAD is a SQL client and its version is essentially irrelevant to your question. What's more relevant is the version of Oracle you are using (check e.g. `select * from v$version`) — , Apr 07 '20 at 07:17

score 0 · Answer 1 · answered Apr 07 '20 at 05:26

In the beginning, I'd suggest you not to use mixed case when naming Oracle objects.

Test case:

SQL> select * From all_tmp_data;

TABLE_NM    PROCE
----------- -----
T_tmp_test1 TEST1
T_tmp_test2 TEST1
T_tmp_test3 TEST1

SQL> create table "T_tmp_test1" as select * From dept;

Table created.

SQL> -- I don't have "T_tmp_test2"
SQL> create table "T_tmp_test3" as select * From emp;

Table created.

SQL>
SQL> select table_name From user_Tables where upper(table_name) like 'T_TMP%';

TABLE_NAME
------------------------------
T_tmp_test3
T_tmp_test1

Procedure which drops tables contained in ALL_TMP_DATA:

as opposed to your code, I concatenated table name with DROP
as you use table names with mixed case, you have to enclose their names into double quotes, always (did I say not do use that?)

As the final select shows, those tables don't exist any more.

SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select table_nm from all_tmp_data) loop
  5      select count(*) into l_cnt
  6      from user_tables
  7      where table_name = cur_r.table_nm;
  8
  9      if l_cnt > 0 then
 10         execute immediate ('drop table "' || cur_r.table_nm || '"');
 11      end if;
 12    end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select table_name From user_Tables where upper(table_name) like 'T_TMP%';

no rows selected

SQL>

As of the process column: I have no idea what is it used for so I did exactly that - didn't use it.

score 0 · Answer 2 · answered Apr 07 '20 at 06:39

You can use the exception handling to handle such scenario directly as follows:

DECLARE
    TABLE_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT ( TABLE_DOES_NOT_EXIST, -00942 );
BEGIN
    FOR CUR_R IN (
        SELECT TABLE_NM
          FROM ALL_TMP_DATA
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'drop table "' || cur_r.table_nm || '"';
            DBMS_OUTPUT.PUT_LINE('"' || cur_r.table_nm || '" table dropped.');
        EXCEPTION
            WHEN TABLE_DOES_NOT_EXIST THEN
                DBMS_OUTPUT.PUT_LINE('"' || cur_r.table_nm || '" table does not exists');
        END;
    END LOOP;
END;
/

Oracle- creating dynamic function for deleting tables based on cursor

2 Answers2