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
