insert a date field with ODBC Enterprise stage

bolingo · Post by **bolingo** » Thu Aug 21, 2008 2:55 pm

Hi all,

I am trying to insert data with a date field with ODBC Enterprise stage;
my job looks like:
sequencial file ------> tranformer -----> ODBC Enterprise
1,'2003-02-15'
2,'2001-12-02'

I have this error:
ODBC_Enterprise_102,0: [Oracle][ODBC]Numeric value out of range.
ODBC_Enterprise_102,0: Failure during execution of operator logic.
ODBC_Enterprise_102,0: Fatal Error: 0
ODBC_Enterprise_102,1: [Oracle][ODBC]Numeric value out of range.
node_node1: Player 2 terminated unexpectedly.
ODBC_Enterprise_102,1: Failure during execution of operator logic.
main_program: Step execution finished with status = FAILED.

Could I have any help to fix this issue;
Thanks a lot;

ray.wurlod · Post by **ray.wurlod** » Thu Aug 21, 2008 3:30 pm

What are the data types in the Oracle table? Is the date format correct for your Oracle instance? What is the SQL being executed (this is reported in the Oracle stage)?

chulett · Post by **chulett** » Thu Aug 21, 2008 4:02 pm

"Numeric value out of range" isn't what one would typically see with a DATE field but then I don't typically use ODBC.

bolingo · Post by **bolingo** » Thu Aug 21, 2008 4:07 pm

ray.wurlod wrote:What are the data types in the Oracle table? Is the date format correct for your Oracle instance? What is the SQL being executed (this is reported in the Oracle stage)?

I use this oracle table

CREATE TABLE TBablexx
(
T_ID NUMBER(8,2),
T_DATE DATE
)

chulett · Post by **chulett** » Thu Aug 21, 2008 4:20 pm

And let me guess, user-defined sql? Or did the stage generate some for you? In either case, please post your sql.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 21, 2008 6:40 pm

bolingo wrote:
ray.wurlod wrote:What are the data types in the Oracle table? Is the date format correct for your Oracle instance? What is the SQL being executed (this is reported in the Oracle stage)?
I use this oracle table

CREATE TABLE TBablexx
(
T_ID NUMBER(8,2),
T_DATE DATE
)

Three questions, one answer. Your problem is at best 1/3 solved.

bolingo · Post by **bolingo** » Fri Aug 22, 2008 8:14 am

ray.wurlod wrote:
bolingo wrote:
ray.wurlod wrote:What are the data types in the Oracle table? Is the date format correct for your Oracle instance? What is the SQL being executed (this is reported in the Oracle stage)?
I use this oracle table

CREATE TABLE TBablexx
(
T_ID NUMBER(8,2),
T_DATE DATE
)
Three questions, one answer. Your problem is at best 1/3 solved.

The date format is mm-dd-yyyy
The properties in ODBC enterprise stage are:
* target
- table=TBablexx
- Write Method=Write
- Write Mode=Truncate

The table TBablexx is already create in Oracle database;
Thanks

bolingo · Post by **bolingo** » Fri Aug 22, 2008 8:16 am

chulett wrote:And let me guess, user-defined sql? Or did the stage generate some for you? In either case, please post your sql.

It is not user-defined SQL, the stage generate some for me;

The date format is mm-dd-yyyy in oracle database

The properties in ODBC enterprise stage are:
* target
- table=TBablexx
- Write Method=Write
- Write Mode=Truncate

The table TBablexx is already create in Oracle database;
Thanks 4U

chulett · Post by **chulett** » Fri Aug 22, 2008 8:42 am

There is no 'date format' in an Oracle database, per se, unless you are attempting to match the NLS_DATE format. Please post the sql and clarify how the DATE column is defined in the job itself.

bolingo · Post by **bolingo** » Fri Aug 22, 2008 8:59 am

chulett wrote:There is no 'date format' in an Oracle database, per se, unless you are attempting to match the NLS_DATE format. Please post the sql and clarify how the DATE column is defined in the job itself.

There is not sql query; the main properties of my ODBC enterprise stage are:

* target
- table=TBablexx
- Write Method=Write
- Write Mode=Truncate

* Connection
- data source=AAAAA
- Password=*****
- User=ETLUSER

* Options
- Truncate Column Names=False
Thanks 4U

ray.wurlod · Post by **ray.wurlod** » Fri Aug 22, 2008 5:00 pm

Date format for ODBC must be YYYY-MM-DD

You are using an ODBC driver, and therefore must conform to the standards specified for ODBC.

bolingo · Post by **bolingo** » Mon Aug 25, 2008 8:29 am

ray.wurlod wrote:Date format for ODBC must be YYYY-MM-DD

You are using an ODBC driver, and therefore must conform to the standards specified for ODBC.

Even if I use this format YYYY-MM-DD, I have the same error:

ODBC_Enterprise_102,1: [Oracle][ODBC]Numeric value out of range.
ODBC_Enterprise_102,1: Failure during execution of operator logic.
ODBC_Enterprise_102,1: Fatal Error: 0
node_node2: Player 1 terminated unexpectedly.
main_program: Unexpected exit status 1
main_program: Step execution finished with status = FAILED.

Could someone help me please!!!!

chulett · Post by **chulett** » Mon Aug 25, 2008 9:56 am

Dude - people are trying to help you but you are making it difficult by not providing enough information. And bottom line is you need to learn how to help yourself, or at least help us help you.

You haven't mentioned the datatype of the Oracle DATE field in the job. You haven't mentioned the version of the Oracle database itself nor the Oracle client version installed on your DataStage server. Mismatches there can cause odd errors, including the one you noted. Let's start with that.

chulett · Post by **chulett** » Mon Aug 25, 2008 11:02 am

Why not use the Oracle stage for Oracle then? In other words, why screw with ODBC?

What happens if you make your field a varchar in the job and match the NLS_DATE format of the target database? If you don't know what that is, ask your DBA, they'll be able to tell you how it is set.

bolingo · Post by **bolingo** » Mon Aug 25, 2008 12:27 pm

chulett wrote:Why not use the Oracle stage for Oracle then? In other words, why screw with ODBC?

What happens if you make your field a varchar in the job and match the NLS_DATE format of the target database? If you don't know what that is, ask your DBA, they'll be able to tell you how it is set.

I use ODBC instead of Oracle because I want to test the performance of these two stages.
The default format date of the job is yyyy-mm-dd.
I thought about using varchar , but this solution cannot help me because I want to know the real time for loading date field in Oracle trough ODBC enterprise stage.
I know the NLS_DATE format of the target database, but how can I modify it?
Thanks for any help!!!