Oracle DB: Return second query if first query is empty

Question

I am writing an Oracle stored procedure to return the results of a database query. If the query does not produce any results, a second query must be run in its place.

In SQL Server, I can accomplish this using something similar to the following:

INSERT INTO @TableVar
SELECT <joinQuery1>;

IF (SELECT COUNT(*) FROM @TableVar) > 0
BEGIN
    SELECT * FROM @TableVar;  -- returns <joinQuery1>
END
ELSE
    SELECT <joinQuery2>;  --returns <joinQuery2>
END

However, I can not wrap my head around how to accomplish the same task in Oracle.

Answer 1

You can utilize WITH to make this perform better (and easier to maintain):

WITH query1 as (
    select 1, 2
    from dual
    where 1=0
    connect by level <= 10
),
query2 as (
    select 3, 4
    from dual
    connect by level <= 10
)
select *
from query1
union all
select *
from query2
where not exists (
    select null
    from query1
);

As is this should return the 10 rows from query2. If you remove the where 1=0 from query1 (causing it to actually return rows), you should get the 10 rows from query1.