how to provide multiple bind variable for execute immediate inside pl/sql block

Question

I have a table which contains the metadata of the table. The task is to periodically delete a specific set of tables, provided the information for where condition and how many days the data is retained are present.If a user needs to delete a data on daily basis, he simply enter his table name in audit log. The procedure will do the rest. The example is shown below.

Table structure:

CREATE TABLE delete_tbl_list (
    id             NUMBER NOT NULL,
    table_name     VARCHAR2(100) NOT NULL,
    column_name    VARCHAR2(100) NOT NULL,
    day_retented   NUMBER NOT NULL,
    where_clause   VARCHAR2(2000)
);

the day_retended is the number which will tell on how many days the data can hold.

select * from  delete_tbl_list

 ID    TABLE_NAME  COLUMN_NAME  DAY_RETENTED   WHERE_CLAUSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------- -----------------------------------------------
 1    audit_log   log_TS       60 

So if i need to delete a table taking log_ts(timestamp) as column with 60days period as retention. The table in query needs to do

delete * from audit_log where log_ts<systimestamp -60 

Now i need to do it using bulk delete and more dynamic and hence i wrote the procedure below,

create procedure dynamic_mass_delete as 

 TYPE tbl_rec_rowid IS    TABLE OF ROWID INDEX BY PLS_INTEGER;
 lv_del_exec_rec    tbl_rec_rowid;
 v_limit                 PLS_INTEGER := 10000;
 m_date date:=sysdate;
 total_records_deleted number:=0;
 l_where delete_tbl_list.where_clause%type;
   l_sql varchar2(2000);
   TYPE ref_cur_type IS REF CURSOR;
delete_content ref_cur_type;
  BEGIN
    for i in (select table_name,COLUMN_NAME,DAY_RETENTED,WHERE_CLAUSE from delete_tbl_list) loop
    DBMS_OUTPUT.PUT_LINE('tablename..'||i.table_name);          
        l_where:='';
        m_date:=m_date-i.day_retented;

        if i.where_clause is not null then
            l_where:=' and '||i.where_clause;
        end if;


        OPEN delete_content FOR 'SELECT  rowid  from ' || i.table_name  ||' where '|| i.COLUMN_NAME || ' <= to_timestamp('''||m_date||''')'||l_where; 
        LOOP
            total_records_deleted := 0;

            FETCH delete_content BULK COLLECT INTO lv_del_exec_rec LIMIT v_limit;

            FORALL j IN lv_del_exec_rec.first..lv_del_exec_rec.last

                execute immediate 'DELETE FROM  :1   where rowid=:2 'using i.table_name,lv_del_exec_rec(j);
                total_records_deleted := total_records_deleted + SQL%rowcount;
                DBMS_OUTPUT.PUT_LINE('Delete count..'||total_records_deleted);          
            EXIT WHEN delete_content%notfound;
        END LOOP;
        CLOSE delete_content;       
    end loop;
    EXCEPTION
    when others then
    DBMS_OUTPUT.PUT_LINE('Error-->'||SQLERRM);
  END;
/

Now i getting error in the delete query stating invalid table name, i was not able to write dbms_output inside for all statment. Is it possible to use multiple bind variable inside a pl/sql procedure.

The error which i get is ,

Error-->ORA-00942: table or view does not exist


PL/SQL procedure successfully completed.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

The table very much exists, but it is throwing error, i was not able to print inside the forall block too.

Answer 1

Switch to

execute immediate 'DELETE FROM ' || i.table_name ||' where rowid = ' || lv_del_exec_rec(j);

Answer 2

You can simplify your code as follows:

BEGIN
    FOR TBL_DETAILS IN (
        SELECT
            TABLE_NAME,
            COLUMN_NAME,
            DAY_RETENTED,
            WHERE_CLAUSE
        FROM
            DELETE_TBL_LIST
    ) LOOP

        DBMS_OUTPUT.PUT_LINE('tablename..' || TBL_DETAILS.TABLE_NAME);

        EXECUTE IMMEDIATE 'DELETE FROM '
                          || TBL_DETAILS.TABLE_NAME
                          || ' WHERE '
                          || TBL_DETAILS.COLUMN_NAME
                          || ' < SYSTIMESTAMP - '
                          || TBL_DETAILS.DAY_RETENTED
                          || CASE
            WHEN TBL_DETAILS.WHERE_CLAUSE IS NOT NULL THEN ' AND ' || TBL_DETAILS.WHERE_CLAUSE
        END;

        DBMS_OUTPUT.PUT_LINE('Delete count..' || SQL%ROWCOUNT);

    END LOOP;
END;
/

Hope, This will help you in creating simpler code.

Cheers!!