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 > > ________________________________
