Print a custom message when no rows are returned by a SELECT statement

I am trying to create a report of INVALID objects in Oracle. The requirement is to print a custom message when no rows are returned (This is because the output will be redirected to an HTML report). I tried the following query:

(select count(*), owner,object_type,to_char( LAST_DDL_TIME, 'DD-MM-YYYY HH24:MI:SS' ) as last_ddl_time 
from dba_objects 
where status='INVALID' and   trunc(last_ddl_time) = sysdate -1 
group by owner,object_type , LAST_DDL_TIME order by object_type) 
UNION 
(select '** DDL Changes in the last 24 hours' text from dual);

Error am seeing:

ORA-00907: missing right parenthesis

While the individual queries work without an issue, it fails to execute when a UNION is introduced. How can I fix this?

Answer

It can’t work that way. If you’re using UNION, SELECT statements have to return the same number of columns which match in datatype.

This is an invalid procedure (created in my SYS schema for simplicity; don’t do that yourself):

SQL> create or replace procedure p_test
  2  is
  3  begin
  4  end;
  5  /

Warning: Procedure created with compilation errors.

Query you’d run; notice NOT EXISTS in the second query – it’ll prevent the message to be displayed if there are some invalid objects. Also, I modified date condition to “today” (subtracted 0) as I can’t wait until tomorrow to post it.

SQL> select
  2    count(*),
  3    owner,
  4    object_type,
  5    to_char(LAST_DDL_TIME, 'DD-MM-YYYY HH24:MI:SS') as last_ddl_time
  6  from dba_objects
  7  where status = 'INVALID'
  8    and trunc(last_ddl_time) = trunc(sysdate) - 0
  9  group by owner,
 10           object_type,
 11           LAST_DDL_TIME
 12  UNION
 13  select
 14    0,
 15    null,
 16    'DDL Changes in the last 24 hours',
 17    to_char(sysdate, 'dd-mm-yyyy')
 18  from dual
 19  where not exists (select null
 20                    from dba_objects
 21                    where status = 'INVALID'
 22                      and trunc(last_ddl_time) = trunc(sysdate) - 0
 23                   )
 24  order by object_type;

  COUNT(*) OWNER      OBJECT_TYPE                      LAST_DDL_TIME
---------- ---------- -------------------------------- -------------------
         1 SYS        PROCEDURE                        04-04-2021 12:36:07

SQL>

If I drop the invalid procedure:

SQL> drop procedure p_test;

Procedure dropped.

SQL> select
  2    count(*),
  3    owner,
  4    object_type,
  5    to_char(LAST_DDL_TIME, 'DD-MM-YYYY HH24:MI:SS') as last_ddl_time
  6  from dba_objects
  7  where status = 'INVALID'
  8    and trunc(last_ddl_time) = trunc(sysdate) - 0
  9  group by owner,
 10           object_type,
 11           LAST_DDL_TIME
 12  UNION
 13  select
 14    0,
 15    null,
 16    'DDL Changes in the last 24 hours',
 17    to_char(sysdate, 'dd-mm-yyyy')
 18  from dual
 19  where not exists (select null
 20                    from dba_objects
 21                    where status = 'INVALID'
 22                      and trunc(last_ddl_time) = trunc(sysdate) - 0
 23                   )
 24  order by object_type;

  COUNT(*) OWNER      OBJECT_TYPE                      LAST_DDL_TIME
---------- ---------- -------------------------------- -------------------
         0            DDL Changes in the last 24 hours 04-04-2021

SQL>