This method uses the SQL*Plus REPFOOTER and COLUMN commands together with a substitution variable to create custom feedback messages and provides the option of displaying or hiding column headings when no rows are selected.
In the last tutorial, Changing the Footer, we saw how to generate a custom feedback message like this one when no rows are returned by a query.
variable v_c1 varchar2(6)
execute :v_c1 := 'Z'
set feedback off
column c_footer noprint new_value v_footer
repfooter left v_footer
select
case sign( nvl( tb.row_count, 0 ) - 1 )
when 1 then chr(10) || tb.row_count || ' Items Found'
when 0 then chr(10) || tb.row_count || ' Item Found'
else '** No Items Found **'
end
as c_footer ,
tb.key, tb.c1, tb.c2, tb.c3, tb.c4
from
( select 1 from dual ) ta
left outer join
( select to_char( count(*) over () ) as row_count, t.*
from t
where c1 like :v_c1
) tb on (1=1)
;
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
** No Items Found **
To supress the column headings in this case we can tweak the solution as follows.
variable v_c1 varchar2(6)
execute :v_c1 := 'Z'
set heading off
set feedback off
column c_header_1 noprint new_value v_header_1
column c_header_2 noprint new_value v_header_2
column c_footer noprint new_value v_footer
repheader left v_header_1 skip 1 v_header_2
repfooter left v_footer
select
nvl2
( tb.row_count ,
' KEY C1 C2 C3 C4' ,
null
) as c_header_1 ,
nvl2
( tb.row_count ,
'---------- ------ ---------- ---------- ----------' ,
null
) as c_header_2 ,
case sign( nvl( tb.row_count, 0 ) - 1 )
when 1 then chr(10) || tb.row_count || ' Items Found'
when 0 then chr(10) || tb.row_count || ' Item Found'
else '** No Items Found **'
end
as c_footer ,
tb.key, tb.c1, tb.c2, tb.c3, tb.c4
from
( select 1 from dual ) ta
left outer join
( select to_char( count(*) over () ) as row_count, t.*
from t
where c1 like :v_c1
) tb on (1=1)
;
** No Items Found **
The output for reports with 1 row and more than 1 row is as follows.
execute :v_c1 := 'X'
/
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
1 X AAA 2005-04-03 3000
1 Item Found
execute :v_c1 := '%'
/
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
1 X AAA 2005-04-03 3000
2 Y BB 2006-05-04 2000
3 Y CCCC 2007-06-05 4000
4 Y 2008-07-06 0
5 Y DDDDD 5000
6 Y
6 Items Found
Note this technique also allows you to "pimp your column headings" in ways not possible with the usual SQL*Plus SET options. For example, you can use different underline characters for diffferent columns, and you can include a blank line between column headings and rows. Here is an example.
execute :v_c1 := '%'
select
nvl2
( tb.row_count ,
' Key Col 1 Col 2 Col 3 Col 4' ,
null
) as c_header_1 ,
nvl2
( tb.row_count ,
'---------- ------ ---------- ---------- **********' || -- uses "--" and "**"
chr(10) , -- adds blank line
null
) as c_header_2 ,
case sign( nvl( tb.row_count, 0 ) - 1 )
when 1 then chr(10) || tb.row_count || ' Items Found'
when 0 then chr(10) || tb.row_count || ' Item Found'
else '** No Items Found **'
end
as c_footer ,
tb.key, tb.c1, tb.c2, tb.c3, tb.c4
from
( select 1 from dual ) ta
left outer join
( select to_char( count(*) over () ) as row_count, t.*
from t
where c1 like :v_c1
) tb on (1=1)
;
Key Col 1 Col 2 Col 3 Col 4
---------- ------ ---------- ---------- **********
1 X AAA 2005-04-03 3000
2 Y BB 2006-05-04 2000
3 Y CCCC 2007-06-05 4000
4 Y 2008-07-06 0
5 Y DDDDD 5000
6 Y
6 Items Found
Glossary
Apologies to all my international readers for using western slang above. For those of you that do not recognize the term, here is what my lame attempt at a pun was getting at. :-)
- pimp
- "a verb such as 'You're pimped up!' or 'Pimp my ride.' The latter example refers to customizing an automobile, made popular by the show Pimp My Ride on MTV ... an American slang term for being unique, 'cool' or socially desirable." -- Wikipedia - Pimp