Oracle DB union two select statements which return multiple rows I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error &quot;exact fetch returns more than requested number of rows&quot;, which occurs of course because I have more than 1 row being retunrned from the second select statement.
This is my stored procedure:
DECLARE

timer_before timestamp;
timer_after timestamp;
timer_duration number;

referencelistid number(20);
referencename varchar(20);
startdate timestamp;
enddate timestamp;
creationuser varchar(20);
creationdate timestamp;
changeuser varchar(20);
changedate timestamp;
username varchar(20);
changetime timestamp;

BEGIN
timer_before :=systimestamp;
--DBMS_OUTPUT.put_line('Start at: ' || timer_before);

SELECT 
    REFERENCE_LIST_ID, 
    REFERENCE_NAME, 
    START_DATE, 
    END_DATE, 
    CREATION_USER, 
    CREATION_DATE, 
    CHANGE_USER, 
    CHANGE_DATE, 
    USERNAME, 
    CHANGE_TIME 
into 
    referencelistid, 
    referencename, 
    startdate, 
    enddate, 
    creationuser, 
    creationdate, 
    changeuser, 
    changedate, 
    username, 
    changetime 
from 
    tbrd_reference_lists 
where 
    reference_list_id = 0000
UNION SELECT 
    REFERENCE_LIST_ID, 
    REFERENCE_NAME, 
    START_ DATE, 
    END_DATE, 
    CREATION_USER, 
    CREATION_DATE, 
    CHANGE_USER, 
    CHANGE_DATE, 
    USERNAME, 
    CHANGE_TIME 
from 
    tbrd_reference_lists_h 
where 
    reference_list_id = 0000;

timer_after :=systimestamp;
--DBMS_OUTPUT.put_line('End at: ' || timer_after);

select
  extract(second from  timer_after-timer_before) into timer_duration
from
  dual;

DBMS_OUTPUT.put_line('Delete Duration: (s): ' || timer_duration);
DBMS_OUTPUT.put_line('Delete Duration: (ms) ' || timer_duration*1000);

END;

I tried to search for a solution, and pipeline functions look like an option. Is there anything simpler that I can use to achieve this
Thanks
Pulkit
Code Review

Always define variables types with %TYPE where it makes sense.

This way, when the table changes, you don't have to go back and fix code.

Bulk Collect

When you are receiving multiple rows from a query, you need to BULK COLLECT INTO a collection.

Defining a Record, then a Table Type is one way to define a Collection Type.  If you were selecting all columns (eg SELECT *) then TYPE many_rows_t TABLE OF tablename%rowtype would be another way.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  TYPE one_row_t IS RECORD (
    referencelistid   tbrd_reference_lists.reference_list_id%TYPE -- number(20),
   ,referencename     tbrd_reference_lists.reference_name%TYPE -- varchar(20),
   ,startdate         tbrd_reference_lists.start_date%TYPE -- timestamp,
   ,enddate           tbrd_reference_lists.end_date%TYPE -- timestamp,
   ,creationuser      tbrd_reference_lists.creation_user%TYPE -- varchar(20),
   ,creationdate      tbrd_reference_lists.creation_date%TYPE -- timestamp,
   ,changeuser        tbrd_reference_lists.change_user%TYPE -- varchar(20),
   ,changedate        tbrd_reference_lists.change_date%TYPE -- timestamp,
   ,username          tbrd_reference_lists.username%TYPE -- varchar(20),
   ,changetime        tbrd_reference_lists.change_time%TYPE -- timestamp
  );
  TYPE many_rows_t IS
    TABLE OF one_row_t;

  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

--DBMS_OUTPUT.put_line('Start at: ' || timer_before);
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  BULK COLLECT INTO
    some_buffer
  FROM tbrd_reference_lists
  WHERE
    reference_list_id   = 0000
  UNION
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  FROM tbrd_reference_lists_h
  WHERE
    reference_list_id   = 0000;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
END;


Limiting memory consumption

The above example is OK if you are receiving a few rows.  But, if you are receiving a few Million rows, you may run into memory problems.

Instead, you'll want to LIMIT how many rows you fetch at one time.

To do this, you need to use a CURSOR and FETCH a limited number of rows at a time.
Please take the time to read this Oracle Magazine article and the Oracle Documentation.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  CURSOR C IS 
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists
    WHERE
      reference_list_id   = 0000
    UNION
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists_h
    WHERE
      reference_list_id   = 0000;    

  TYPE many_rows_t is table of C%rowtype;
  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

  OPEN C;

  loop
    FETCH C bulk collect into some_buffer limit 100;

    /* process data */

    exit when C%notfound;
  end loop;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
EXCEPTION
  -- Prevent cursor leakage
  when others then
    if C%isopen then
      close c;
    end if;

    raise;
END;