Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.
Moderators: chulett , rschirm , roy
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Tue Apr 17, 2007 9:47 am
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.
DeepakCorning
Premium Member
Posts: 503 Joined: Wed Jun 29, 2005 8:14 am
Post
by DeepakCorning » Tue Apr 17, 2007 9:49 am
Is the output link to an IPC stage?
Also whats is definition of the column in the stage?
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Tue Apr 17, 2007 9:57 am
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
Last edited by
mystuff on Tue Apr 17, 2007 10:02 am, edited 1 time in total.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Tue Apr 17, 2007 10:02 am
Are you letting the stage generate the sql?
-craig
"You can never have too many knives" -- Logan Nine Fingers
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Tue Apr 17, 2007 10:06 am
No its self written SQL, which I used it in SQL Plus and it works fine there
DeepakCorning
Premium Member
Posts: 503 Joined: Wed Jun 29, 2005 8:14 am
Post
by DeepakCorning » Tue Apr 17, 2007 10:06 am
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.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Tue Apr 17, 2007 10:13 am
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Tue Apr 17, 2007 10:16 am
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
Last edited by
mystuff on Tue May 26, 2009 8:28 am, edited 1 time in total.
us1aslam1us
Charter Member
Posts: 822 Joined: Sat Sep 17, 2005 5:25 pm
Location: USA
Post
by us1aslam1us » Tue Apr 17, 2007 10:31 am
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.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
mystuff
Premium Member
Posts: 200 Joined: Wed Apr 11, 2007 2:06 pm
Post
by mystuff » Tue Apr 17, 2007 10:38 am
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.
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Tue Apr 17, 2007 10:55 am
As Craig requested earlier, please post your complete SQL.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Tue Apr 17, 2007 10:57 am
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.
Last edited by
chulett on Tue Apr 17, 2007 11:13 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
us1aslam1us
Charter Member
Posts: 822 Joined: Sat Sep 17, 2005 5:25 pm
Location: USA
Post
by us1aslam1us » Tue Apr 17, 2007 10:59 am
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.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Tue Apr 17, 2007 11:03 am
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)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Tue Apr 17, 2007 11:15 am
Doh! Thanks, going too fast today - I've corrected my previous post.
TO_CHAR() on select sql, TO_DATE() on 'update' sql.
-craig
"You can never have too many knives" -- Logan Nine Fingers