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
