8 Replies Latest reply: May 17, 2009 7:02 PM by CezarSantos-http://odiexperts.com RSS

    sql server datetime to oracle timestamp issue  ?

    688337
      hi everyone ,

      when i try to convert the sql server datetime to oracle timestamp it showing me error inthe interface namely ORA-30088: datetime/interval precision is out of range .


      The agove error occurs at the work table creation itself .

      i have modified it the physical architecture data type converted to sql server datetime to oracle timestamp and tried but still the same error.

      can someone help me with this.

      Thanks
        • 1. Re: sql server datetime to oracle timestamp issue  ?
          566902
          Can you show us the SQL generated on the create table step that is failing?
          • 2. Re: sql server datetime to oracle timestamp issue  ?
            681562
            Hi,

            Use this function in the mapping CAST(datecolumnname AS DATE).

            By this u can convert all timestamp to dates.

            Thanks,
            katukota
            • 3. Re: sql server datetime to oracle timestamp issue  ?
              688337
              The error is created at the second step namely create work table .

              It is creating all the work table this

              create table TARGET_HR.C$_0ORDERID
              (
                   C1_ORDERID     NUMBER(11) NULL,
                   C2_ORDERDATE     TIMESTAMP (11) NULL,
                   C3_SHIPPEDDATE     TIMESTAMP (11) NULL
              )
              NOLOGGING

              The work table is not created and it shows me the error ORA-30088: datetime/interval precision is out of range .

              When i copy paste the create table and try to create it . it also shows the above error.

              AS per the Metalink document id : - Doc ID:      741294.1 . i have changed the datatype converted to from date to timestamp in the oracle physical architecture . Still it fails.

              Please suggest

              Edited by: user10586521 on May 15, 2009 8:14 AM

              Edited by: user10586521 on May 15, 2009 8:19 AM
              • 4. Re: sql server datetime to oracle timestamp issue  ?
                688337
                i have solved the issues by changing i nthe create work table

                <%=odiRef.getColList("","[CX_COL_NAME]\T[DEST_DT]NULL ",",\N\T","","")%>

                where the default is the DEST_WRI_DT when i changed it to DEST_DT it worked and the ODI created the work table with TIMESTAMP With No Length assigned , as before we see that the default format for creating the work table created with timestamp and length .

                Is it a bug with ODI or is there is another way around to solve this above issue with out changing the default work table formart.

                Thanks
                • 5. Re: sql server datetime to oracle timestamp issue  ?
                  CezarSantos-http://odiexperts.com
                  Hi Dev,

                  You're right, that is a good solution.

                  Cezar
                  • 6. Re: sql server datetime to oracle timestamp issue  ?
                    688337
                    HEY CEZAR ,

                    I need your help ,

                    The work table code which i modified s

                    create table <%=odiRef.getTable("L", "COLL_NAME", "A")%>
                    (
                         <%=odiRef.getColList("", "[CX_COL_NAME]\t[DEST_DT] NULL", ",\n\t", "","")%>
                    )
                    <%=odiRef.getUserExit("WORK_TABLE_OPTIONS")%>

                    the above code creates the work table like this

                    create table TARGET_HR.C$_0ORDERID
                    (
                         C1_ORDERID     NUMBER NULL,
                         C2_ORDERDATE     TIMESTAMP NULL,
                         C3_SHIPPEDDATE     TIMESTAMP NULL,
                         C4_VAR     VARCHAR2 NULL
                    )
                    NOLOGGING


                    The default code for work table is

                    create table <%=odiRef.getTable("L", "COLL_NAME", "A")%>
                    (
                         <%=odiRef.getColList("", "[CX_COL_NAME]\t[DEST_WRI_DT] NULL", ",\n\t", "","")%>
                    )
                    <%=odiRef.getUserExit("WORK_TABLE_OPTIONS")%>

                    and it creates the work table as


                    create table TARGET_HR.C$_0ORDERID
                    (
                         C1_ORDERID     NUMBER(11) NULL,
                         C2_ORDERDATE     TIMESTAMP (11) NULL,
                         C3_SHIPPEDDATE     TIMESTAMP (11) NULL,
                         C4_VAR     VARCHAR2(50) NULL
                    )
                    NOLOGGING

                    so it creates the timestamp with 11 which is wrong and so the work table is created.

                    Can you help me if i can put a condition so that if it is timestamp i can go for DEST_DT , and for rest it will be DEST_WRI_DT or some other solution . ?? its kind of urgent .

                    THANKS
                    DEV
                    • 7. Re: sql server datetime to oracle timestamp issue  ?
                      CezarSantos-http://odiexperts.com
                      Hi Dev,

                      Let the KM with the original code

                      go to topology, physcial architecture and under datatype, edit the timestamp datatype.

                      Once there, let just the word "timestamp" at the 2 bottom text box.

                      That will solve your problem.


                      Cezar Santos