Oracle DB: Return second query if first query is empty

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:

  • is mysqli command also avaible for use in mssql server?
  • How to aggregate column in One SQL without using the inner query in SQL server
  • Convert YYYYMM to MMMYY
  • TSQL ParentID Recursive SP
  • Microsoft SQL Server error with Laravel: Why this solves my mistake?
  • SQL Server Management Studio 2014 - possible to filter results grid?
  • INSERT INTO @TableVar
    SELECT <joinQuery1>;
    IF (SELECT COUNT(*) FROM @TableVar) > 0
        SELECT * FROM @TableVar;  -- returns <joinQuery1>
        SELECT <joinQuery2>;  --returns <joinQuery2>

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

  • Get latest date before date value in row
  • PATINDEX() replacement in MYSQL
  • ROW_Count() To Start Over Based On Order
  • How to join two tables together with same number of rows by their order
  • Is there a way to get a list of all current temporary tables in SQL Server?
  • User-Defined Functions SQL Server 2005 flagged incorrectly as non-deterministic?
  • 2 Solutions collect form web for “Oracle DB: Return second query if first query is empty”

    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.

    The answer depends very much how are you going to use results of query further. So you should either use pipelened functions, insert into GTT or return ref cursor.

    At any case I would recommend you to do it in 1 SQL statement to achieve read consistency.

    So please consider something like

    create procedure test (pCursor out sys_refcursor) is
     open pCursor for
     select <joinQuery1>
     union all
     SELECT <joinQuery2>
     where not exists (select 1 from joinquery1)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.