How to insert dynamically through a variable in PL/SQL?

Question

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??

Answer 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;