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 wanna hold the data into variable first & then dynamically pass the variable into the VALUES clause.

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

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).

Answer 2

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;

Answer 3

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.

Answer 4

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.