How to insert dynamically through a variable in PL/SQL?
43,967
Solution 1
You need to use different variables for each value
declare
v_data1 number
v_data2 varchar2(50);
begin
v_data1 :=1
v_data2 = 'sunny';
insert into test values(v_data1,v_data2);
-- Alternatively insert into test (Name) values (v_data2);
commit;
end;
Solution 2
Table test has two columns. You're only inserting one and not naming which column it is hence "not enough values". So you need:
INSERT INTO test (name) VALUES (data)
or probably better is to put in an ID:
INSERT INTO test (id, name) VALUES (1, data)
or simply:
INSERT INTO test VALUES (1, data)
For this kind of thing though I would use a cursor rather than dynamic SQL (or even inline SQL).
Solution 3
The normal way to pass values into dynamic SQL statements is with bind variables like this:
declare
v_id integer;
v_name varchar2(50);
begin
v_id := 1;
v_name := 'sunny';
execute immediate
'insert into test (id, name) values(:b1, :b2)'
using v_id, v_name;
commit;
end;
That needs one variable per value.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Author by
Admin
Updated on July 05, 2022Comments
-
Admin almost 2 years
Lets create a table first
create table test ( id number, name varchar2(20) );
Now during insert, I want to hold the data into variable first & then dynamically pass the variable into the VALUES clause like this:
declare v_data varchar2(50); begin v_data:='1,sunny'; execute immediate 'insert into test values(v_data)'; commit; end;
But its showing some errors(Not enough values)...... plz help how to achieve this??