Oracle using loop variable in procedure called in loop

Question

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?

Answer 1

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;

• When calling a procedure in PL/SQL, you just type in the procedure name. You don't use call.
• You'd want to pass i.tbl_name not just the pseudo-record i to your procedure.
• Naming a package func is likely to be confusing-- people are likely to expect that is a function not a package.