Page 1 of 2
sql not working in datastage
Posted: Tue Apr 17, 2007 9:47 am
by mystuff
Hi,
I am getting this error during extraction from oracle plugin
Code: Select all
ORA-01801: date format is too long for internal buffer
When I run the same query in SQL Plus, I don't get any error. But I get an error when I run the same query through datastage. I have changed the column definitions plenty of times, but it doesnt effect anything.
Re: sql not working in datastage
Posted: Tue Apr 17, 2007 9:49 am
by DeepakCorning
Is the output link to an IPC stage?
Also whats is definition of the column in the stage?
Posted: Tue Apr 17, 2007 9:57 am
by mystuff
I have
Code: Select all
Oracle Plugin -> Transformer -> Sequential File
I have tried the column definitions Varchar (38 ), TimeStamp (38 )
The fields which are causing problem are of type DATE in oracle
Posted: Tue Apr 17, 2007 10:02 am
by chulett
Are you letting the stage generate the sql?
Posted: Tue Apr 17, 2007 10:06 am
by mystuff
No its self written SQL, which I used it in SQL Plus and it works fine there
Posted: Tue Apr 17, 2007 10:06 am
by DeepakCorning
If you have the Generated SQL On then the generated SQL will be converting this Date Column to a char type (TO_CHAR) (verify this) , and if not then you will have to manually do this as I am not sure Datastage will handle the Date time stamp as it is.
Posted: Tue Apr 17, 2007 10:13 am
by chulett
mystuff wrote:No its self written SQL, which I used it in SQL Plus and it works fine there
Means nothing. Your SQL is the culprit here and you'd not be having an issue if the stage generated it for you.
If this is a DATE field in Oracle, DataStage can handle it just fine when declared as either a Date or a Timestamp. I prefer the latter.
Post your SQL.
Posted: Tue Apr 17, 2007 10:16 am
by mystuff
I have just now used varchar (38 ) in column definition
It is working when I use
Code: Select all
select
trim(date1),
trim(date2)
from table
These fields have a maximum length of 19 in oracle. The SQL is not working when I am removing the trim function.
Thanks
Posted: Tue Apr 17, 2007 10:31 am
by us1aslam1us
What error message did you got when you removed that trim? It seems you are having spaces in that field and the length seems to be more than 38.
Posted: Tue Apr 17, 2007 10:38 am
by mystuff
I get the following error, when I remove the trim function
Code: Select all
ORA-01801: date format is too long for internal buffer
I checked lengths of these fields they were both 19 and of datatype DATE in oracle.
Posted: Tue Apr 17, 2007 10:55 am
by DSguru2B
As Craig requested earlier, please post your complete SQL.
Posted: Tue Apr 17, 2007 10:57 am
by chulett
Why not take
explicit control of the field rather than make
assumptions about the format you'll get the data in? The generated SQL ensures this by wrapping a TO_CHAR() function around the field - why not do the same? Then you'll
know exactly what you'll get, regardless of instance or NLS_DATE setting.
Code: Select all
select to_char(date1,'YYYY-MM-DD') from table
select to_char(date1,'YYYY-MM-DD HH24:MI:SS') from table
Two examples.
Posted: Tue Apr 17, 2007 10:59 am
by us1aslam1us
And also some sample data, It seems you are having Invalid dates in your data.
Code: Select all
ORA-01801: date format is too long for internal buffer
Cause: The date format string was too long to process. This should occur only if several long literals are specified as part of a date.
Action: Remove long literals from the date format string.
Posted: Tue Apr 17, 2007 11:03 am
by DSguru2B
chulett wrote:
select to_date(date1,'YYYY-MM-DD') from table
select to_date(date1,'YYYY-MM-DD HH24:MI:SS') from table
Two examples.
You mean select
to_char 
(if its a date column)
Posted: Tue Apr 17, 2007 11:15 am
by chulett
Doh! Thanks, going too fast today - I've corrected my previous post.
TO_CHAR() on select sql, TO_DATE() on 'update' sql.