CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Random guy
Random guy

Reputation: 923

Insert into not working on plsql in oracle

declare
vquery long;
cursor c1 is
select * from temp_name;

begin

for i in c1
loop
vquery :='INSERT INTO ot.temp_new(id)
select '''||i.id||''' from ot.customers';
dbms_output.put_line(i.id);
end loop;

end;
/

Output of select * from temp_name is :

ID                                                                              
--------------------------------------------------------------------------------
customer_id                                                                     
1 row selected.

I have customers table which has customer_id column.I want to insert all the customer_id into temp_new table but it is not being inserted. The PLSQL block executes successfully but the temp_new table is empty. The output of dbms_output.put_line(i.id); is 

customer_id

What is wrong there?

Upvotes: 0

Views: 1737

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

The main problem is that you generate a dynamic statement that you never execute; at some point you need to do:

execute immediate vquery;

But there are other problems. If you output the generated vquery string you'll see it contains:

INSERT INTO ot.temp_new(id)
select 'customer_id' from ot.customers

which means that for every row in customers you'll get one row in temp_new with ID set to the same fixed literal 'customer_id'. It's unlikely that's what you want; if customer_id is a column name from customers then it shouldn't be in single quotes.

As @mathguy suggested, long is not a sensible data type to use; you could use a CLOB but only really need a varchar2 here. So something more like this, where I've also switched to use an implicit cursor:

declare
  l_stmt varchar2(4000);
begin
  for i in (select id from temp_name) 
  loop
    l_stmt := 'INSERT INTO temp_new(id) select '||i.id||' from customers';
    dbms_output.put_line(i.id);
    dbms_output.put_line(l_stmt);
    execute immediate l_stmt;
  end loop;
end;
/

db<>fiddle

The loop doesn't really make sense though; if your temp_name table had multiple rows with different column names, you'd try to insert the corresponding values from those columns in the customers table into multiple rows in temp_new, all in the same id column, as shown in this db<>fiddle.

I guess this is the starting point for something more complicated, but still seems a little odd.

Upvotes: 6

Related Questions