Timestamp retrieval

Need your help.

How to retrieve SQLT_TIMESTAMP binary representation (including fraction) in Oracle9 using dynamic embedded SQL ("Method 4": declare->open->describe->fetch->close), and what is the struct format (SQLDA::V[i] for timestamp [when SQLDA::T[i]=SQLT_TIMESTAMP])?

Thanks in advance!
LVL 4
DemiurgAsked:
Who is Participating?
 
earth man2Connect With a Mentor Commented:
The safest way is to use OCI function.

void OCIDateTimeGetTime ( dvoid         *hndl,
                          OCIError      *err,
                          OCIDateTime   *datetime,
                          ub1           *hour,
                          ub1           *min,
                          ub1           *sec,
                          ub4           *fsec );

SQL> select dump(current_timestamp),current_timestamp from dual;
DUMP(CURRENT_TIMESTAMP)
--------------------------------------------------------------------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
Typ=188 Len=20: 212,7,9,7,11,37,26,71,40,144,244,57,1,0,5,68,0,0,0,77
07-SEP-04 12.37.26.972329 +01:00

You can see century + 100, decade + 100, month, day, hh24, mi, ss in the byte stream but also there is a GMT offset and fractional seconds there too.
Oracle are reluctant to let you know these formats because they may change in the future so would like you to use functional api to isolate functionality from the implementation,
0
 
seazodiacCommented:
Can you elaborate ? Is it just me having hard time understanding your question?

are you trying to retrieve the timestamp and display them?

0
 
DemiurgAuthor Commented:
I write a program (MSVC 6.0) that communicates with Oracle 9 using dynamic embedded SQL.
I want to fetch value(s) (using cursor) of a column of type SQLT_TIMESTAMP. After "prepare" statement sqlda items contain: T[i]=187 (SQLT_TIMESTAMP), L[i]=11 (seems like timestamp struct is 11 bytes long). I assign V[i] to a pointer to 128-byte array (array is zeroed). When "fetch" statement is executed, program fails with exception (memory access violation). Then i tried to put a pointer to another memory block at the beginning of 128-byte array (rest of it is still zeroed). In this case that block is filled with struct like
{
short year;
char month;
char day;
char hour;
char minute;
char second;
}, and than 13 zero bytes except for 15th, which equal to 0x03.
The question now is where is fraction (the column has 6 digits of fraction).
In general, i need to retrieve timestamp not just to display it, but to use it in calculations, thus using string representation is risky due to possible different time formats, so i need some fixed binary one. The workaround i know is to use "EXEC SQL ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF'" and then fetch it as a string (overriding T[i]=5, for example), but i'd prefer not to use it.
0
 
DemiurgAuthor Commented:
Thanks!
0
 
earth man2Commented:
http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/TIMESTAMPTZ.html

Byte       Represents
0         Century (119 for 1990)
1         Decade  (190 for 1990)
2         Month
3         Day      
4         Hour
5         Minute
6         Seconds
7         Nanoseconds (Most Significant bit)
8         Nanoseconds
9         Nanoseconds
10        Nanoseconds (Least Significant Bit)
11,12     Region id or Timezone Hour/Minute        
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.