Front page | perl.dbi.users |
Postings from February 2008
RE: Using to_dsinterval or INTERVAL with placeholder
Thread Previous
|
Thread Next
From:
Vanole, Mike
Date:
February 12, 2008 11:35
Subject:
RE: Using to_dsinterval or INTERVAL with placeholder
Thanks Joseph,
I chickened out and built the full insert statement I knew would run and
ran it in a do().
I will goof around with bind_param, but I would love to know how to do
this with '?' style placeholders. There's got to be a syntax that works
- that will load INTERVAL DAY to SECOND
Mike
-----Original Message-----
From: Lamb Joseph [mailto:joseph_lamb@yahoo.com]
Sent: Tuesday, February 12, 2008 10:42 AM
To: dbi-users@perl.org
Subject: Re: Using to_dsinterval or INTERVAL with placeholder
Mike,
Here are some of the things that I would do.
1. Set the NLS Date Format
Example I pulled from the internet
# set Oracle NLS date format
if ( $optctl{dateformat} ) {
$dbh->do(qq{alter session set nls_date_format
= '$optctl{dateformat}'} );
}
2. Using bind_param set all your parameters. I usually have problem
using placeholders when I am inserting strings into Oracle. I really do
not know why.
3. I do all my calculation and date manipulations locally. For example,
calculate the hour to minute before insert. Format the date to the NLS
Format before inserting.
Hope this helps.
Joseph Lamb
----- Original Message ----
From: "Vanole, Mike" <MV5492@att.com>
To: dbi-users@perl.org
Sent: Tuesday, February 12, 2008 8:17:28 AM
Subject: Using to_dsinterval or INTERVAL with placeholder
This
may
be
less
a
DBI
question
and
more
Oracle
-
probably
both...
Using
DBD::Oracle
Given
this
table:
TABLE
batch_application_sla
Name
Null?
Type
-----------------------------------------
--------
----------------------------
ASSOCIATION
VARCHAR2(100)
SLA
INTERVAL
DAY(2)
TO
SECOND(6)
DISPLAY
CHAR(1)
SUMMARY_DISPLAY
CHAR(1)
LOAD_STATUS
VARCHAR2(20)
PROCESS_DATE
DATE
DESCRIPTION
VARCHAR2(300)
SLA_DISP
CHAR(1)
SLA_ADD_DAYS
NUMBER
I
can
insert
a
row
as
follows:
insert
into
batch_application_sla
values('YYY',interval
'23:32'
HOUR
to
MINUTE,'y','y','COMPLETE',to_date('2008/01/01',
'YYYY/MM/DD'),'TEST','y',0)
How
can
I
use
placeholders?
I'm
getting
various
errors
with
the
INTERVAL.
This
is
one
version:
INSERT
into
batch_application_sla
values(?,to_dsinterval(?,'HH:MI'
HOUR
to
MINUTE),?,?,?,to_date(?,
'YYYY/MM/DD'),?,?,?)
The
source
data
I'm
loading
is
in
"HH:MI"
format,
but
I
can
transform
it
into
anything.
I've
also
tried:
INSERT
into
batch_application_sla
values(?,INTERVAL
?
HOUR
to
MINUTE,?,?,?,to_date(?,
'YYYY/MM/DD'),?,?,?)
Many
thanks,
Mike
________________________________________________________________________
____________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
Thread Previous
|
Thread Next