Forum Stats

  • 3,741,433 Users
  • 2,248,429 Discussions
  • 7,861,800 Comments

Discussions

Cannot sys_extract_utc unified_audit_trail.event_timestamp in 12c R1

altink
altink Member Posts: 3
edited Jun 23, 2019 8:21AM in Database Security - General

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

mseberg

Comments

  • mseberg
    mseberg Member Posts: 7,004 Silver Crown
    edited Jun 17, 2019 9:56AM

    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

  • altink
    altink Member Posts: 3
    edited Jun 20, 2019 12:16PM

    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

    mseberg
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jun 23, 2019 12:09AM

    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 argumentSQL>

    ...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),...
  • altink
    altink Member Posts: 3
    edited Jun 23, 2019 8:21AM

    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

Sign In or Register to comment.