Hi All,
I am getting the following error when I try INSERTing selected rows from
one database to another.
ORA-01843: not a valid month
Do I have to use to_date() and if so, has anyone done this with bind
variables?
I am using bind variables to INSERT the selected rows. Below is the
code and the table layout.
SOURCE DATABASE TABLE (Netezza)
nv01()=> \d t_bil_payment_type_codes
Table "t_bil_payment_type_codes"
Attribute | Type | Modifier | Default Value
-------------------+-----------------------+----------+---------------
bil_pymt_typ_cde | character(1) | not null |
bil_pymt_typ_desc | character varying(40) | not null |
lst_updt_id | character varying(40) | |
lst_updt_tstmp | timestamp | |
Distributed on hash: "bil_pymt_typ_cde"
nv01()=> select * from t_bil_payment_type_codes;
bil_pymt_typ_cde | bil_pymt_typ_desc | lst_updt_id | lst_updt_tstmp
------------------+-------------------+-------------+-------------------
--
1 | CASH | ODSIHR | 2005-09-08
19:32:38
2 | MEDICAID | ODSIHR | 2005-09-08
19:32:38
3 | MEDICARE | ODSIHR | 2005-09-08
19:32:38
4 | THIRD PARTY | ODSIHR | 2005-09-08
19:32:38
(4 rows)
DESTINATION DATABASE TABLE (Oracle)
SQL> desc t_bil_payment_type_codes
Name Null? Type
----------------------------------------- --------
----------------------------
BIL_PYMT_TYP_CDE NOT NULL CHAR(1)
BIL_PYMT_TYP_DESC NOT NULL VARCHAR2(40)
LST_UPDT_ID VARCHAR2(40)
LST_UPDT_TSTMP TIMESTAMP(6)
SQL> select * from t_bil_payment_type_codes;
B BIL_PYMT_TYP_DESC
- ----------------------------------------
LST_UPDT_ID
----------------------------------------
LST_UPDT_TSTMP
------------------------------------------------------------------------
---
1 CASH
ODSIHR
08-SEP-05 07.32.38.000000 PM
CODE EXCERPT:
$sqlString = qq{select * from t_bil_payment_type_codes};
$tblName = "t_bil_payment_type_codes";
unless ($s_sth = $s_dbh->prepare(qq{$s_sqlString})) {
$MESSAGE = "sub_prepare() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
unless ($s_sth->execute()) {
$MESSAGE = "sub_execute() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
unless ($s_arrayref = $s_sth->fetchall_arrayref()) {
$MESSAGE = "sub_fetchallArrayref() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
if ($dbDriver eq "Oracle") {
$d_sqlString = qq{SELECT COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('$tblName')
AND OWNER = UPPER('$schema')
ORDER BY COLUMN_ID};
}
elsif ($dbDriver eq "ODBC") {
$d_sqlString = qq{select column_name, data_type
from all_tab_columns
where table_name = lower('$tblName')
and owner = lower('$schema')
order by column_sequence_nbr};
unless ($d_sth = $d_dbh->prepare(qq{$d_sqlString})) {
$MESSAGE = "sub_prepare() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
unless ($d_sth->execute()) {
$MESSAGE = "sub_execute() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
unless ($d_arrayref = $d_sth->fetchall_arrayref()) {
$MESSAGE = "sub_fetchallArrayref() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
foreach $column (@{$d_arrayref}) {
push (@cols, @{$column});
}
$delimCols = join(', ', @cols);
$bindVars = join ', ', ('?') x @{$d_arrayref};
$d_sqlString = "insert into " . $tblName . " (" . $delimCols . ")
values" . " (" . $bindVars . ")";
unless ($d_sth = $d_dbh->prepare(qq{$d_sqlString})) {
$MESSAGE = "sub_prepare() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
foreach $bindVar (@{$s_arrayref}) {
unless ($d_sth->execute(@$bindVar)) {
$MESSAGE = "sub_executeBind() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
}
Thanks a bunch in advance.
Peter
