2 Replies Latest reply on Jul 10, 2020 3:54 PM by Danno_AECC

    SQL Dev 20.2 raises error for SYS_EXTRACT_UTC call

    Danno_AECC

      Hello all,

       

      Here's a weird problem. I upgraded from 19.4 to 20.2 and now have an issue with function call SYS_EXTRACT_UTC. The previous versions in use with other folks at my company that currently have no problem are 4.1.5.21 (wow, eh?), 18.1, 19.3, and 19.4. I have no idea what to look at to resolve this problem but we use timestamp with timezone extensively and I'm handicapped for troubleshooting code right now.

       

      SQL*Plus example (to show code):

       

      Connected to:

      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

       

      SQL> select sys_extract_utc(to_timestamp(trunc(sysdate -3))) from dual;

       

      SYS_EXTRACT_UTC(TO_TIMESTAMP(TRUNC(SYSDATE-3)))

      ---------------------------------------------------------------------------

      07-JUL-20 05.00.00 AM

       

      SQL>

       

      Success with 19.4 :

      SQL Dev success with SYS_EXTRACT_UTC.jpg

      Failure with 20.2 :

       

      SQL Dev failure with SYS_EXTRACT_UTC.jpg

      The error in 20.2  :

       

      ORA-00904: "SYS"."SYS_EXTRACT_UTC": invalid identifier

      00904. 00000 -  "%s: invalid identifier"

      *Cause:   

      *Action:

      Error at Line: 17 Column: 8

       

      Any advice on what to do here?

      Thanks,
      Danno_AECC

        • 1. Re: SQL Dev 20.2 raises error for SYS_EXTRACT_UTC call
          Glen Conway

          Well, your example, specifically the "to_timestamp(trunc(sysdate -3))" part of

          select sys_extract_utc(to_timestamp(trunc(sysdate -3))) from dual;

          fails for me on 19.4 also.  If I do a "show nls" in the worksheet I see that

          NLS_DATE_FORMAT rrrr-mm-dd hh24:mi:ss

          NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

          do not match up, which I think is required by the to_timestamp documentation:

          Capture1.JPG

          So just go to your preferences and make those two match and all will be fine. Here are my original settings, but after making "Timestamp Format" and "Date Format" the same, the SQL works fine.

          Capture.JPG

           

          Edit:  I have seen complaints about 20.2 not migrating all settings from prior releases.  That could be what is happening in your case, or perhaps you just made some change and forgot about it.

          • 2. Re: SQL Dev 20.2 raises error for SYS_EXTRACT_UTC call
            Danno_AECC

            You are absolutely correct. Thank you very much, Glen!

             

            Problem resolved.