Q:

return a default row in sql

Is it possible in oracle sql to return a default row if no rows found.
I have a process where in rows fetched will be put in a flat ascii file.
now i have a requirement that if there are no rows fetched by the sql query then there should be a default row in the ascii file.
is it possible in sql to output a default row if no rows fetched by the query
note: i dont want to use pl/sql.

A:

For complex queries where the overhead on finding out if there is a row in the result set is onerous or the query is just very large and unwieldy, then a subquery factoring clause might be beneficial:
With my_query as
    (
    select a, b, c from foobar where foo='FOO' 
    )
Select a,b,c
From   my_query
Union All
Select ...
From   dual
Where  Not Exists
       (Select 1 from my_query)
/

A:

You could use UNION ALL for this:
select a, b, c from foobar 
where foo='FOO'
union all
select 'def', 'ault', 'value' from dual 
where not exists ( select 'x' from foobar where foo='FOO' )

