execute immediate with 'insert ... using' to pass variable into insert statement
SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL> set echo on
SQL>
SQL> declare
2 myNumber number;
3 begin
4 for i in 1 .. 10
5 loop
6 myNumber := dbms_random.random;
7
8 execute immediate
9 'insert into t values ( :x1, :x2, :x3, :x4 )'
10 using myNumber, myNumber, myNumber, myNumber;
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
C1 C2 C3 C4
---------- ---------- ---------- ----------
-215681493 -215681493 -215681493 -215681493
65675438 65675438 65675438 65675438
-544483009 -544483009 -544483009 -544483009
327912408 327912408 327912408 327912408
-233632941 -233632941 -233632941 -233632941
623357100 623357100 623357100 623357100
616373886 616373886 616373886 616373886
-703556128 -703556128 -703556128 -703556128
-928105331 -928105331 -928105331 -928105331
-179825426 -179825426 -179825426 -179825426
10 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Related examples in the same category