Oracle while exists(select ...) insert into

Question

I am trying to insert id's into a temporary table with while loop. TEMP_TABLE contains ID-s which = ID of entry in ID_TABLE. ID_TABLE has two fields CON_1 and CON_2, which refer to another entry in ID_TABLE.

I want to add all CON_1 and CON_2 values from ID_TABLE into TEMP_TABLE where ID_TABLE.ID = TEMP_TABLE.ID and CON_1 or CON_2 are not already in TEMP_TABLE and then repeat the process until there are no IDs left to insert (after adding CON_1 or CON_2 to TEMP_TABLE, those ID-s might refer to ID_TABLE.ID where CON_1 or CON_2 are not already present in TEMP_TABLE).

Basically, an ID might have connections as another ID and I want to add ID, it's connections and the connections for the connections ... into TEMP_TABLE.

The query I made this far:

begin
    while exists(select extId
                 from (
                          select distinct case
                                              when con.CON_1 = idTable.ID
                                                  then con.CON_2
                                              else con.CON_1
                                              end
                                              as extId
                          from ID_TABLE idTable
                                   inner join TEMP_TABLE temp on idTable.ID = temp.ID
                                   inner join CONNECTIONS_TABLE con on con.CON_2 = idTable.ID
                              or con.CON_1 = idTable.ID)
                 where not exists(select ID from TEMP_TABLE where ID = extId))
        loop
            insert into TEMP_TABLE
            select extId
            from (
                     select distinct case
                                         when con.CON_1 = idTable.ID
                                             then con.CON_2
                                         else con.CON_1
                                         end
                                         as extId
                     from ID_TABLE idTable
                              inner join TEMP_TABLE temp on idTable.id = temp.ID
                              inner join CONNECTIONS_TABLE con on con.CON_2 = idTable.id
                         or con.CON_1 = idTable.ID)
            where not exists(select ID from TEMP_TABLE where ID = extId);
        end loop;
end;

When I run the query, I get this error:

PLS-00103: Encountered the symbol "INNER" when expecting one of the following:
   ) , with group having intersect minus order start union where
   connect

Running on Oracle 12c

Answer 1

You appear to be trying to recursively add CON_1 or CON_2 values from the CONNECTIONS_TABLE that were connected to a prior ID value in both the TEMP_TABLE and the ID_TABLE.

You don't appear to need the WHILE loop (or even PL/SQL) and can use a single MERGE statement and a hierarchical query:

MERGE INTO temp_table DST
USING (
  SELECT DISTINCT
         extid
  FROM   (
    select con_1, con_2
    from   connections_table c
    START WITH EXISTS(
      SELECT 1
      FROM   ID_TABLE i
             inner join TEMP_TABLE t
             on ( i.id = t.ID )
      WHERE  i.id IN ( c.con_1, c.con_2 )
    )
    CONNECT BY NOCYCLE
         PRIOR con_1 IN ( con_1, con_2 )
    OR   PRIOR con_2 IN ( con_1, con_2 )
  )
  UNPIVOT (
    extid FOR con IN ( con_1 AS 1, con_2 AS 2 )
  )
) src
ON ( src.extID = dst.id )
WHEN NOT MATCHED THEN
  INSERT ( id ) VALUES ( src.extid );

Which for the initial setup:

CREATE TABLE temp_table( id ) AS
SELECT 1 FROM DUAL;

CREATE TABLE connections_table( con_1, con_2 ) AS
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 5, 10 FROM DUAL;

CREATE TABLE id_table ( id ) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;

Would insert 3 rows and then:

SELECT * FROM temp_table;

Outputs:

ID
1
2
4
3

db<>fiddle here