DSXchange

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;

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)?

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

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
)

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

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.

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

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

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.

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

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.

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!!!!

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.

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.

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!!!