that should be fix with the small patch I provied the other days
or you can get the latest Trunk version of DBD oracle
at
http://svn.perl.org/modules/dbd-oracle/trunk
anyway you look at it you will have to recompile you DBD::Oracle as it
stands <1.24 will never be able to use any of the extra digits on a
timestanmp they are simply not returned by the object handler.
cheers
John Scoles
Carlson, John W. wrote:
Would this help? Note the precision on the timestamp.
CREATE OR REPLACE TYPE ARCHIVE.VOLTAGE_TIMESTAMP_ARRAY AS VARRAY(200) OF
TIMESTAMP(6);
From: John Scoles [mailto:byter...@hotmail.com]
Sent: Thursday, January 28, 2010 5:04 PM
To: Carlson, John W.; dbi-users@perl.org
Subject: RE: selecting millisecond or microsecond accuracy from varray of
timestamps
From: carlso...@llnl.gov
To: dbi-users@perl.org
Date: Thu, 28 Jan 2010 11:42:42 -0800
Subject: selecting millisecond or microsecond accuracy from varray of timestamps
We have an oracle type,
CREATE OR REPLACE TYPE ARCHIVE.VOLTAGE_TIMESTAMP_ARRAY AS VARRAY(200) OF
TIMESTAMP;
We would like to get millisecond or microsecond accuracy out of these timestamps.
We are currently using DBD::Oracle 1.21. Here is a sample query, with several
varrays & rows being returned for an identifier, which we stitch together based
on timestamp. (there may be several rows per identifier).
select identifier, output_voltage, output_current, voltage_timestamp from
CTS_VOLTAGE_ARCHIVE where unit = 'CRYO' and shot_id = ? order by sample_num
We are getting the varray values out with an array reference, not a table
function. We would like to continue doing this. I am not currently sure what
accuracy the inserts into the varray are.
Well they are as accurate as any timestamp.
Varray is not a 'special' field in any way. Oracle actually strores the VARRAY
in a seperate table of (in this case) 201 fields of 1 for a locator/id and 200
for your max size of your VARRAY. One of the reasons it never caught on as it
does not really save anything over a regular relational table except it might
be eaiser for a novice DBA who has an OOD programming background?
I am wondering if upgrading to DBD::Oracle 1.23 would improve the situation
about getting millisecond or microsecond accuracy.
Upgrading to 1.23 will not help in this in any way.
Just to ask another question are you looseing acurracy when selecting out you
values witt 1.21??
the code for getting a timestamp is like this uses OCIDateTimeToText and puts
it into a buffer of 200chars +1 for '\0'
do you need more accuracy than that??
Looking into the code I see that we do not supply a
fsprec (IN) value that Specifies the precision in which the fractional seconds
is returned. not sure it this is what is effecting you
Cheers
John Scoles
Thanks,
John Carlson
________________________________
