Front page | perl.dbi.users |
Postings from February 2008
RE: Using to_dsinterval or INTERVAL with placeholder
Thread Previous
From:
Vanole, Mike
Date:
February 12, 2008 14:39
Subject:
RE: Using to_dsinterval or INTERVAL with placeholder
That worked. I formatted the input as executed as described and
everything loaded.
Thank you, Ian.
Hopefully, all this will serve my need in the end. I'm actually trying
to come up with a way to overcome the lack of a TIME with TIME ZONE
datatype as part of a conversion from PostgreSQL to Oracle.
Regards,
Mike
-----Original Message-----
From: Ian Harisay [mailto:imharisa@nuskin.com]
Sent: Tuesday, February 12, 2008 4:01 PM
To: dbi-users@perl.org
Subject: RE: Using to_dsinterval or INTERVAL with placeholder
Sorry, I haven't tried anything to test what I am about to recommend.
It doesn't look like you got your answer though. I think you want your
insert statement to look like this:
$db->do(<<"EOS",{},1,'0 03:00:00')
insert into batch_application_sla (job_id, job_duration)
values(?,to_dsinterval(?))
EOS
my understanding is that the to_dsinterval() input string must be in 'D
HH:MI:SS' format.
-----Original Message-----
From: John Scoles [mailto:scoles@pythian.com]
Sent: Tuesday, February 12, 2008 1:01 PM
To: Vanole, Mike
Cc: Lamb Joseph; dbi-users@perl.org
Subject: Re: Using to_dsinterval or INTERVAL with placeholder
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:joseph_lamb@yahoo.com]
> Sent: Tuesday, February 12, 2008 10:42 AM
> To: dbi-users@perl.org
> 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@att.com>
> To: dbi-users@perl.org
> 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
>
Thread Previous