4 Replies Latest reply on Jun 23, 2019 12:21 PM by altink

    Cannot sys_extract_utc unified_audit_trail.event_timestamp in 12c R1

    altink

      Dear All

       

      I can run the following SQL on an Oracle 12c R2:

       

      select

      sys_extract_utc(event_timestamp) as UTC_Time

      from unified_audit_trail

       

      But when the same in 12c R1, I get the following error:

       

      ORA-30175 - invalid type given for an argument

       

      When
      SQL>desc unified_audit_trail ;

       

      in both 12c R1 and R2 there is:

       

      EVENT_TIMESTAMP            TIMESTAMP(6) WITH LOCAL TIME ZONE

       

      the column data type is the same, so why is the error on 12c R1 ?

       

      best regards,
      Altin

        • 1. Re: Cannot sys_extract_utc unified_audit_trail.event_timestamp in 12c R1
          mseberg

          Does something like this work?

           

          select

            sys_extract_utc( TO_TIMESTAMP(event_timestamp) AT TIME ZONE   'US/Pacific' ) as UTC_Time

          from

            unified_audit_trail;

           

          Changing for your time zone of course.

           

          Best Regards

           

          mseberg

          • 2. Re: Cannot sys_extract_utc unified_audit_trail.event_timestamp in 12c R1
            altink

            Thank you very much mseberg,

             

            your script:

             

            sys_extract_utc( TO_TIMESTAMP(event_timestamp) AT TIME ZONE 'US/Pacific' ) as UTC_Time

             

            is executed without error on both 12cR1 and 12c R2.


            But it requires a "hard"-code-d time zone config, while I already have a simpler solution:

             

            event_timestamp at time zone 'UTC' as UTC

             

            that also performs OK in both 12cR1 and 12c R2

             

            But what this question points to is:

            Why the direct sys_extract_utc(event_timestamp) does not work in Oracle 12c R1, while does so in 12c R2, and in the Oracle view and docs the column data type is the same ?

             

            I think this might be related to the fact of the difference in UNIFIED_AUDIT_TRAIL implementation between 12cR1 and 12c R2

            12cR1  - gets raw data from SYS.GV$UNIFIED_AUDIT_TRAIL only

            12c R2 - gets raw data from AUDSYS.AUD$UNIFIED union-ed to SYS.GV$UNIFIED_AUDIT_TRAIL

             

            best regards,

            Altin

            • 3. Re: Cannot sys_extract_utc unified_audit_trail.event_timestamp in 12c R1
              Gaz in Oz

              Looks like this is a known bug related to:

              Bug 12327455 : ORA-30175 FROM SYS_EXTRACT_UTC FOR CAST TO TIMESTAMP WITH LOCAL TIME ZONE

              In 12.1.0.2.0 (and as far back as 11.2.0.2.0):

              SQL> select sys_extract_utc(cast(timestamp'2019-06-23 12:12:12.120' as timestamp with local time zone)) x from dual;

              select sys_extract_utc(cast(timestamp'2019-06-23 12:12:12.120' as timestamp with local time zone)) x from dual

                                     *

              ERROR at line 1:

              ORA-30175: invalid type given for an argument

               

              SQL>

              ...and the view unified_audit_trail does exactly that, "cast(... as timestamp with local time zone)":

              select

              ...

                       statement_id,

                       cast(event_timestamp as timestamp with local time zone),

              ...

              • 4. Re: Cannot sys_extract_utc unified_audit_trail.event_timestamp in 12c R1
                altink

                Thank you very much Gaz in Oz

                 

                I tested your command. It produced same error in both 12c R1 and a2c R2.
                I have the problem only in 12c R1.

                 

                Also checked Bug 12327455. I simulated the test case there had NO error in any of the four column select commands,

                again in both 12c R1 and 12c R2. I was expecting problem described as (quoted):
                "then the first SELECT will succeed, while the three following will fail reporting "ORA-30175: invalid type given for an argument".

                in 12c R1 - but it didn't. And finally - the Bug is for Win 32 only (Platform: 912 - Microsoft Windows (32-bit)), while I am testing

                on 64bit.

                 

                best regards

                Altin