
Anonymous
Not applicable
2013-08-21
10:41 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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 ?
Talend 5.3.1 , Oracle 11
Query for check : Select to_timestamp ( '2013-05-13T03:52:30.000Z' , 'YYYY-MM-DD"T"HH24:MI
This query works ok in SQL developer :
select lastmodifieddate , a.*
from sf_accounts a
where to_timestamp ( lastmodifieddate , 'YYYY-MM-DD"T"HH24:MI
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 ?
365 Views
4 Replies

Anonymous
Not applicable
2013-08-21
03:03 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
If you are comfortable looking at the generated Java code from your job then it is easier to understand what is occurring.
365 Views

Anonymous
Not applicable
2013-08-21
05:30 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
365 Views

Anonymous
Not applicable
2013-08-22
09:22 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Query for check : Select to_timestamp ( '2013-05-13T03:52:30.000Z' , 'YYYY-MM-DD"T"HH24:MI
just running it - and then got why
Workaround I found is :
context.where1 = "to_timestamp ( lastmodifieddate , 'YYYY-MM-DD~T~HH24:MI
context.where1 = context.where1.replace ( '~' , '"' ) ;
and in troracleInput :
" sql text " + context.where1
Not perfect but workable
365 Views

Anonymous
Not applicable
2013-08-22
09:34 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
365 Views
