SQL & PL/SQL

Hello All

I came up with below code to avoid writing a select query twice.

Actual requirement is writing various validation queries to validate data present in lookup tables using LEFT join with lookup table and check the count of records where data is missing in lookup.

If data is missing in lookup send an email with the data from main txn tables as an attachment ( few 100s records) for which lookup does not have matching records. This requires to write a select query twice. First to check count and if count is greater than 1 then OPEN a cursor with same select query generate the output and send an email.

I came up with below code purely in terms of less code to maintain (Performance is not an issue as its a small data set) . When I say less code its entire join between tables and WHERE clause which gets repeated.

In below code I have put ELSE part which will actually not be required.

Do ideally with just 2 line of code I am able to replace entire select count(1) query which otherwise is require to be repeated.

Do you see any drawback of this approach?

DECLARE
  lv_cur_number  INTEGER;
  lv_ref_cur              SYS_REFCURSOR  ;
  lv_count NUMBER;
BEGIN   
  --Actual select query will be much more complex with joins and various conditions in WHERE clause
  OPEN lv_ref_cur
   FOR SELECT * 
         FROM user_tables;
 
  lv_cur_number := DBMS_SQL.TO_CURSOR_NUMBER(lv_ref_cur);
  lv_count := DBMS_SQL.FETCH_ROWS(lv_cur_number);   
  
  IF ( lv_count > 0 ) THEN
     DBMS_OUTPUT.PUT_LINE ('Query has data = '||lv_count);
  ELSE   
     DBMS_OUTPUT.PUT_LINE ('Query has NO data');   
  END IF;
END;
/
  

Regards

Arun