Re: selecting millisecond or microsecond accuracy from varray of timestamps

[John Scoles]

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


    

________________________________