Well maybe DBD::Oracle dose not know how to bind them,
Set the debug to 15 before the prepare statement and off after the binds
and lets see what you get.
these are very obscure Oracle types so I could see that they may not work.
cheers
John Scoles
Vanole, Mike wrote:
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:[EMAIL PROTECTED]
Sent: Tuesday, February 12, 2008 10:42 AM
To: [email protected]
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" <[EMAIL PROTECTED]>
To: [email protected]
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