0

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 "exact fetch returns more than requested number of rows", 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

  • You will need to declare that SELECT statement as a cursor and loop through the rows returned by the that cursor performing whatever action is appropriate for each row. – BriteSponge Feb 13 at 10:31
  • You are not interested in this SELECT .. UNION SELECT .. result? You need in execution time only? Maybe simply wrap it with something like SELECT COUNT(*) INTO dummy_variable FROM (SELECT .. UNION SELECT ..)? Or the same with your query in CTE? – Akina Feb 13 at 10:47
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.