Alter session bug

This applies to the following setup:
 The Oracle bug number is 4904743 (metalink note 352513.1).
This bug causes the command 'alter session set nls_date_format ...' to have no effect in Pro*C if dynamic SQL is used.

This is exactly what happens in DBAComp. After connecting, the date format is set to a format that includes the date and the time portions. Due to this bug this command is ignored and the date format not set correctly.

There are 2 effects of this:
  1. All date columns are displayed without the time portion
  2. AWR and Statspack reporting does not work correctly
Workaround:
The workaround is to use an anonymous PL/SQL block using the 'execute immediate' command to run the 'alter session'. This way the command is no longer ignored.
Since all SQL commands executed in DBAComp come from the version respective dbacomp.x.y.sql file, the workaround must be applied to dbacomp.9.2.sql. To do this you have 2 possibilities:

1. Either look for the 'alter_session' SQL and replace it by the following lines:

alter_session:    declare \
                            fmt varchar2(30) := 'dd-mm-yy hh24:mi:ss'; \
                            begin  \
                                /* Both formats must be identical so that to_date(<timestamp>) \
                                   does not generate an ora-1830 */ \
                                execute immediate 'alter session set NLS_DATE_FORMAT='''||fmt||''''; \
                                execute immediate 'alter session set NLS_TIMESTAMP_FORMAT='''||fmt||''''; \
                            end;

2. Or download the modified dbacomp.9.2.sql which incorporates the above change. Replace the original file with this modified one.

 To test, restart DBAComp and look for a date column. If is is formatted as it should then your change was a success.