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??
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;
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).
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.
using 'v_id', 'v_name'?Your approach works, but you need to adjust your query a little:
execute immediate 'insert into test values(' || v_data|| ')';
so that the contents of your v_data variable are actually inserted into the string, not the value "v_data" itself.
