Oracle Query with double quotes got ORA-00972 identifier is too long

One Star

Oracle Query with double quotes got ORA-00972 identifier is too long

Hi All
Talend 5.3.1 , Oracle 11
Query for check : Select to_timestamp ( '2013-05-13T03:52:30.000Z' , 'YYYY-MM-DD"T"HH24:MISmiley FrustratedS.FF3"Z"') from dual ;
This query works ok in SQL developer :
select lastmodifieddate , a.*
from sf_accounts a
where to_timestamp ( lastmodifieddate , 'YYYY-MM-DD"T"HH24:MISmiley FrustratedS.FF3"Z"') > sysdate
in Talend in ToracleInput I got :
ORA-00972 identifier is too long
Cause: An identifier with more than 30 characters was specified.
( w/o where caluse all works ok )
I tried to use instead on single " , 3" , 4" (for me seems 4" is correct """" )
but still get ORA-00972

any ideas how to resolve it ?
One Star

Re: Oracle Query with double quotes got ORA-00972 identifier is too long

I find that quotes need to be escaped. The Java escape character is \ From your post it appears that you are trying to use Oracle style escaping rather than Java
If you are comfortable looking at the generated Java code from your job then it is easier to understand what is occurring.
Five Stars

Re: Oracle Query with double quotes got ORA-00972 identifier is too long

I'd also recommend that you don't use "a.*" and name your columns. It will save you a lot of pain later, when columns don't come out in the order you expect.
One Star

Re: Oracle Query with double quotes got ORA-00972 identifier is too long

I need " in oracel SQL
Query for check : Select to_timestamp ( '2013-05-13T03:52:30.000Z' , 'YYYY-MM-DD"T"HH24:MISmiley FrustratedS.FF3"Z"') from dual ;
just running it - and then got why
Workaround I found is :
context.where1 = "to_timestamp ( lastmodifieddate , 'YYYY-MM-DD~T~HH24:MISmiley FrustratedS.FF3~Z~') > sysdate" ;
context.where1 = context.where1.replace ( '~' , '"' ) ;
and in troracleInput :
" sql text " + context.where1
Not perfect but workable
One Star

Re: Oracle Query with double quotes got ORA-00972 identifier is too long

I find that quotes need to be escaped. The Java escape character is \ From your post it appears that you are trying to use Oracle style escaping rather than Java

Thanks - You are RIGHT !!
Need to put \ before " :
where to_timestamp ( lastmodifieddate , 'YYYY-MM-DD\"T\"HH24:MISmiley FrustratedS.FF3\"Z\"') > sysdate

here is simple test for
String a="1234 \" $%^ \"" ;
System.out.println(a);
PS Goood thing in MSDN forum that you can mark other post as usefull - I thought Notify post is same feature
but seems not