Skip to main content
Announcements
A fresh, new look for the Data Integration & Quality forums and navigation! Read more about what's changed.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:MI 0683p000009M9p6.pngS.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 0683p000009M9p6.pngS.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 ?
Labels (2)
4 Replies
Anonymous
Not applicable
Author

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.
Anonymous
Not applicable
Author

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.
Anonymous
Not applicable
Author

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

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 0683p000009M9p6.pngS.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