Fellow DS Users,
Just wondering how would I be able to load DATE+TIME values into my DW through the OCI 8 Stage.
I have got this working through the ODBC stage but unfortunately some of my tables have way too many columns for ODBC to handle, hence the need for OCI.
I would prefer to load data that looks like "YYYY-MM-DD HH24:MI" in my data file.
I would also prefer NOT to have to change my default NLS_DATE_FORMAT setting as it is going to affect a whole lot of other users. The error message I get from DS is that it cant convert the format given o a DateTime format.
-----------------
While we are here, how can I prevent the Oracle Bulk Loader from converting my blank character fields to NULLS when I really want to insert blanks or a space. My data will often have blank values for character fields (which is
OK) but the bulk loader insists on trying to convert these to NULL and my table schema is defined as having ALL fields defined as NOT NULL so it wont load !
eg, Data file looks like this after processing through DS (sqlldr converts
last field to NULL) ... "Field1","Field2",""
When the data looks like this "Field1","Field2"," " (Note the blank in the last field), it will load blanks into the third field.
P.S. We are using DS 4.0.1 on Win NT.
Help !!
Basil Privitera
DS Consultant
Altis Consulting
*******************Confidentiality and Privilege Notice*******************
This email is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply email. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you.
Loading Time Values Using OCI Stage
Basil,
When using the ORAOCI8 stage, use a data type of TIMESTAMP for ALL date fields.
If you are using generated queries (which hopefully is most of the time), Then there is nothing more for you to do.
However, if you are using a user defined query, there is one more thing you need to know. When reading from or writing to Oracle, treat the "timestamp" field as a character field. That is, when writing to Oracle, you need a construct like, for example, to_date(:3,YYYY-MM-DD HH24:MI:SS). :3 refers to the 3rd field in the link (in this case). When reading from Oracle, you will need to_char(date_field, YYYY-MM-DD HH24:MI:SS).
On a reference link, with a user defined query, you may need both, especially if one of your keys is a date. Also remember that on a reference link, :1, :2, :3 etc refers to the key fields only.
You do not need to change NLS_DATE_FORMAT.
The error you are getting sounds to me like you are using DATE type in DataStage instead of TIMESTAMP.
At Anglo, we use Oracle more than any other data source or target. Once you understand date handling between Oracle and DataStage, the rest is easy.
Regarding nulls and Oracle loader.
If the second example you give is acceptable, then Id write a Transform (called, to pick a name out of the air) Nvl which mimics the Oracle Nvl function. As it appears you might use it a lot, you could write a transform that explicitly replaces null or empty strings with a space.
When we first started using DataStage, we used a lot of Oracle loader stages. We eventually replaced them all with OCI stages.
Cheers,
David Barham
Information Technology Consultant
CoalMIS Project
Anglo Coal Australia Pty Ltd
Brisbane, Australia
-----Original Message-----
From: Basil BPR05 Privitera [SMTP:BPRIVITERA@QANTAS.COM.AU]
Sent: Sunday, 4 February 2001 9:39
To: informix-datastage@oliver.com
Subject: Loading Time Values Using OCI Stage
Fellow DS Users,
Just wondering how would I be able to load DATE+TIME values into my DW
through the OCI 8 Stage.
I have got this working through the ODBC stage but unfortunately some of my
tables have way too many columns for ODBC to handle, hence the need for
OCI.
I would prefer to load data that looks like "YYYY-MM-DD HH24:MI" in my data
file.
I would also prefer NOT to have to change my default NLS_DATE_FORMAT
setting as it is going to affect a whole lot of other users. The error
message I get from DS is that it cant convert the format given o a
DateTime format.
-----------------
While we are here, how can I prevent the Oracle Bulk Loader from converting
my blank character fields to NULLS when I really want to insert blanks or a
space. My data will often have blank values for character fields (which is
OK) but the bulk loader insists on trying to convert these to NULL and my
table schema is defined as having ALL fields defined as NOT NULL so it
wont load !
eg, Data file looks like this after processing through DS (sqlldr converts
last field to NULL) ... "Field1","Field2",""
When the data looks like this "Field1","Field2"," " (Note the blank in the
last field), it will load blanks into the third field.
P.S. We are using DS 4.0.1 on Win NT.
Help !!
Basil Privitera
DS Consultant
Altis Consulting
*******************Confidentiality and Privilege
Notice*******************
This email is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to
anyone, and you should destroy this message and kindly notify the sender by
reply email. Confidentiality and legal privilege are not waived or lost by
reason of mistaken delivery to you.
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
When using the ORAOCI8 stage, use a data type of TIMESTAMP for ALL date fields.
If you are using generated queries (which hopefully is most of the time), Then there is nothing more for you to do.
However, if you are using a user defined query, there is one more thing you need to know. When reading from or writing to Oracle, treat the "timestamp" field as a character field. That is, when writing to Oracle, you need a construct like, for example, to_date(:3,YYYY-MM-DD HH24:MI:SS). :3 refers to the 3rd field in the link (in this case). When reading from Oracle, you will need to_char(date_field, YYYY-MM-DD HH24:MI:SS).
On a reference link, with a user defined query, you may need both, especially if one of your keys is a date. Also remember that on a reference link, :1, :2, :3 etc refers to the key fields only.
You do not need to change NLS_DATE_FORMAT.
The error you are getting sounds to me like you are using DATE type in DataStage instead of TIMESTAMP.
At Anglo, we use Oracle more than any other data source or target. Once you understand date handling between Oracle and DataStage, the rest is easy.
Regarding nulls and Oracle loader.
If the second example you give is acceptable, then Id write a Transform (called, to pick a name out of the air) Nvl which mimics the Oracle Nvl function. As it appears you might use it a lot, you could write a transform that explicitly replaces null or empty strings with a space.
When we first started using DataStage, we used a lot of Oracle loader stages. We eventually replaced them all with OCI stages.
Cheers,
David Barham
Information Technology Consultant
CoalMIS Project
Anglo Coal Australia Pty Ltd
Brisbane, Australia
-----Original Message-----
From: Basil BPR05 Privitera [SMTP:BPRIVITERA@QANTAS.COM.AU]
Sent: Sunday, 4 February 2001 9:39
To: informix-datastage@oliver.com
Subject: Loading Time Values Using OCI Stage
Fellow DS Users,
Just wondering how would I be able to load DATE+TIME values into my DW
through the OCI 8 Stage.
I have got this working through the ODBC stage but unfortunately some of my
tables have way too many columns for ODBC to handle, hence the need for
OCI.
I would prefer to load data that looks like "YYYY-MM-DD HH24:MI" in my data
file.
I would also prefer NOT to have to change my default NLS_DATE_FORMAT
setting as it is going to affect a whole lot of other users. The error
message I get from DS is that it cant convert the format given o a
DateTime format.
-----------------
While we are here, how can I prevent the Oracle Bulk Loader from converting
my blank character fields to NULLS when I really want to insert blanks or a
space. My data will often have blank values for character fields (which is
OK) but the bulk loader insists on trying to convert these to NULL and my
table schema is defined as having ALL fields defined as NOT NULL so it
wont load !
eg, Data file looks like this after processing through DS (sqlldr converts
last field to NULL) ... "Field1","Field2",""
When the data looks like this "Field1","Field2"," " (Note the blank in the
last field), it will load blanks into the third field.
P.S. We are using DS 4.0.1 on Win NT.
Help !!
Basil Privitera
DS Consultant
Altis Consulting
*******************Confidentiality and Privilege
Notice*******************
This email is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to
anyone, and you should destroy this message and kindly notify the sender by
reply email. Confidentiality and legal privilege are not waived or lost by
reason of mistaken delivery to you.
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************