2 solutions:

1) write a java stored procedure. java has time functionality that is much more granular then the Oracle DATE type. This will work in 8.1.5 and up. It can look like this:

ops$tkyte@DEV8I.WORLD> CREATE or replace JAVA SOURCE
2 NAMED "MyTimestamp"
3 AS
4 import java.lang.String;
5 import java.sql.Timestamp;
6
7 public class MyTimestamp
8 {
9 public static String getTimestamp()
10 {
11 return (new
12 Timestamp(System.currentTimeMillis())).toString();
13 }
14 };
15 /

Java created.

ops$tkyte@DEV8I.WORLD> create or replace function my_timestamp return varchar2
2 AS LANGUAGE JAVA
3 NAME 'MyTimestamp.getTimestamp() return java.lang.String';
4 /

Function created.


ops$tkyte@DEV8I.WORLD> l
1* select my_timestamp, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
ops$tkyte@DEV8I.WORLD> /

MY_TIMESTAMP
----------------------------------------------------------------------------------------------------
TO_CHAR(SYSDATE,'YY
-------------------
2000-06-22 13:47:53.376
2000-06-22 13:47:53


ops$tkyte@DEV8I.WORLD>


2) use an external procedure written in C. C has api's to the system that allow for much more granular time components. This will work in 8.0 and up. For example if you run something like:

ops$tkyte@DEV8I.WORLD> create or replace library timelib as
2 '/export/home/tkyte/src/t/extproc.so'
3 /
Library created.

ops$tkyte@DEV8I.WORLD> create or replace
2 procedure get_extended_time( p_timestring out varchar2 )
3 is external
4 name "get_extended_time"
5 library timelib
6 language C
7 with context
8 parameters ( CONTEXT,
9 p_timestring STRING,
10 p_timestring INDICATOR short,
11 p_timestring MAXLEN int,
12 p_timestring LENGTH int );
13
14 /

Procedure created.
ops$tkyte@DEV8I.WORLD> declare
2 l_timestring varchar2(30);
3 begin
4 get_extended_time( l_timestring );
5 dbms_output.put_line(
to_char( sysdate, 'mm/dd/yy hh24:mi:ss' ) );
6 dbms_output.put_line( l_timestring );
7 end;
8 /
06/22/00 13:26:28
06/22/00 13:26:28.103243

PL/SQL procedure successfully completed.

In sqlplus after compiling the following C code into a .so or .dll or .sl (depending on platform) you can get the milliseconds.

Here is the C code:

#include <stdio.h>
#include <stdarg.h>
#include <time.h>

#ifndef OCI_ORACLE
# include <oci.h>
#endif


#define raise_application_error return raise_application_error_x

static long raise_application_error_x( OCIExtProcContext * ctx,
int errCode,
char * errMsg, ...)
{
char msg[8192];
va_list ap;

va_start(ap,errMsg);
vsprintf( msg, errMsg, ap );
va_end(ap);

OCIExtProcRaiseExcpWithMsg(ctx,errCode,msg,strlen(msg));
return -1;
}

long
get_extended_time( OCIExtProcContext * ctx,
char * p_data,
short * p_data_i,
int * p_data_maxl,
int * p_data_l )
{
struct timeval tp;

if ( *p_data_maxl < 25 )
raise_application_error( ctx, 20001,
"String must be 25 bytes or more" );

gettimeofday(&tp, NULL);
cftime( p_data, "%D %T", &tp.tv_sec );
sprintf( p_data+strlen(p_data), ".%d", tp.tv_usec );

*p_data_l = strlen(p_data);
*p_data_i = 0;

return 0;
}