OS:
cat /etc/centos-release
CentOS release 6.3 (Final)

ZABBIX:
zabbix_server -V
Zabbix server v2.0.3 (revision 30485) (03 October 2012)
Compilation time: Oct 22 2012 21:24:20

ORACLE:
select * from V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL*CLIENT (istant client):

rpm -qa | grep oracle-instant
oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64
oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64
oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64
oracle-instantclient11.2-odbc-11.2.0.3.0-1.x86_64
oracle-instantclient11.2-jdbc-11.2.0.3.0-1.x86_64

sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 9 16:35:28 2013

NSL_LANG:

PARAMETER VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

PARAMETER VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

I think this a zabbix realted bug:

I have found a note in oracle MOS (AKA metalink):

ORA-01425 when using 'like' or 'escape' on NVARCHAR2 column [ID 1470516.1]

Solution

Change the query on the NVARCHAR2 column to Use TO_CHAR or LIKEC
Please use any of the following solutions :
select * from t1 where to_char(col2) like '%\?“%' escape '\';
or
select * from t1 where col2 likec '%\?“%' escape '\';

I think the problem is inside zabbix server not frontend. I don't know where

I have started a find against all zabbix source. Only related string with "escape" are:

./libs/zbxdbhigh/db.c:1142: *           ... LIKE 'a!_b!%c\\d\'e!!f' ESCAPE '!' (MySQL, PostgreSQL)       *
./libs/zbxdbhigh/db.c:1143: *           ... LIKE 'a!_b!%c\d''e!!f' ESCAPE '!' (IBM DB2, Oracle, SQLite3) *

based on metalink note 1470516.1 above, the strings must be:

LIKE 'a!_b!%c\d''e!!f' ESCAPE '\!'

I have tested the 3 workaround and no one works ... it's a blocker issue and i can't change status of bug

Actually the problem comes from this place (src/libs/zbxdbhigh/db.c:1987)

result = DBselect(
	"select host"
	" from hosts"
	" where host like '%s%%' escape '%c'"
		ZBX_SQL_NODE,
	host_name_sample_esc, ZBX_SQL_LIKE_ESCAPE_CHAR, DBand_node_local("hostid"));

Changing the code you listed above won't affect anything. But the problem might be
related to nvarchar2-varchar2 conversions. I could not reproduce the problem on
our development oracle server, but that might be because we have both -
NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET set to UTF8.

You environment data appears to be truncated, could you please check what charsets
are used in you database ?
(select * from v$nls_parameters where parameter like '%CHARACTERSET%'

Based on your query, this is the output:

SQL>  select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET                                                 AL32UTF8
NLS_NCHAR_CHARACTERSET                                           AL16UTF16

That explains. Apparently there are some conversion problems with like operator and nvarchar2/varchar2 types.

But, Zabbix requires UTF8 character set (see Oracle section in http://www.zabbix.com/documentation/2.0/manual/appendix/install/db_scripts#scripts), so I'm closing this one.

Ok... So what is the right output i need to see with this query?

select * from v$nls_parameters where parameter like '%CHARACTERSET%';

This for my DBAs ... Thank you!

NLS_NCHAR_CHARACTERSET should also be AL32UTF8