Yes, I have patched DBD::Oracle 1.23 and it's working! Thanks a bunch! It was only changing the 0 to a 6, right?
The reason I was trying to patch it another way was because I don't have root on the machine in question. John -----Original Message----- From: John Scoles [mailto:sco...@pythian.com] Sent: Thursday, February 04, 2010 11:24 AM To: Carlson, John W. Cc: John Scoles; dbi-users@perl.org Subject: Re: selecting millisecond or microsecond accuracy from varray of timestamps 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 >> >> >> > > ________________________________ > >
