How can I make an entire PL/SQL code block dynamic with bind variables?

Question

Background

I'm trying to make a re-usable PL/SQL procedure to move data from one database to another.

For this purpose, I'm using dynamic SQL.

The procedure executes perfectly if I use a REPLACE with placeholders. However, for security reasons, I want to use bind variables.

---

Question

How can I make an entire PL/SQL code block dynamic (with bind variables)? If I use a REPLACE instead of the bind variables, it works fine.

---

How to replicate

To replicate this in your database, create the following procedure as it is:

create or replace procedure move_data(i_schema_name in varchar2, i_table_name in varchar2, i_destination in varchar2) as
l_sql varchar2(32767);
l_cursor_limit pls_integer := 500;
l_values_list varchar2(32767);

begin

select listagg('l_to_be_moved(i).' || column_name, ', ') within group (order by column_id)
into l_values_list
from all_tab_cols
where owner = i_schema_name and
      table_name = i_table_name and
      virtual_column = 'NO';

l_sql := q'[
declare
l_cur_limit pls_integer := :l_cursor_limit;

cursor c_get_to_be_moved is
select :i_table_name.*, :i_table_name.rowid
from :i_table_name;

type tab_to_be_moved is table of c_get_to_be_moved%rowtype;

l_to_be_moved tab_to_be_moved;

begin  

open c_get_to_be_moved;
loop
    fetch c_get_to_be_moved
    bulk collect into l_to_be_moved limit l_cur_limit;
    exit when l_to_be_moved.count = 0;      

    for i in 1.. l_to_be_moved.count loop
        begin
            insert into :i_table_name@:i_destination values (:l_values_list);
        exception
        when others then
            dbms_output.put_line(sqlerrm);
            l_to_be_moved.delete(i);
        end;    
    end loop;
    forall i in 1.. l_to_be_moved.count
    delete
    from :i_table_name
    where rowid = l_to_be_moved(i).rowid;    

    for i in 1..l_to_be_moved.count loop
        if (sql%bulk_rowcount(i) = 0) then
            raise_application_error(-20001, 'Could not find ROWID to delete. Rolling back...');           
        end if;
    end loop;    
    commit;
end loop;          
close c_get_to_be_moved;

exception
when others then
    rollback;
    dbms_output.put_line(sqlerrm);
end;]';
execute immediate l_sql using l_cursor_limit, i_table_name, i_destination, l_values_list;
exception
when others then
    rollback;
    dbms_output.put_line(sqlerrm);
end;
/

And then you can execute the procedure with the following:

begin
    move_data('MySchemaName', 'MyTableName', 'MyDatabaseLinkName');
end;
/

Answer 1

Due to many reasons(inability to generate an appropriate execution plan, security checking, etc.) Oracle does not allow identifiers binding (table names, schema names, column names and so on). So if it's really necessary, the only way is to hard code those identifiers after some sort of validation (to prevent SQL injection).