Friday, October 4, 2013

E-mail special characters and SQL

Looking at the system post “network issue” I found that the “Journal Import Health Check” (yes, that one) alert has been erroring after the alert was changed and looking back it’s been alerting for several days.  Here is the relevant request log text:

Performing alert "Journal Import Health Check" (ROUTINE=alepaa) (FILE=7175946/alr/lib/alepea.c)

SQL error ORA-00911: invalid character
occurred while executing the user-defined SQL statement for this alert (STMT=select request_id,
decode(status_code, 'E', 'ERROR', 'X', 'TERMINATED', 'D', 'CANCELLED', 'I', 'SCHEDULED/NO MANAGER', 'NOT LISTED') as "STATUS"
from fnd_concurrent_requests
where concurrent_program_id = 20215
and status_code not in (
'C' --Normal
, 'G' --Warning
, 'Q' --Standby
, 'R' --Running
and (requested_start_date > sysdate – 1/24 OR actual_completion_date > sysdate – 1/24))

Looking at the SQL in the form for Oracle Alerts didn’t make me think there was any issue, and the alert showed:

But when you copy and paste this into a SQL development tool it showed:

With ASCII characters instead of the minus sign we were looking for, and copying this back into the e-mail application shows:

It is hard to avoid something like this happening if we’re copying from e-mail, but this reinforces that after we change an Oracle Alert, we should utilize the Verify button to get it to run the SQL and make sure everything is correct:

No comments:

Post a Comment