timestamp datatype

------_=_NextPart_001_01C69AC5.85958CD5
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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

unless ($d_sth =3D $d_dbh->prepare(qq{$d_sqlString})) {
$MESSAGE =3D "sub_prepare() - $DBI::errstr";
$STATUS =3D $FAILURE;
sub_exit();
}
=20
foreach $bindVar (@{$s_arrayref}) {
unless ($d_sth->execute(@$bindVar)) {
$MESSAGE =3D "sub_executeBind() - $DBI::errstr";
$STATUS =3D $FAILURE;
sub_exit();
}
}
=20
Thanks a bunch in advance.
=20
Peter

------_=_NextPart_001_01C69AC5.85958CD5--

RE: timestamp datatype

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@andyh.co.uk :: 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@source.wolterskluwer.com]
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

RE: timestamp datatype

Hi Andy,

I created a subroutine to assist in the creation of an INSERT statement,
like so:

sub sub_createInsert($$) {
print STDERR "\n***** sub_createInsert() *****\n\n";
my ($tblName, $columnsref) =3D @_;
my ($sqlString, $delimCols, $bindVars, $column, @cols, @temp, $cnt);
my (@timestamp, @date);
$cnt =3D 0;
foreach $column (@{$columnsref}) {
$cnt++;
$timestamp[$cnt] =3D $cnt if ("@{$column}[1]" =3D~ m/timestamp/i);
$date[$cnt] =3D $cnt if ("@{$column}[1]" =3D~ m/date/i);
push (@cols, @{$column}[0]);
}
$delimCols =3D join(', ', @cols);
$bindVars =3D join ', ', ('?') x @{$columnsref};
@temp =3D split(/,/, $bindVars);
for (my $i=3D1; $i<=3D$cnt; $i++) {
if ($timestamp[$i]) {
$temp[$i] =3D "to_date(?, 'YYYY-MM-DD HH24:MM:SS')";
}
elsif ($date[$i]) {
$temp[$i] =3D "to_date(?)";
}
}
$bindVars =3D join(', ', @temp);
$sqlString =3D "insert into " . $tblName . " (" . $delimCols . ")
values" . " (" . $bindVars . ")";
print STDERR "RETURNING SQL STRING: $sqlString\n\n";
return($sqlString);
} #<===3D (End of sub_createInsert)=20

It appears to be working quite nicely. :)

Thanks for all your help.
=20
Peter

-----Original Message-----
From: Andy Hassall [mailto:andy@andyh.co.uk]=20
Sent: Wednesday, June 28, 2006 11:18 AM
To: Loo, Peter # PHX; 'DBI-Users'
Subject: RE: timestamp datatype

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@andyh.co.uk :: 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@source.wolterskluwer.com]
Sent: 28 June 2006 16:14
To: DBI-Users
Subject: timestamp datatype

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

unless ($d_sth =3D $d_dbh->prepare(qq{$d_sqlString})) {
$MESSAGE =3D "sub_prepare() - $DBI::errstr";
$STATUS =3D $FAILURE;
sub_exit();
}
=20
foreach $bindVar (@{$s_arrayref}) {
unless ($d_sth->execute(@$bindVar)) {
$MESSAGE =3D "sub_executeBind() - $DBI::errstr";
$STATUS =3D $FAILURE;
sub_exit();
}
}
=20
Thanks a bunch in advance.
=20
Peter



This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.