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

  • Re: Using to_dsinterval or INTERVAL with placeholder
    ... Well maybe DBD::Oracle dose not know how to bind them, ... Set the NLS Date Format ... using placeholders when I am inserting strings into Oracle. ...
    (perl.dbi.users)
  • Re: Using to_dsinterval or INTERVAL with placeholder
    ... Set the NLS Date Format ... I usually have problem using placeholders when I am inserting strings into Oracle. ...
    (perl.dbi.users)
  • RE: Using to_dsinterval or INTERVAL with placeholder
    ... Using to_dsinterval or INTERVAL with placeholder ... these are very obscure Oracle types so I could see that they may not work. ... Set the NLS Date Format ...
    (perl.dbi.users)
  • Re: Date Formats
    ... tables of SQL Server. ... I had problem in exporting these fields into Oracle. ... > I presume that the problem is that Oracle doesn't interpret the datetime values from SQL server ... > a format, hence trying to enforce a format for that datatype is meaningless. ...
    (microsoft.public.sqlserver.programming)
  • Re: No one could logon to productio database for a while
    ... Puget Sound Oracle Users Group ... code for script is quite simple, it does a v$sql to find all sql ... The script ask for the hash value of the SQL statement. ... col "SLAVE SQL" format A95 WORD_WRAP ...
    (comp.databases.oracle.server)