timestamp datatype

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

Relevant Pages

  • How to remove rows from a DataTable without deleting form the database
    ... I have a data table that is connected to a database table with a data ... adapter in the 'standard' manner. ... However I want to be able to remove selected rows from the data table (i.e. ... "remove from" the current set of records - there is no underlying SQL ...
    (microsoft.public.dotnet.general)
  • Transform Data Task and RowCount delivered
    ... I have a task delivering every 15 minutes a number of lines from a database. ... Is it possible to store the number of selected rows in a global variable for ...
    (microsoft.public.sqlserver.dts)
  • RE: Truncated INSERT statements when using sp_generate_inserts by Vyas
    ... SET NOCOUNT ON ... This procedure is also useful to create a database setup, ... ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON ... IF @owner IS NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005 Express-Database does not have a valid owner?
    ... That means that the database's owner is MYDOMAIN\MyUserName ... My design machine is running Windows XP Pro. ... database diagram. ... Database diagram support objects cannot be installed because this ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
    (microsoft.public.sqlserver.server)