Insert using Union/Union All fails to insert all rows

Question

I am trying to insert the result of a UNION into a table .While I cannot reproduce the original query here, due to it being in a restricted environment, it's structure looks similar to this:

WITH temp(X,Y,Z) AS(
SELECT....
)
SELECT X,Y,Z from TEMP --PART A
UNION 
SELECT 'A','B','C' FROM DUAL;--PART B

Part A of the query returns about 1000 records, while Part B is just a single record.

When I wrap this whole query inside a procedure, such that the result of the select statements are inserted into a table, I see that only Part B, a single record gets inserted into the target table.Here is what it loos like when I wrap it inside the procedure.

CREATE OR REPLACE PROCEDURE PROC_INSERT
AS
BEGIN
INSERT INTO TABLENAME(COLUMN1,COLUMN2....)
 WITH temp(X,Y,Z) AS(
    SELECT....
    )
    SELECT X,Y,Z from TEMP
    UNION 
    SELECT 'A','B','C' FROM DUAL;
COMMIT;
END;

I tried using both UNION and UNION ALL, but somehow I don't see to get the Part A of the result set inserted into the table.

What could be the possible reasons for such a scenario to occur? I tried to replicate the same ,but failed.

Answer 1

You can do something like this

DECLARE 
  CURSOR CUR IS SELECT X, Y, Z FROM OG_TABLE UNION SELECT 'A', 'B', 'C' FROM DUAL;
BEGIN 
  
  FOR REC IN CUR 
    LOOP 
    
    INSERT INTO NEWTABLE (COL1, COL2, COL3) VALUES (REC.X, REC.Y, REC.Z);
    
  END LOOP;
  
END;

Alternative Optimized code

DECLARE 
  LIM PLS_INTEGER := 100;

  CURSOR CUR IS SELECT X, Y, Z FROM OG_TABLE UNION SELECT 'A', 'B', 'C' 
  FROM DUAL;
 
  TYPE OG_REC IS RECORD( X OG_TABLE.X%TYPE, Y OG_TABLE.Y%TYPE, Z 
  OG_TABLE%TYPE);

  TYPE OGT IS TABLE OF OG_REC;
  OG_TAB OGT;

BEGIN 
  
  OPEN CUR;
   
  LOOP
    
    FETCH CUR BULK COLLECT INTO OG_TAB LIMIT LIM;
    
        BEGIN
     
             FORALL i IN 1..OG_TAB.COUNT SAVE EXCEPTIONS
             INSERT INTO NEWTABLE (COL1, COL2, COL3) VALUES (OG_TAB(i).X, 
             OG_TAB(i).Y, OG_TAB(i).Z);
    
        EXCEPTION
        WHEN OTHERS THEN
            NULL;
        END;
    
  EXIT WHEN CUR%NOTFOUND;
  END LOOP;
  
  COMMIT;
  CLOSE CUR;
END;