Custom Feedback Messages

Changing the Header and Footer

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



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11852.html]SQL Snippets: Custom Feedback Messages - Changing the Header and Footer[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11852.html">SQL Snippets: Custom Feedback Messages - Changing the Header and Footer</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Custom Feedback Messages - Changing the Header and Footer
  • URL (href): http://www.sqlsnippets.com/en/topic-11852.html