asked on

PL/SQL insert using hard-coded values and a select

Hi, I know this is probably going to be an easy one for most of you but I just can't figure it out. I'm not exactly a PL/SQL expert and I hope someone can help me out. I'm working on an Oracle 10g db and I need to insert data into a table. Simple enough... The table has 3 columns - colA will always have a value of 1, colB will be gotten from a different table using a pretty simple select and colC will always be NULL.

I've tried my hand at using PL/SQL :) but of course it didn't work. I figured I'd need to use a cursor since I'm going to be getting back more that 1 result on my select. Am I even a little close?

Thanks,
biglarrrr

DECLARE
CURSOR curs IS
select a.colB
from table2
where blahblahblah;
 
curs_rec curs%rowtype;
Begin
FOR curs_rec IN curs LOOP
insert into table1(colA, colB, colC) values (1, curs_rec, null);
 
end loop;
 
EXCEPTION
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('No more Data to be processed');
 
end;

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

if you want to see the iterative way, you almost had it, the only thing you were missing was the column name

DECLARE
CURSOR curs IS
select a.colB
from table2
where blahblahblah;
 
curs_rec curs%rowtype;
Begin
FOR curs_rec IN curs LOOP
insert into table1(colA, colB, colC) values (1, curs_rec.colb, null);
 
end loop;
 
EXCEPTION
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('No more Data to be processed');
 
end;

Open in new window

Sean Stuber

and, you aren't going to use the explicitly declare curs_rec variable.

The FOR LOOP creates its own variable.
So, the curs_rec you see in the FOR loop is NOT the same one you declared above.

but it's moot, you should do the insert directly from the select anyway

Larry Groves

ASKER

Hi sdstuber. Thanks so much for this. I really appreciate it and your feedback.

Thanks,
biglarrrr

Sean Stuber

glad I could help!