Oracle DB Errors

[Trav]

I've configured OTRS 3.0.11 on CentOS 6.0 / Perl 5.10.1 and an Oracle 11g (11.2.0.10) back-end database. I've followed the documentation and set the appropriate environment parameters. I can use the application but I'm seeing multiple errors in the OTRS error log (see below). The support assessment tool verified that the NLS_DATE_FORMAT is set correctly but is also displaying "Check NLS_DATE_FORMAT by using SELECT statement. 07-DEC-11 is not the right format 'yyyy-mm-dd hh:mm::ss' (please check $ENV{NLS_DATE_FORMAT}).".

[Wed Dec 7 08:32:49 2011] -e: Use of uninitialized value $CountClosed in numeric gt (>) at /opt/otrs//Kernel/Output/HTML/DashboardTicketStatsGeneric.pm line 132.

[Wed Dec 7 08:32:49 2011] -e: DBD::Oracle::st execute failed: ORA-01861: literal does not match format string (DBD ERROR: error possibly near <*> indicator at char 211 in 'SELECT COUNT(DISTINCT(st.id)) FROM ticket st, queue sq , ticket_history th WHERE sq.id = st.queue_id AND st.id = th.ticket_id AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time <= <*>'2011-12-07 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time >= '2011-12-07 00:00:00'') [for Statement "SELECT COUNT(DISTINCT(st.id)) FROM ticket st, queue sq , ticket_history th WHERE sq.id = st.queue_id AND st.id = th.ticket_id AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time <= '2011-12-07 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time >= '2011-12-07 00:00:00'"] at /opt/otrs//Kernel/System/DB.pm line 553.
[Wed Dec 7 08:32:49 2011] -e: Use of uninitialized value in concatenation (.) or string at /opt/otrs//Kernel/System/Log.pm line 161.
ERROR: OTRS-CGI-10 Perl: 5.10.1 OS: linux Time: Wed Dec 7 08:32:49 2011

At the time of database creation, the default nls_date_format for Oracle is set to MMM/DD/YYYY. I modified this setting hoping to resolve some of these errors and set the value to "YYYY-MM-DD" but the application generates a different set of ORA-01830 errors in the log:

[Wed Dec 7 09:40:38 2011] -e: Use of uninitialized value $CountClosed in numeric gt (>) at /opt/otrs//Kernel/Output/HTML/DashboardTicketStatsGeneric.pm line 132.

[Wed Dec 7 09:40:38 2011] -e: DBD::Oracle::st execute failed: ORA-01830: date format picture ends before converting entire input string (DBD ERROR: error possibly near <*> indicator at char 211 in 'SELECT COUNT(DISTINCT(st.id)) FROM ticket st, queue sq , ticket_history th WHERE sq.id = st.queue_id AND st.id = th.ticket_id AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time <= <*>'2011-12-06 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time >= '2011-12-06 00:00:00'') [for Statement "SELECT COUNT(DISTINCT(st.id)) FROM ticket st, queue sq , ticket_history th WHERE sq.id = st.queue_id AND st.id = th.ticket_id AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time <= '2011-12-06 23:59:59' AND th.history_type_id IN (1, 27) AND th.state_id IN (2, 3) AND th.create_time >= '2011-12-06 00:00:00'"] at /opt/otrs//Kernel/System/DB.pm line 553.
[Wed Dec 7 09:40:38 2011] -e: Use of uninitialized value in concatenation (.) or string at /opt/otrs//Kernel/System/Log.pm line 161.
ERROR: OTRS-CGI-10 Perl: 5.10.1 OS: linux Time: Wed Dec 7 09:40:38 2011

Any thoughts as to why the application isn't interpreting the proper date format when I click on the dashboard or select a ticket?

[crythias]

You might check this http://forums.otrs.org/viewtopic.php?f=81&t=5946#p25808

I don't have Oracle database, so my help is limited to what other people have posted.

[Trav]

Thanks for the feedback. I have the following lines in my Config.pm as they align with the "AL32UTF8" character set that I specified when I created the database.

$ENV{ORACLE_HOME} = '/usr/lib/oracle/11.2/client/lib/';
$ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
$ENV{NLS_LANG} = "american_america.al32utf8";

I've also enabled the following in /etc/httpd/conf.d/otrs.conf

SetEnv ORACLE_HOME /usr/lib/oracle/11.2/client/lib/
SetEnv ORACLE_SID otrs
SetEnv NLS_LANG american_america.al32utf8
SetEnv NLS_DATE_FORMAT 'YYYY-MM-DD HH24:MI:SS'

[Trav]

Setting the "NLS_DATE_FORMAT" AND "NLS_LANG" in /opt/otrs/scripts/apache2-perl-startup.pl fixed the problem. I'm no longer seeing "Invalid Date" messages in the error log and the support assessment returns clean. I'm not sure why these values need to placed in this directory too as they are already defined in the /etc/httpd/conf.d/otrs.conf file.

/opt/otrs/scripts/apache2-perl-startup.pl

$ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
$ENV{NLS_LANG} = "american_america.al32utf8";

[VMania]

Hi there,

as indicated by Trav, inserting the statement resolved misbehaviour. With this the "AdminSupport" page now shows green light.
# ORA-01861: literal does not match format string
$ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';

Obviously the "Config.pm" is not run at the right moment so that the settings don't load into Apache's runtime.

• Is it a bug or only a lack of explanation in the "otrs_admin" manual?
  Will the file "apache2-perl-startup.pl" be replaced during an update process?

However thanks a lot to Trav posting your solution. At least I can use it as a workaround until I do understand the context.

[VMania]

You might check this http://forums.otrs.org/viewtopic.php?f=81&t=5946#p25808

I don't have Oracle database, so my help is limited to what other people have posted.

Hello crythias,

could you please tell us, if /otrs/scripts/apache2-perl-startup.pl is the right place to insert the redundant $ENV{NLS_DATE_FORMAT} setting coming from /otrs/kernel/Config.pm file? May be this file will be replace by a default coming with a new OTRS release.

Many thanks in advance

[lflavio]

Hi VMania,

You can put environment variables in Kernel/Config.pm as long as you put them outside the Load function and inside a BEGIN block.
This way...

Code: Select all

BEGIN {
    $ENV{ORACLE_HOME} = '/usr/lib/oracle/12.1/client64';
    $ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
    $ENV{NLS_LANG} = "american_america.utf8";
}

Regards,
Luiz Flávio