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

Reply via email to