Mega Code Archive
Demonstrates the effect of duplicate placeholders with EXECUTE IMMEDIATE
<![CDATA[
SQL>
SQL>
SQL> CREATE TABLE duplicates (
2 f1 NUMBER,
3 f2 NUMBER,
4 f3 NUMBER,
5 f4 NUMBER);
Table created.
SQL>
SQL> DECLARE
2 v_InsertStmt VARCHAR2(100);
3 v_f1 NUMBER := 1;
4 v_f2 NUMBER := 2;
5 v_f3 NUMBER := 3;
6 BEGIN
7 v_InsertStmt :=
8 'INSERT INTO duplicates VALUES (:a, :b, :c, :a)';
9
10 EXECUTE IMMEDIATE v_InsertStmt
11 USING v_f1, v_f2, v_f3, v_f1;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
2 FROM duplicates;
F1 F2 F3 F4
---------- ---------- ---------- ----------
1 2 3 1
1 row selected.
SQL>
SQL> DECLARE
2 v_InsertStmt VARCHAR2(100);
3 v_f1 NUMBER := 4;
4 v_f2 NUMBER := 5;
5 v_f3 NUMBER := 6;
6 BEGIN
7 v_InsertStmt :=
8 'BEGIN ' ||
9 ' INSERT INTO duplicates VALUES (:a, :b, :c, :a); ' ||
10 'END;';
11
12 EXECUTE IMMEDIATE v_InsertStmt
13 USING v_f1, v_f2, v_f3;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
2 FROM duplicates;
F1 F2 F3 F4
---------- ---------- ---------- ----------
1 2 3 1
4 5 6 4
2 rows selected.
SQL>
SQL> DROP TABLE duplicates;
Table dropped.
SQL>
SQL>
]]>