0 Replies Latest reply: Apr 23, 2012 3:59 PM by cjohnson RSS

    TIMESTAMP(7) WITH TIME ZONE

    cjohnson
      I started using Entity Framework by generating an entity data model from an existing Oracle database. Most tables in that database contain a NOT NULL column named RECORD_TIMESTAMP of type "TIMESTAMP(7) WITH TIME ZONE". The SSDL that was generated by Oracle Developer Tools version 11.2.0.3.0 contains the following Property in most EntityType elements:
           <Property Name="RECORD_TIMESTAMP" Type="timestamp with time zone" Nullable="false" />
      When I generate DDL (Data Definition Language) SQL for a new database from the entity data model, the resulting RECORD_TIMESTAMP columns have type "TIMESTAMP WITH TIME ZONE". "TIMESTAMP WITH TIME ZONE" is equivalent to "TIMESTAMP(6) WITH TIME ZONE". For my application, I need a timestamp with precision of 10^-7 seconds (100 nanoseconds), which is "TIMESTAMP(7) WITH TIME ZONE". Thus, I need to change the entity data model to specify a precision of 7 instead of the default precision of 6 for the RECORD_TIMESTAMP columns.

      First I tried simply changing "timestamp with time zone" to "timestamp(7) with time zone" in the SSDL, but that resulted in the following build error:
           Error 40: The Type timestamp(7) with time zone is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.
      I tried qualifying the type with a namespace of "Oracle.DataAccess.Types", but that was not recognized. So I then added a Precision attribute to the RECORD_TIMESTAMP properties, as follows:
           <Property Name="RECORD_TIMESTAMP" Type="timestamp with time zone" Nullable="false" Precision="7" />
      This allows the model to build successfully, since the optional Precision attribute is specified in the SSDL XML schema (System.Data.Resources.SSDLSchema_2.xsd). However, the Precision attribute is apparently ignored by ODT when generating DDL, since the RECORD_TIMESTAMP columns still have a type of "TIMESTAMP WITH TIME ZONE" instead of the desired "TIMESTAMP(7) WITH TIME ZONE". Since ODT does not use T4 templates for generating DDL, I cannot find a way to generate DDL with columns of type "TIMESTAMP(7) WITH TIME ZONE". Suggestions?