This method uses the SQL*Plus REPFOOTER and COLUMN commands together with a substitution variable to create custom feedback messages. With it you can replace standard SQL*Plus feedback messages like
6 rows selected. no rows selected
with text like
6 Items Found 0 Items Found ** No Items Found **
Before we examine the solution, consider a report like the following, which displays a standard SQL*Plus feedback message.
variable v_c1 varchar2(6) execute :v_c1 := '%' set feedback on select * from t where c1 like :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 rows selected.
To change the
6 rows selected.
text into something like
6 Items Found
you can use a report like this.
set feedback off
column c_footer noprint new_value v_footer
repfooter left v_footer
select
chr(10) || nvl( tb.row_count, 0 ) || ' Items Found' 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
---------- ------ ---------- ---------- ----------
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
The output for reports with 1 and 0 rows would be as follows.
execute :v_c1 := 'X' /
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
1 X AAA 2005-04-03 3000
1 Items Found
execute :v_c1 := 'Z' /
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
0 Items Found
While acceptable, the results could use a little fine tuning. It would be nice
to see
1 Item Found
instead of
1 Items Found and
** No Items Found **
instead of
0 Items Found.
Also, there should only be one blank line between the column headings and the
message
0 Items Found,
not two.
With a little extra code we can easily address these minor annoyances.
variable v_c1 varchar2(6)
execute :v_c1 := '%'
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' -- plural
when 0 then chr(10) || tb.row_count || ' Item Found' -- singular
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
---------- ------ ---------- ---------- ----------
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
execute :v_c1 := 'X' /
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
1 X AAA 2005-04-03 3000
1 Item Found
execute :v_c1 := 'Z' /
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
** No Items Found **
We will refine this solution even further in the next tutorial, Changing the Header and Footer, where we customize the column headings in addition to the feedback message.
Gotchas
If you use the SET NULL command with this technique your results may look misleading when the report returns no rows. In the following example it looks like table T contains a row with nulls in every column. In fact it does not. The row of nulls is an artifact of the left outer join used in the technique.
set null "(null)" /
KEY C1 C2 C3 C4
---------- ------ ---------- ---------- ----------
(null) (null) (null) (null) (null)
** No Items Found **