Insert Rows Using ROWNUM In Oracle

Question

In my code I am trying to insert data in a for loop based on the row number. The reason I want to use it is because otherwise I get a "single-row subquery returns more than one row" error, because my select subqueries return more than one row, indeed and obviously I want to insert it one at a time.

declare
begin
for x in (select * from PilotKeyLookup) loop
if x.p2id != null then
insert into BridgeTable (groupid, pilotid) values (sqBridgeGroupID.nextval, (select p1id from PilotKeyLookup));
insert into BridgeTable (groupid, pilotid) values (sqBridgeGroupID.currval, (select p2id from PilotKeyLookup));
else
insert into BridgeTable (groupid, pilotid) values (sqBridgeGroupID.nextval, (select p1id from PilotKeyLookup));
end if;
end loop;
end;

And this is how I am trying to use rownum:

declare
begin
for x in (select * from PilotKeyLookup) loop
if x.p2id != null then
insert into BridgeTable (groupid, pilotid) values (sqBridgeGroupID.nextval, (select p1id from PilotKeyLookup where rownum = x));
insert into BridgeTable (groupid, pilotid) values (sqBridgeGroupID.currval, (select p2id from PilotKeyLookup where rownum = x));
else
insert into BridgeTable (groupid, pilotid) values (sqBridgeGroupID.nextval, (select p1id from PilotKeyLookup where rownum = x));
end if;
end loop;
end;

However, I get an "expression is of wrong type" error. Any suggestions?

Answer 1

You probably need a conditional insert INSERT ... WHEN ... THEN, like this:

CREATE TABLE PilotKeyLookup(
  p1id int, p2id int
);

INSERT INTO PilotKeyLookup VALUES( 5, null );
INSERT INTO PilotKeyLookup VALUES( 6, 7 );

CREATE TABLE BridgeTable(
   groupid int, pilotid int
);

CREATE SEQUENCE sqBridgeGroupID;

INSERT
  WHEN 1 = 1 THEN INTO BridgeTable( groupid, pilotid ) 
                  VALUES ( sqBridgeGroupID.nextval, p1id )
  WHEN p2id is not null THEN INTO BridgeTable( groupid, pilotid ) 
                  VALUES ( sqBridgeGroupID.currval, p2id )
SELECT *
FROM PilotKeyLookup p;



select * from BridgeTable;

   GROUPID    PILOTID
---------- ----------
         1          5 
         2          6 
         2          7