Use parameters in EXECUTE IMMEDIATE
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
2 v_YourNum NUMBER;
3 v_YourDesc VARCHAR2(50);
4 v_INSERT_stmt VARCHAR2(100);
5 BEGIN
6 v_INSERT_stmt := 'INSERT INTO mytable VALUES (:1, :2)';
7
8 v_YourNum := 1;
9 v_YourDesc := 'One';
10 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
11
12 v_YourNum := 2;
13 v_YourDesc := 'Two';
14 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
15
16 v_YourNum := 3;
17 v_YourDesc := 'Three';
18 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
19
20 v_YourNum := 4;
21 v_YourDesc := 'Four';
22 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
23
24 v_YourNum := 5;
25 v_YourDesc := 'Five';
26 EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
27 END;
28 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * FROM MYTABLE
2
SQL> drop table mytable;
Table dropped.
SQL> --
Related examples in the same category