nulls into Oracle Oracle
Fry, Mark
Mark.Fry@COGNOS.com
Thu, 1 May 2003 08:57:02 +0100
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C30FB7.3FC9D6D0
Content-Type: text/plain;
charset="iso-8859-1"
Hi Harold,
In your dictionary, make sure that your database definition for Oracle
Oracle has the following options:
NULL VALUES ALLOWED &
DEFAULT DATE IS NULL
If memory serves (and it's been a while now!) this should allow PowerHouse
to write nulls to the date columns in ORACLE rather than trying to write
zeros.
Make sure too that you really have nulls in your RDB date columns, and not
zeros (ie the base date of 17-11-1858).
Good luck!
Mark Fry
Mark.Fry@Cognos.com
-----Original Message-----
From: Edis, Robert [mailto:Robert.Edis@blistex.com]
Sent: 30 April 2003 23:56
To: 'powerh-l@lists.swau.edu'
Subject: RE: nulls into Oracle Oracle
G'day Harold
I am assuming you mean Oracle Rdb and Oracle 9.n (or 8.n or 7.n).
How is your PHD set to handle NULL values in the receiving database? If it
is not already then set it to NULL VALUES ALLOWED.
How is your PHD set to handle conversion of NULLs it reads from the source
database? This is in the system settings at the beginning of the PDL
script. Make sure PH it not converting NULLs to something other than NULL.
Oracle Rdb allows you to put a zero in a date column. Oracle 9 (or 8 or 7)
does not if I remember correctly. It has to be a 'real' date or NULL.
What if you let the 1858 date get written to the Oracle 9 (or 8 or 7) DB and
then run PL/SQL scripts afterwards to convert just those dates to NULLs?
Regards,
Blue
-----Original Message-----
From: Johnson, Harold A EDUC:EX [mailto:Harold.A.Johnson@gems1.gov.bc.ca]
Sent: Wednesday, April 30, 2003 5:20 PM
To: 'powerh-l@lists.swau.edu'
Subject: nulls into Oracle Oracle
Hi all. I'm using VMS Powerhouse (830d) to convert data directly from an
RDB database to an Oracle Oracle database. When I assign a field to the
"OO" database, a NULL value from RDB gets converted to that old "1858/11/17
00:00:00.00" null date. Is there anyway to force an OO null into the field
without checking every single field in RDB for "if ... is null"?????
If I don't assign anything to a field, then it gets assigned properly to a
"~".
thnx!
This message may contain privileged and/or confidential information. If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. Thank you.
------_=_NextPart_001_01C30FB7.3FC9D6D0
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.4725.2100" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>Hi
Harold,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>In
your dictionary, make sure that your database definition for Oracle Oracle has
the following options:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>NULL
VALUES ALLOWED &</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003>DEFAULT DATE IS NULL</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>If
memory serves (and it's been a while now!) this should allow PowerHouse to write
nulls to the date columns in ORACLE rather than trying to write
zeros.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>Make
sure too that you really have nulls in your RDB date columns, and not zeros (ie
the base date of 17-11-1858).</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>Good
luck!</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=997154007-01052003>Mark
Fry</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003>Mark.Fry@Cognos.com</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=997154007-01052003></SPAN></FONT> </DIV><FONT face=Arial
color=#0000ff><SPAN class=997154007-01052003></SPAN></FONT><FONT face=Tahoma>
<DIV><BR><FONT size=2><SPAN class=997154007-01052003> </SPAN>-----Original
Message-----<BR><B>From:</B> Edis, Robert
[mailto:Robert.Edis@blistex.com]<BR><B>Sent:</B> 30 April 2003
23:56<BR><B>To:</B> 'powerh-l@lists.swau.edu'<BR><B>Subject:</B> RE: nulls into
Oracle Oracle<BR><BR></DIV></FONT></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003>G'day Harold</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=062184922-30042003>I am
assuming you mean Oracle Rdb and Oracle 9.n (or 8.n or
7.n).</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=062184922-30042003>How
is your PHD set to handle NULL values in the receiving database? If it
is not already then set it to NULL VALUES ALLOWED. </SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=062184922-30042003>How
is your PHD set to handle conversion of NULLs it reads from the source
database? This is in the system settings at the beginning of the PDL
script. Make sure PH it not converting NULLs to something other than
NULL.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003>Oracle Rdb allows you to put a zero in a date
column. Oracle 9 (or 8 or 7) does not if I remember correctly. It
has to be a 'real' date or NULL.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=062184922-30042003>What
if you let the 1858 date get written to the Oracle 9 (or 8 or 7) DB and then
run PL/SQL scripts afterwards to convert just those dates to
NULLs?</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=062184922-30042003>Blue</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Johnson, Harold A EDUC:EX
[mailto:Harold.A.Johnson@gems1.gov.bc.ca]<BR><B>Sent:</B> Wednesday, April
30, 2003 5:20 PM<BR><B>To:</B> 'powerh-l@lists.swau.edu'<BR><B>Subject:</B>
nulls into Oracle Oracle<BR><BR></FONT></DIV>
<DIV><SPAN class=395241422-30042003><FONT face=Arial color=#0000ff size=2>Hi
all. I'm using VMS Powerhouse (830d) to convert data directly
from an RDB database to an Oracle Oracle database. When I assign a
field to the "OO" database, a NULL value from RDB gets converted to that old
"<FONT face=r_ansi size=2>1858/11/17 00:00:00.00" <FONT face=Arial>null
date. Is there anyway to force an OO null into the field without
checking every single field in RDB for "if ... is
null"?????</FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=395241422-30042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=395241422-30042003><FONT face=Arial color=#0000ff size=2>If
I don't assign anything to a field, then it gets assigned properly to a
"~".</FONT></SPAN></DIV>
<DIV><SPAN class=395241422-30042003></SPAN> </DIV>
<DIV><SPAN class=395241422-30042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=395241422-30042003><FONT face=Arial color=#0000ff
size=2>thnx!</FONT></SPAN></DIV>
<DIV><SPAN class=395241422-30042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Arial color=#0000ff
size=2></FONT> </DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
<BR>
<P><FONT SIZE=2 FACE="Arial">This message may contain privileged and/or confidential information. If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you.</FONT></P>
------_=_NextPart_001_01C30FB7.3FC9D6D0--