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@xxxxxxxxx]
Sent: Tuesday, February 12, 2008 10:42 AM
To: dbi-users@xxxxxxxx
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@xxxxxxx>
To: dbi-users@xxxxxxxx
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
.

Relevant Pages