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;
/

Identifiers(table name, schema name and so on) cannot be bound. — Nick Krasnov, Dec 05 '16 at 13:04
@NicholasKrasnov Thanks! I guess I'll just validate the input variables. Could you please put the same as an answer so I can close this? — Zesty, Dec 05 '16 at 13:12

score 3 · Accepted Answer · answered Dec 05 '16 at 13:30

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).

score 1 · Answer 2 · answered Dec 05 '16 at 13:36

1

If I understand well, you could try a trick, by using a dynamic SQL inside a dynamic SQL.

setup:

create table tab100 as select level l from dual connect by level <= 100;
create table tab200 as select level l from dual connect by level <= 200;
create table tabDest as select * from tab100 where 1 = 2;

This will not work:

create or replace procedure testBind (pTableName in varchar2) is
    vSQL varchar2(32000);
begin
    vSQL := 'insert into tabDest select * from :tableName';
    execute immediate vSQL using pTableName;
end;

But this will do the trick:

create or replace procedure testBind2 (pTableName in varchar2) is
    vSQL varchar2(32000);
begin
    vSQL := q'[declare
                vTab  varchar2(30)    := :tableName;
                vSQL2 varchar2(32000) := 'insert into tabDest select * from ' || vTab;
               begin
                 execute immediate vSQL2;
               end;
              ]';
    execute immediate vSQL using pTableName;
end;

answered Dec 05 '16 at 13:36

Aleksej

22,443
5
33
38

+1 for effort. However, the purpose is defeated, since we still have to concatenate a string at the end, which still leaves us open to SQL injection. – Zesty Dec 05 '16 at 13:43
Why not directly `vSQL := 'insert into tabDest select * from '||tableName;`? – Wernfried Domscheit Dec 05 '16 at 14:15
@WernfriedDomscheit See tableName := 'MyTable; drop MyTable'; – Zesty Dec 05 '16 at 14:22
`execute immediate` does not accept more than one command. Anyway, check my answer and package DBMS_ASSERT. – Wernfried Domscheit Dec 05 '16 at 14:28

score 1 · Answer 3 · answered Dec 05 '16 at 14:26

I think you can do it simpler.

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);

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 := 'insert into '||i_destination||'.'||i_table_name||' select * from '||i_schema_name||'.'||i_table_name;

execute immediate l_sql;

end;

If you are concerned about SQL-Injection, have a look at package DBMS_ASSERT. This PL/SQL package provides function to validate properties of input values.

+1 for DBMS_ASSERT. The reason I'm doing it X rows at a time is to avoid massive undo. This is for moving very large audit tables. — Zesty, Dec 06 '16 at 08:04

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

3 Answers3