Insert into a Table using Execute Immediate

Insert into a Table using Execute Immediate

 Oracle PL/SQL supports Dynamic sql through which DML operations can be performed using bind variables.

You have a table called ab

SQL>desc ab;

SQL> desc ab;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------------
 ID                                                                                           NUMBER
 NAME                                                                                         VARCHAR2(20)

Insert records into ab table using Execute Immediate/Dynamic SQL

--it has 2 columns declare 2 variables of same type

--declare another variable to hold sql statement. here insert

--assign values to 2 variables

--pass sql statement to execute immediate statement along with 2 variables .

declare
n number:=1;
nm varchar2(20);
sql_stmt varchar2(250);
begin
sql_stmt :='insert into ab values(:1,:2)';
n:=2;
nm:='hello';
execute immediate sql_stmt using n,nm;
end;

--Execute the above block of code

SQL> /

PL/SQL procedure successfully completed.

--test whether insert successful or not

SQL> select * from ab;

        ID NAME
---------- --------------------
         2 hello

Tags:Insert into a Table using Execute Immediate,Execute Immediate for Insert,Execute Immediate with using variables, Insert data using dynamic sql, inserting data using Execute immediate,