Michael,
No that just gives me the same error message:
Here is my entire program:
#!/usr/bin/perl
use DBI qw( neat_list );
use DBD::DB2::Constants;
use DBD::DB2 qw($attrib_int $attrib_char $attrib_float $attrib_date
$attrib_ts);
use Date::Manip;
use FileHandle;
###################
#
# DB2 connect stuff
#
###################
print "User Name: ";
chop($uid = <STDIN>);
print "Password: \n";
system ("stty -echo");
chop($pw = <STDIN>);
system ("stty echo");
print "Database: ";
chop($dbase = <STDIN>);
$DSN = join ':','dbi','DB2',$dbase;
$db2_dbh = DBI->connect($DSN, $uid, $pw)
|| die "Cannot connect to $DBI: $DBI::errstr\n" unless $dbh;
###################
#
# Oracle connect stuff
#
###################
$ora_uid = 'XXXXX';
$ora_pw = 'EEEEEE';
$ora_dbase = 'SSSSS';
$ora_DSN = join ':','dbi','Oracle',$ora_dbase;
$ora_dbh = DBI->connect($ora_DSN, $ora_uid, $ora_pw)
|| die "Cannot connect to $DBI: $DBI::errstr\n" unless $dbh2;
############################
$db2_dbh -> {RaiseError} = 1;
$ora_dbh -> {RaiseError} = 1;
$ora_dbh -> {PrintError} = 1;
############################
open (REPORT_FILE, ">DB2_data_load.log") || die "Can't create
DB2_data_load.log file \n";
$strt_date = &ParseDate("today");
$start_date = &UnixDate($strt_date,"%d-%m-%Y %H:%M:%S");
print "Start Run $start_date \n";
############################
# Main Section
#
############################
my $ora_sqld="ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'";
$ora_dbh->do($ora_sqld);
$ora_stmt = qq{insert into taborg (FIL_ID_NUM, ABI_ORIG_CD,
ABB_REGTN_STAT_CD, ABB_NUM, ABI_MOM_ABB_NUM,
ABI_MOM_STAT_CD, ABI_DAD_ABB_NUM, ABI_DAD_STAT_CD, ABB_LAST_CON_DT,
ABB_CON_PER_NAM, ABI_LAST_UPDT_DT,
SEG_UPDT_LOCK_NUM, ABI_TXT, ABI_ABL_STAT_CD, ABI_ABL_STAT_CD_DT,
ABI_CHLD_REGTN_NUM, ABI_MOM_REGTN_NUM, ABI_DAD_REGTN_NUM)
values
(?,?,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,?,?,
?,?,?,?)};
$ora_sth = $ora_dbh->prepare($ora_stmt);
$db2_stmt = qq{select * from hmiztdb.taborg};
$db2_sth = $db2_dbh->prepare($db2_stmt)|| die "Error on prepare
$DBI::errstr";
$db2_rc = $db2_sth->execute()|| die "Error on Execute $DBI::errstr";
$rv = $db2_sth->bind_columns (undef, \$FIL_ID_NUM, \$ABI_ORIG_CD,
\$ABB_REGTN_STAT_CD,
\$ABB_NUM, \$ABI_MOM_ABB_NUM, \$ABI_MOM_STAT_CD, \$ABI_DAD_ABB_NUM,
\$ABI_DAD_STAT_CD, \$ABB_LAST_CON_DT, \$ABB_CON_PER_NAM,
\$ABI_LAST_UPDT_DT,
\$SEG_UPDT_LOCK_NUM, \$ABI_TXT, \$ABI_ABL_STAT_CD, \$ABI_ABL_STAT_CD_DT,
\$ABI_ABL_EFF_DT, \$ABI_ABL_LPD_DT, \$ABI_ABL_EXP_DT,
\$ABI_CHLD_REGTN_NUM,
\$ABI_MOM_REGTN_NUM, \$ABI_DAD_REGTN_NUM, \$ABI_ORIG_UPDT_DT);
while ($db2_sth->fetch) {
print "$FIL_ID_NUM, $ABI_ORIG_CD, $ABB_REGTN_STAT_CD, $ABB_NUM,
$ABI_MOM_ABB_NUM, $ABI_MOM_STAT_CD, $ABI_DAD_ABB_NUM, $ABI_DAD_STAT_CD,
$ABB_LAST_CON_DT, $ABB_CON_PER_NAM, $ABI_LAST_UPDT_DT, $SEG_UPDT_LOCK_NUM,
$ABI_TXT, $ABI_ABL_STAT_CD, >$ABI_ABL_STAT_CD_DT<, $ABI_ABL_EFF_DT,
$ABI_ABL_LPD_DT, $ABI_ABL_EXP_DT, $ABI_CHLD_REGTN_NUM, $ABI_MOM_REGTN_NUM,
$ABI_DAD_REGTN_NUM, $ABI_ORIG_UPDT_DT\n";
if ($ABI_ABL_STAT_CD_DT eq '') { $ABI_ABL_STAT_CD_DT = undef;}
$ora_sth->execute ($FIL_ID_NUM, $ABI_ORIG_CD, $ABB_REGTN_STAT_CD,
$ABB_NUM, $ABI_MOM_ABB_NUM, $ABI_MOM_STAT_CD, $ABI_DAD_ABB_NUM,
$ABI_DAD_STAT_CD, $ABB_LAST_CON_DT, $ABB_CON_PER_NAM,
$ABI_LAST_UPDT_DT,
$SEG_UPDT_LOCK_NUM, $ABI_TXT, $ABI_ABL_STAT_CD, $ABI_ABL_STAT_CD_DT,
$ABI_CHLD_REGTN_NUM,
$ABI_MOM_REGTN_NUM, $ABI_DAD_REGTN_NUM);
}
$log_date = &ParseDate("today");
$date_2 = &UnixDate($log_date,"%d-%m-%Y %H:%M:%S");
print "\n\nEnd Run: $date_2\n";
$sth->finish();
$result =$dbh->disconnect;
exit;
Thanks for the help.
-----Original Message-----
From: Fox, Michael [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 03, 2001 4:01 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Problems inserting a NULL Date using Place holders.
Ronald,
I cannot reproduce your problem, but you might want to try altering the
session date format, rather than using to_date, as in
my $sqld="ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'";
$dbh->do($sqld);
and just use a string placeholder ? instead of to_date(?,'YYYY-MM-DD') in
the SQL
-----Original Message-----
From: Warden, Ronald MCF:EX [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 04, 2001 7:46 AM
To: '[EMAIL PROTECTED]'
Subject: Problems inserting a NULL Date using Place holders.
I have a problems. I am trying to read data from one data base DB2 and
insert it into an Oracle database. I have got the script that goes off and
discovers the structure of the DB2 database table and creates it in my
oracle database. I am now working on the part that goes off and gets the
data from the DB2 database and loads it into the oracle database.
I get the following error from my program when I run it.
CS00000699, F, R, , , , , , 0001-01-01, CONTACT PERSON
NAME , 1999-08-16, -1759758998, TEXT FROM TABORG TABLE , , ><, , , ,
, , ,
DBD::Oracle::st execute failed: ORA-02005: implicit (-1) length not
valid for this bind or define datatype (DBD ERROR: OCIBindByName) at
test_taborg_load.pl line 115.
After much investigate (inserting only one coloumn at a time) I found out
that the problem is that I have a NULL values for a date. My insert string
lools like:
$ora_stmt = qq{insert into taborg (FIL_ID_NUM, ABI_ORIG_CD,
ABB_REGTN_STAT_CD, ABB_NUM, ABI_MOM_ABB_NUM,
ABI_MOM_STAT_CD, ABI_DAD_ABB_NUM, ABI_DAD_STAT_CD, ABB_LAST_CON_DT,
ABB_CON_PER_NAM, ABI_LAST_UPDT_DT,
SEG_UPDT_LOCK_NUM, ABI_TXT, ABI_ABL_STAT_CD, ABI_ABL_STAT_CD_DT,
ABI_CHLD_REGTN_NUM, ABI_MOM_REGTN_NUM, ABI_DAD_REGTN_NUM)
values
(?,?,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,?,?,
to_date(?,'YYYY-MM-DD'),?,?,?)};
and my execute command looks like:
$ora_sth->execute ($FIL_ID_NUM, $ABI_ORIG_CD, $ABB_REGTN_STAT_CD,
$ABB_NUM, $ABI_MOM_ABB_NUM, $ABI_MOM_STAT_CD, $ABI_DAD_ABB_NUM,
$ABI_DAD_STAT_CD, $ABB_LAST_CON_DT, $ABB_CON_PER_NAM,
$ABI_LAST_UPDT_DT,
$SEG_UPDT_LOCK_NUM, $ABI_TXT, $ABI_ABL_STAT_CD, $ABI_ABL_STAT_CD_DT,
$ABI_CHLD_REGTN_NUM,
$ABI_MOM_REGTN_NUM, $ABI_DAD_REGTN_NUM);
All of the values come from a bin_coloumn command when I read the data from
the DB2 database. The bind_columns commands looks like:
$rv = $db2_sth->bind_columns (undef, \$FIL_ID_NUM, \$ABI_ORIG_CD,
\$ABB_REGTN_STAT_CD,
\$ABB_NUM, \$ABI_MOM_ABB_NUM, \$ABI_MOM_STAT_CD,
\$ABI_DAD_ABB_NUM,
\$ABI_DAD_STAT_CD, \$ABB_LAST_CON_DT, \$ABB_CON_PER_NAM,
\$ABI_LAST_UPDT_DT,
\$SEG_UPDT_LOCK_NUM, \$ABI_TXT, \$ABI_ABL_STAT_CD,
\$ABI_ABL_STAT_CD_DT,
\$ABI_ABL_EFF_DT, \$ABI_ABL_LPD_DT, \$ABI_ABL_EXP_DT,
\$ABI_CHLD_REGTN_NUM,
\$ABI_MOM_REGTN_NUM, \$ABI_DAD_REGTN_NUM, \$ABI_ORIG_UPDT_DT);
The last to_date in the $ora_stmt is what is failing.
When I use the print neat_list I get an undef in the place where the date
should be and from the data line I only have the >< no extra characters.
I even added the line:
if ($ABI_ABL_STAT_CD_DT eq '') { $ABI_ABL_STAT_CD_DT = undef;}
I have checked the archives, but I can't see any hints to help me out.
I am not certain what I am missing, this should be fairly simple. I have
read the fine manual, as well as Tim's book.
Thanks
---
Ronald Warden
Corporate Data Manager
250.356.0466
Data Services Branch
Ministry for Children and Families
CAUTION
This e-mail and any files transmitted with it are privileged and
confidential information intended for the use of the addressee. The
confidentiality and/or privilege in this e-mail is not waived, lost or
destroyed if it has been transmitted to you in error. If you have received
this e-mail in error you must (a) not disseminate, copy or take any action
in reliance on it; (b) please notify Australia Post immediately by return
e-mail to the sender; and (c) please delete the original e-mail.
