Execute immediate can cause data corruption
I am a fan of execute immediate statement, but I found one problem. execute immediate is parsing string '--' as a beginning of a comment, this behaviour can lead to a major data corruption. Although this can easily handled by using parameters, but I still concerned with this because lot of my source code are not using parameters.
Example
create or replace procedure update_table (pvalue number,pcustid number) is
begin
execute immediate 'update tab_customer set sales=sales-' || to_char(pvalue) || ' where customer_id=' || to_char(pcustid);
end;
/
if you call the procedure using positive pvalue, the result is normal, 1 row will be updated every call.
Example
create or replace procedure update_table (pvalue number,pcustid number) is
begin
execute immediate 'update tab_customer set sales=sales-' || to_char(pvalue) || ' where customer_id=' || to_char(pcustid);
end;
/
if you call the procedure using positive pvalue, the result is normal, 1 row will be updated every call.
0