sql not working in datastage

[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.

[DeepakCorning]

Is the output link to an IPC stage?

Also whats is definition of the column in the stage?

[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

[chulett]

Are you letting the stage generate the sql?

[mystuff]

No its self written SQL, which I used it in SQL Plus and it works fine there

[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.

[chulett]

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.

[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

[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.

[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.

[DSguru2B]

As Craig requested earlier, please post your complete SQL.

[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.

[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. 

[DSguru2B]

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)

[chulett]

Doh! Thanks, going too fast today - I've corrected my previous post.

TO_CHAR() on select sql, TO_DATE() on 'update' sql.