insert a date field with ODBC Enterprise stage
Moderators: chulett, rschirm, roy
insert a date field with ODBC Enterprise stage
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;
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;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Three questions, one answer. Your problem is at best 1/3 solved.bolingo wrote:I use this oracle tableray.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)?
CREATE TABLE TBablexx
(
T_ID NUMBER(8,2),
T_DATE DATE
)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
The date format is mm-dd-yyyyray.wurlod wrote:Three questions, one answer. Your problem is at best 1/3 solved.bolingo wrote:I use this oracle tableray.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)?
CREATE TABLE TBablexx
(
T_ID NUMBER(8,2),
T_DATE DATE
)
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
It is not user-defined SQL, the stage generate some for me;chulett wrote:And let me guess, user-defined sql? Or did the stage generate some for you? In either case, please post your sql.
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 not sql query; the main properties of my ODBC enterprise stage are: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.
* target
- table=TBablexx
- Write Method=Write
- Write Mode=Truncate
* Connection
- data source=AAAAA
- Password=*****
- User=ETLUSER
* Options
- Truncate Column Names=False
Thanks 4U
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Even if I use this format YYYY-MM-DD, I have the same error: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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I use ODBC instead of Oracle because I want to test the performance of these two stages.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.
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!!!