Datastage/Oliver team,
I am having a problem with a visual job I have designed. It is not a complicated job but I am having trouble inserting rows into our Oracle table. The datastage job seems to convert some of the dates to NULL prior to insert (the dates are used for a unique key in the table) and therefore the job complains that it cant insert the row into the table.
Prior to the DB error code, the log states the following: ===========================
KCImport.Tcom_LOG: At row 1, link "Log_Info", while processing column "DATA_DATE" Value treated as NULL Attempt to convert String value "11-FEB-01" to Date type unsuccessful ===========================
I have debbuged the job and the Iconv/Oconv functions seem to be converting the date into an acceptable Oracle format.
Why would datastage say it is treating a value as NULL?
Regards,
David Nemirovsky.
IT Consultant (Contracted To Telstra)
Black Diamond
T e c h n o l o g i e s
Web: http://www.bdt.com.au
Level 5, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: mailto:david.nemirovsky@team.telstra.com
Telephone: (03) 8696 - 5983
Facsimile: (03) 9682 - 7074
Mobile: 04111 - 88 - 44 - 1
Inserting dates into Oracle
Has this gotta be the number one Oracle/DataStage question or what? Perhaps Ray can get approval to publish the appropriate (small) portion of the training course to the list server?
What you are trying to do seems reasonable, it just isnt the way DataStage does it. DataStage will convert it to the correct format for Oracle. What you have to do is use the Timestamp data type in any links that match dates in Oracle, then convert your data to match the Timestamp format. In Oracle terms, this is YYYY-MM-DD HH24:MI:SS
To quote a previous message,
"...read pages 19 and 21 of the DataStage Oracle 8 Call Interface Plug-In technical bulletin which you should find on your DataStage CD in the "Packages" folder. This explains how DataStage handles dates in Oracle."
If your data source provides dates, then typically, you would use Iconv and Oconv to get the date into YYYY-MM-DD format then append " 00:00:00" to the end.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Nemirovsky, David [mailto:David.Nemirovsky@team.telstra.com]
Sent: Wednesday, 23 May 2001 2:59 PM
To: informix-datastage@oliver.com
Subject: Inserting dates into Oracle
Datastage/Oliver team,
I am having a problem with a visual job I have designed. It is not a complicated job but I am having trouble inserting rows into our Oracle table. The datastage job seems to convert some of the dates to NULL prior to insert (the dates are used for a unique key in the table) and therefore the job complains that it cant insert the row into the table.
Prior to the DB error code, the log states the following: ===========================
KCImport.Tcom_LOG: At row 1, link "Log_Info", while processing column "DATA_DATE" Value treated as NULL Attempt to convert String value "11-FEB-01" to Date type unsuccessful ===========================
I have debbuged the job and the Iconv/Oconv functions seem to be converting the date into an acceptable Oracle format.
Why would datastage say it is treating a value as NULL?
Regards,
David Nemirovsky.
IT Consultant (Contracted To Telstra)
Black Diamond
T e c h n o l o g i e s
Web: http://www.bdt.com.au
Level 5, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: mailto:david.nemirovsky@team.telstra.com
Telephone: (03) 8696 - 5983
Facsimile: (03) 9682 - 7074
Mobile: 04111 - 88 - 44 - 1
What you are trying to do seems reasonable, it just isnt the way DataStage does it. DataStage will convert it to the correct format for Oracle. What you have to do is use the Timestamp data type in any links that match dates in Oracle, then convert your data to match the Timestamp format. In Oracle terms, this is YYYY-MM-DD HH24:MI:SS
To quote a previous message,
"...read pages 19 and 21 of the DataStage Oracle 8 Call Interface Plug-In technical bulletin which you should find on your DataStage CD in the "Packages" folder. This explains how DataStage handles dates in Oracle."
If your data source provides dates, then typically, you would use Iconv and Oconv to get the date into YYYY-MM-DD format then append " 00:00:00" to the end.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Nemirovsky, David [mailto:David.Nemirovsky@team.telstra.com]
Sent: Wednesday, 23 May 2001 2:59 PM
To: informix-datastage@oliver.com
Subject: Inserting dates into Oracle
Datastage/Oliver team,
I am having a problem with a visual job I have designed. It is not a complicated job but I am having trouble inserting rows into our Oracle table. The datastage job seems to convert some of the dates to NULL prior to insert (the dates are used for a unique key in the table) and therefore the job complains that it cant insert the row into the table.
Prior to the DB error code, the log states the following: ===========================
KCImport.Tcom_LOG: At row 1, link "Log_Info", while processing column "DATA_DATE" Value treated as NULL Attempt to convert String value "11-FEB-01" to Date type unsuccessful ===========================
I have debbuged the job and the Iconv/Oconv functions seem to be converting the date into an acceptable Oracle format.
Why would datastage say it is treating a value as NULL?
Regards,
David Nemirovsky.
IT Consultant (Contracted To Telstra)
Black Diamond
T e c h n o l o g i e s
Web: http://www.bdt.com.au
Level 5, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: mailto:david.nemirovsky@team.telstra.com
Telephone: (03) 8696 - 5983
Facsimile: (03) 9682 - 7074
Mobile: 04111 - 88 - 44 - 1
Of course this worked... Thanks Dave!
Regards,
David Nemirovsky.
IT Consultant (Contracted To Telstra)
Black Diamond
T e c h n o l o g i e s
Web: http://www.bdt.com.au
Level 5, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: mailto:david.nemirovsky@team.telstra.com
Telephone: (03) 8696 - 5983
Facsimile: (03) 9682 - 7074
Mobile: 04111 - 88 - 44 - 1
> -----Original Message-----
> From: David Barham [SMTP:david@barham.hm]
> Sent: Wednesday, 23 May 2001 3:19 pm
> To: informix-datastage@oliver.com
> Subject: RE: Inserting dates into Oracle
>
> Has this gotta be the number one Oracle/DataStage question or what?
> Perhaps Ray can get approval to publish the appropriate (small)
> portion of the training course to the list server?
>
>
> What you are trying to do seems reasonable, it just isnt the way
> DataStage does it. DataStage will convert it to the correct format
> for Oracle. What
> you have to do is use the Timestamp data type in any links that match
> dates
> in Oracle, then convert your data to match the Timestamp format. In
> Oracle
> terms, this is YYYY-MM-DD HH24:MI:SS
>
> To quote a previous message,
>
> "...read pages 19 and 21 of the DataStage Oracle 8 Call Interface
> Plug-In technical bulletin which you should find on your DataStage CD
> in the "Packages" folder. This explains how DataStage handles dates
> in Oracle."
>
> If your data source provides dates, then typically, you would use
> Iconv and Oconv to get the date into YYYY-MM-DD format then append "
> 00:00:00" to the
> end.
>
> David Barham
> Information Technology Consultant
> InformAtect Pty Ltd
> Brisbane, Australia
>
> e-mail: david@barham.hm
>
> -----Original Message-----
> From: Nemirovsky, David [mailto:David.Nemirovsky@team.telstra.com]
> Sent: Wednesday, 23 May 2001 2:59 PM
> To: informix-datastage@oliver.com
> Subject: Inserting dates into Oracle
>
> Datastage/Oliver team,
>
> I am having a problem with a visual job I have designed. It is not a
> complicated job but I am having trouble inserting rows into our Oracle
> table. The datastage job seems to convert some of the dates to NULL
> prior to insert (the dates are used for a unique key in the table) and
> therefore the job complains that it cant insert the row into the
> table.
>
> Prior to the DB error code, the log states the following:
> ===========================
> KCImport.Tcom_LOG: At row 1, link "Log_Info", while processing column
> "DATA_DATE" Value treated as NULL
> Attempt to convert String value "11-FEB-01" to Date type unsuccessful
> ===========================
>
> I have debbuged the job and the Iconv/Oconv functions seem to be
> converting the date into an acceptable Oracle format.
>
> Why would datastage say it is treating a value as NULL?
>
> Regards,
> David Nemirovsky.
> IT Consultant (Contracted To Telstra)
> Black Diamond
> T e c h n o l o g i e s
> Web: http://www.bdt.com.au
> Level 5, 1 Southbank Boulevard,
> Southbank, Victoria 3006
> E-mail: mailto:david.nemirovsky@team.telstra.com
> Telephone: (03) 8696 - 5983
> Facsimile: (03) 9682 - 7074
> Mobile: 04111 - 88 - 44 - 1
Regards,
David Nemirovsky.
IT Consultant (Contracted To Telstra)
Black Diamond
T e c h n o l o g i e s
Web: http://www.bdt.com.au
Level 5, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: mailto:david.nemirovsky@team.telstra.com
Telephone: (03) 8696 - 5983
Facsimile: (03) 9682 - 7074
Mobile: 04111 - 88 - 44 - 1
> -----Original Message-----
> From: David Barham [SMTP:david@barham.hm]
> Sent: Wednesday, 23 May 2001 3:19 pm
> To: informix-datastage@oliver.com
> Subject: RE: Inserting dates into Oracle
>
> Has this gotta be the number one Oracle/DataStage question or what?
> Perhaps Ray can get approval to publish the appropriate (small)
> portion of the training course to the list server?
>
>
> What you are trying to do seems reasonable, it just isnt the way
> DataStage does it. DataStage will convert it to the correct format
> for Oracle. What
> you have to do is use the Timestamp data type in any links that match
> dates
> in Oracle, then convert your data to match the Timestamp format. In
> Oracle
> terms, this is YYYY-MM-DD HH24:MI:SS
>
> To quote a previous message,
>
> "...read pages 19 and 21 of the DataStage Oracle 8 Call Interface
> Plug-In technical bulletin which you should find on your DataStage CD
> in the "Packages" folder. This explains how DataStage handles dates
> in Oracle."
>
> If your data source provides dates, then typically, you would use
> Iconv and Oconv to get the date into YYYY-MM-DD format then append "
> 00:00:00" to the
> end.
>
> David Barham
> Information Technology Consultant
> InformAtect Pty Ltd
> Brisbane, Australia
>
> e-mail: david@barham.hm
>
> -----Original Message-----
> From: Nemirovsky, David [mailto:David.Nemirovsky@team.telstra.com]
> Sent: Wednesday, 23 May 2001 2:59 PM
> To: informix-datastage@oliver.com
> Subject: Inserting dates into Oracle
>
> Datastage/Oliver team,
>
> I am having a problem with a visual job I have designed. It is not a
> complicated job but I am having trouble inserting rows into our Oracle
> table. The datastage job seems to convert some of the dates to NULL
> prior to insert (the dates are used for a unique key in the table) and
> therefore the job complains that it cant insert the row into the
> table.
>
> Prior to the DB error code, the log states the following:
> ===========================
> KCImport.Tcom_LOG: At row 1, link "Log_Info", while processing column
> "DATA_DATE" Value treated as NULL
> Attempt to convert String value "11-FEB-01" to Date type unsuccessful
> ===========================
>
> I have debbuged the job and the Iconv/Oconv functions seem to be
> converting the date into an acceptable Oracle format.
>
> Why would datastage say it is treating a value as NULL?
>
> Regards,
> David Nemirovsky.
> IT Consultant (Contracted To Telstra)
> Black Diamond
> T e c h n o l o g i e s
> Web: http://www.bdt.com.au
> Level 5, 1 Southbank Boulevard,
> Southbank, Victoria 3006
> E-mail: mailto:david.nemirovsky@team.telstra.com
> Telephone: (03) 8696 - 5983
> Facsimile: (03) 9682 - 7074
> Mobile: 04111 - 88 - 44 - 1