RE: timestamp datatype

[Andy Hassall]

You must tell Oracle what format your DATE or TIMESTAMP data is in, because
it's bound as a string, rather than the raw Oracle DATE or TIMESTAMP format.
This means you have to either:

(a) Explicitly state it in the SQL statement using TO_DATE(?, 'format') (or
TO_TIMESTAMP or TO_TIMESTAMP_TZ as appropriate), or,

(b) Implicitly state it using NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT or
NLS_TIMESTAMP_TZ_FORMAT - either set as environment variables before you
connect, or using ALTER SESSION after you connect.

Either way, the format you pass must match your data.

For cases where you're building up the statement, including placeholders,
dynamically as you are, the ALTER SESSION route is often more convenient, as
you can continue to bind using just a plain placeholder, instead of having
to consider the type and put a TO_* function around it.

-- 
Andy Hassall :: [EMAIL PROTECTED] :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

-----Original Message-----
From: Loo, Peter # PHX [mailto:[EMAIL PROTECTED] 
Sent: 28 June 2006 16:14
To: DBI-Users
Subject: 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