Rem Filename   : header_title.sql
Rem 
Rem Parameters : &1 = length of separator lines, if 0 or a negative value is specified, then
Rem                   no separator lines are displayed.
Rem                   It is best to specify values > 100 (or 0 or negative) if the S option is 
Rem                   used in &2.
Rem
Rem              &2 = content indicator : D      = database identification
Rem                                       DC     = database identification + current date and time
Rem                                       DS     = database identification + startup date and time
Rem                                       DCS    = database identification + current date and time
Rem                                                + startup date and time
Rem                                       -[DCS] = if the content indicator is prefixed with a dash,
Rem                                                then all heading lines start with a "--" comment 
Rem                                                indicator.
Rem                   If you want to omit all of the above options, use the empty string ''.
Rem
Rem              &3 = The text of the title, if there are blanks in the title, the you should
Rem                   include the title in single quotes.
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.x, v9.x
Rem
Rem Modification history
Rem --------------------
Rem 04-Jun-2003  Initial creation.
Rem 13-Jun-2003  Added weekday to dates.
Rem 06-Oct-2003  Added new option to &2 to prefix all header lines with a "--" 
Rem              comment indicator, this comes in handy for headers in generated scripts
Rem              to avoid run-time errors while executing the generated scripts.
Rem 09-Oct-2003  Enlarged title variable from 80 to 150 characters.
Rem 13-Oct-2003  Bug correction : replaced length(v_comment_symbol) by 
Rem              nvl(length(v_comment_symbol),0) to handle empty string properly.
Rem 28-Jun-2004  Use chr(9) (=tab) in stead of chr(160) for whitespace at the beginning of a
Rem              line. chr(160) doesn't work well for unicode character sets. 
Rem
Rem =====================================================================================================
Rem
Rem Description : Displays a report identification header with some optional database identification
Rem               information.
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<GLORYETL> @@header_title 101 DCS 'TABLESPACE OVEREVIEW REPORT'
Rem 
Rem -----------------------------------------------------------------------------------------------------
Rem                                     TABLESPACE OVEREVIEW REPORT
Rem 
Rem                              of database GLORYETL on server beahs661640
Rem -----------------------------------------------------------------------------------------------------
Rem Startup date and time : Tuesday   03-JUN-2003 10:20:42  ( uptime = 10 days, 1 hours and 39 minutes )
Rem Current date and time : Friday    13-JUN-2003 12:00:25
Rem -----------------------------------------------------------------------------------------------------
Rem 
Rem =====================================================================================================

SET SERVEROUTPUT  ON

declare
  v_inst_name       varchar2(30);
  startup_time      date;
  v_host_name       varchar2(30);
  uptime            number;
  uptime_days       number;
  uptime_hours      number;
  uptime_minutes    number;
  title             varchar2(150) := '&3';
  v_text            varchar2(80);
  v_comment_symbol  varchar2(3) := '';


begin
  SELECT instance_name, startup_time, host_name
    INTO v_inst_name, startup_time, v_host_name
    FROM v$instance;

  uptime := sysdate - startup_time;
  uptime_days := trunc(uptime);
  uptime_hours := trunc((uptime - uptime_days)*24);
  uptime_minutes := trunc((uptime - ( uptime_days + uptime_hours/24 ))*60*24);

  if ( instr( '&2', '-' ) > 0 ) then v_comment_symbol := '--'; end if;

  if ( &1 > 0 ) then dbms_output.put_line( chr(10) || rpad( '-', &1, '-' ) ); end if;

  dbms_output.put_line( v_comment_symbol || 
                        rpad(chr(9),trunc((&1-(nvl(length(v_comment_symbol),0)+length(title)))/2)-6) || 
                        title );

  if ( instr( '&2', 'D' ) > 0 ) then
    v_text := 'of database '  || v_inst_name || ' on server ' || v_host_name;
    dbms_output.put_line ( v_comment_symbol || chr(10) || v_comment_symbol || 
                           rpad(chr(9),trunc((&1-(nvl(length(v_comment_symbol),0)+length(v_text)))/2)-6) || 
                           v_text );
  end if;

  if ( &1 > 0 ) then dbms_output.put_line( rpad( '-', &1, '-' ) ); end if;
  if ( instr( '&2', 'S' ) > 0 ) then
    dbms_output.put_line( v_comment_symbol || ' ' ||
                          'Startup date and time : ' || to_char( startup_time, 'Day DD-MON-YYYY HH24:MI:SS')
                                                     || '  ( uptime = ' || trunc(uptime) || ' days, ' 
                                                     || uptime_hours || ' hours and '
                                                     || uptime_minutes || ' minutes )');
  end if;
  if ( instr( '&2', 'C' ) > 0 ) then
    dbms_output.put_line( v_comment_symbol || ' ' ||
                          'Current date and time : ' || to_char( SYSDATE, 'Day DD-MON-YYYY HH24:MI:SS' ) );
  end if;

  if ( &1 > 0 ) and (( instr( '&2', 'C' ) > 0 ) or ( instr( '&2', 'S' ) > 0 )) then 
    dbms_output.put_line( rpad( '-', &1, '-' ) ); 
  end if;

end;
/