RE: timestamp datatype
- From: andy@xxxxxxxxxxx (Andy Hassall)
- Date: Wed, 28 Jun 2006 19:17:45 +0100
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 :: andy@xxxxxxxxxxx :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
-----Original Message-----
From: Loo, Peter # PHX [mailto:Peter.Loo@xxxxxxxxxxxxxxxxxxxxxxxx]
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
.
- References:
- timestamp datatype
- From: Peter # PHX Loo
- timestamp datatype
- Prev by Date: RE: CSV problem on Solaris
- Next by Date: RE: timestamp datatype
- Previous by thread: timestamp datatype
- Next by thread: RE: timestamp datatype
- Index(es):
Relevant Pages
|
