oraclefor-loopstored-proceduresuser-defined-functionsoracle19c

Oracle using loop variable in procedure called in loop

I'm using Oracle 19c, and want to 1) create a procedure that does DROP IF EXISTS (that part works when I drop a table name in), and 2) run that procedure in a FOR LOOP using the loop variable as the argument for the procedure.

CREATE OR REPLACE PACKAGE func IS
    PROCEDURE drop_if_exists( tbl_name IN VARCHAR2 );
END func;

CREATE OR REPLACE PACKAGE BODY func AS
    v_object_name VARCHAR2(100);
    PROCEDURE drop_if_exists( tbl_name IN VARCHAR2 )
        IS
    BEGIN
        v_object_name := dbms_assert.sql_object_name( tbl_name );
        EXECUTE IMMEDIATE ('drop table ' || tbl_name || ' purge');
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
END func;

BEGIN
    FOR i IN (SELECT tbl_name FROM names_of_tables)
        LOOP
            BEGIN
                SELECT func.drop_if_exists( i ) FROM dual;
            END;
        END LOOP;
END;

However, I get "ORA-00904: : invalid identifier"

Substituting a procedure call results in "Unknown database function 'call'"

BEGIN
    FOR i IN (SELECT tbl_name FROM names_of_tables)
        LOOP
            BEGIN
                --SELECT func.drop_if_exists( i ) FROM dual;
                call (func.drop_if_exists(i));
            END;
        END LOOP;
END;

Have I gone astray? Is this not the appropriate way to approach this task?

Solution

  • You probably want something like this

    BEGIN
    FOR i IN (SELECT tbl_name FROM names_of_tables)
        LOOP
            BEGIN
                func.drop_if_exists( i.tbl_name );
            END;
        END LOOP;
END;