Performance between using statement and string concatenation for dynamic sql
SQL>
SQL> create or replace procedure DO_WORK(x number) is
2 y number;
3 begin
4 y := x;
5 end;
6 /
Procedure created.
SQL> set timing on
SQL>
SQL> begin
2 for i in 1 .. 100 loop
3 execute immediate 'begin do_work(:x); end;' using i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL>
SQL> begin
2 for i in 1 .. 100 loop
3 execute immediate 'begin do_work('||i||'); end;';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17