SQL & PL/SQL

Hello,

I try to convert a SQL Server Datetime value to an Oracle timestamp, but I cannot; the maximum I get is till seconds, but I want the whole precission, till miliseconds.
I have none experience with SQL Server.

This is the architecture:
SQL Server (10.0.5500)
- SQL Server
- Table with many columns, one them has "Datetime" data type.
- Store procedure that returns the content of the table, as a direct select (I mean, no transformations are done in the query).

Oracle (11g)
- Database link to SQL Server (using Oracle Database Gateway for MS SQL Server).
- Store procedure that calls the SQL Server store procedure.
- The Oracle Table.column data type is TIMESTAMP.

Note:
The SQL Server source system wants to return the data using store procedures.
In our system, we will integrate the Oracle store procedure into a OWB mapping/process flow.

This is part of the PL/SQL code:

procedure p_test as
    rc sys_refcursor;
    TYPE typRecord IS RECORD ( 
       uid INTEGER,
       ... -- (more columns)
       Date1 DATE);

   r_typ typRecord;
BEGIN
  -- Call to the SQL Server procedure.
  "interface_GetType"@MSLINK(rc);

  LOOP
    FETCH rc INTO r_typ;
    EXIT WHEN rc%NOTFOUND;

    INSERT INTO mytable
      (uid, ..., date1)
    VALUES 
      (r_typ.uid, ... , r_typ.date1);
  END LOOP; 
  ...  
END;

I have tried these options with these results:
1. Declare date1 as DATE in the Record Type definition, and store like that when doing the INSERT. It works, but I get only till seconds precission, I miss the miliseconds.
2. Declare date1 as TIMESTAMP(3)/TIMESTAMP(6) in the Record Type definition: I get the error "ORA-28528: Heterogeneous Services datatype conversion error."
3. Declare date1 as VARCHAR2(2000) in the Record Type definition.
3.1 I insert the value without any conversion into a VARCHAR2 column (to see what I get). The result is that in this column I see only the DD-MM-YY (no hours, minutes, seconds).
3.2 I insert the date1 column into a TIMESTAMP column, without any previous conversion. What I get then is: "17.07.2012 00:00:00,000000"
3.3 I convert the date1 column with "to_timestamp(r_typ.typ_date1,'DD.MM.RRRR HH24:MI:SS'))" (note that I don't even try to get miliseconds, just only till seconds). The result is the same than 3.2.

Summary:
- If I copy the value from SQL Server datetime to VARCHAR2 and then convert it before the insertion into the table.column TIMESTAMP, it seems that the hours:minutes:seconds.miliseconds are lost.
- If I copy the value from SQL Server datetime to DATE, the miliseconds are lost.
- If I try to copy directly from SQL Server datetime to Oracle TIMESTAMP, I get the ora-28528.

Can anyone give any suggestion?

Thank you very much in advance,
Francisco.

PS: Maybe I could "force" the source SQL Server system to declare the source column as NVARCHAR instead of DATETIME, copy from SQL Server NVARCHAR to Oracle VARCHAR2, and then use the TO_TIMESTAMP Oracle function, but I would prefer to take this option as the last chance.

Edited by: FranBlanes on 30.08.2012 04:26