Oracle Cursor within a Package not working – ORA 06512

I am trying to build a package that will take in a table of table names and either drop from or delete those tables. I am using dynamic sql, and dropping or deleting the tables works, but I need both the procedures to loop through all of the table names passed back to it.

I’ve tried mulitple ways – including trying to create a FOR Loop and a cursor. Here is a similar function I wrote in PostgreSQL that works but I’m having trouble translating it to Oracle.

Here is my function in PostgreSQL that works:

CREATE OR REPLACE FUNCTION drop_tables_for_stnd_mod_build(tablenames text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
tab_name RECORD;
BEGIN  
   FOR tab_name IN EXECUTE 'SELECT table_name FROM ' || tablenames
   LOOP
        EXECUTE 'DROP TABLE ' || tab_name.table_name || ' CASCADE';
    END LOOP;
END;
$function$
;

And the procedure I’m writing as part of a package in Oracle

CREATE OR REPLACE PACKAGE BODY stnd_build_table_cleanup
AS 
PROCEDURE drop_tables(table_in CLOB)
IS
TYPE cur_type is REF CURSOR;
c cur_type;
query_string VARCHAR(300);
loop_string VARCHAR(300); 
table_name VARCHAR(100); 

BEGIN  
    loop_string := 'SELECT tablenames FROM :table';
    OPEN c FOR loop_string USING table_in;
    LOOP
      FETCH c INTO table_name; 
    query_string := 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS';
   -- dbms_output.PUT_LINE (query_string);
    EXECUTE IMMEDIATE query_string; 
     EXIT WHEN c%NOTFOUND;
    END LOOP ;
    CLOSE c;
END drop_tables;

Here is the error I get when I try to call my function: Error report – ORA-00903: invalid table name ORA-06512: at “AMS_NYS.STND_BUILD_TABLE_CLEANUP”, line 13 ORA-06512: at line 2 00903. 00000 – “invalid table name” *Cause:
*Action:

Thanks!

Answer

Here’s one possibility. Note that I coded this as a standalone procedure for simplicity.

CREATE OR REPLACE TYPE table_type IS TABLE OF VARCHAR2(128); 

CREATE OR REPLACE PROCEDURE drop_tables(tables_to_drop_in table_type)
IS
BEGIN  
    FOR i IN tables_to_drop_in.FIRST .. tables_to_drop_in.LAST LOOP
        --DBMS_OUTPUT.PUT_LINE(tables_to_drop_in(i));
        EXECUTE IMMEDIATE 'DROP TABLE ' || tables_to_drop_in(i) || ' CASCADE CONSTRAINTS';
    END LOOP;
END drop_tables;

DECLARE
    tables_to_drop table_type;
BEGIN
    tables_to_drop := table_type('TBL1','TBL2', 'TBL3');
    drop_tables(tables_to_drop);
END;