topic Re: Oracle Query with double quotes got ORA-00972 identifier is too long in Talend Studio

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

Anonymous — Wed, 21 Aug 2013 14:41:33 GMT

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:MI S.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:MI S.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 ?

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

Anonymous — Wed, 21 Aug 2013 19:03:30 GMT

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.

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

Anonymous — Wed, 21 Aug 2013 21:30:24 GMT

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.

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

Anonymous — Thu, 22 Aug 2013 13:22:22 GMT

I need " in oracel SQL
Query for check : Select to_timestamp ( '2013-05-13T03:52:30.000Z' , 'YYYY-MM-DD"T"HH24:MI S.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:MI S.FF3~Z~') > sysdate" ;
context.where1 = context.where1.replace ( '~' , '"' ) ;
and in troracleInput :
" sql text " + context.where1
Not perfect but workable

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

Anonymous — Thu, 22 Aug 2013 13:34:19 GMT

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:MI S.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