Announcement

Collapse
No announcement yet.

ORA-20000: Undefined time zone for location

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORA-20000: Undefined time zone for location

    Hi all,

    Recently I asked to oracle a new DB user, to run a customize report. we already execute the query with other users and works fine, but with the new user doesn't work.
    We already grant appropriate privileges to the custom schema for execution of UTC, as well as underlying objects.

    But now we have the folowing error message:

    [I]ORA-20000: Undefined time zone for location *******
    ORA-06512: en "GLOGOWNER.UTC", l

  • #2
    Solved! The esquema/user needs to have granted the access and privileges to some extra tables like "VPD" and "TIME_ZONE_LOCALE"...
    If you have the same problem in future, try this configuration.

    Comment


    • #3
      Hi All

      Can anyone guide me on how to solve this issue.

      I am passing an event on Shipment tendered and Shi[ment tendered accepted. While I insert the record in IE_SHIPMENSTATUS table , its being punched in UTC timezone.However I am tryng to update the eventdate in IE_SHIPMENSTATUS table thorugh a DSU in agent as below :-

      UPDATE IE_SHIPMENTSTATUS
      SET EVENTDATE = $event_date - (utc.get_local_Date($event_date,
      (SELECT S.SOURCE_LOCATION_GID
      FROM SS_STATUS_HISTORY SS ,
      SHIPMENT S
      WHERE SS.SHIPMENT_GID = S.SHIPMENT_GID
      AND SS.I_TRANSACTION_NO = $I_TRANSACTION
      AND S.SHIPMENT_GID = $GID
      AND SS.DOMAIN_NAME = 'KNEU/VEDAN'
      )) - $event_date)
      WHERE
      SHIPMENT_GID = $GID
      AND STATUS_CODE_GID = 'KNEU/VEDAN.0141'


      now this insert stament returns an erro as below :-
      Agent KNEU/VEDAN.SHIPMENT_TENDERED_V02 failed

      [SQL_STATEMENT] UPDATE IE_SHIPMENTSTATUS SET EVENTDATE = ? - (utc.get_local_Date(?, (SELECT S.SOURCE_LOCATION_GID FROM SS_STATUS_HISTORY SS , SHIPMENT S WHERE SS.SHIPMENT_GID = S.SHIPMENT_GID AND SS.I_TRANSACTION_NO = ? AND S.SHIPMENT_GID = ? AND SS.DOMAIN_NAME = 'KNEU/VEDAN' )) - ?) WHERE SHIPMENT_GID = ? AND STATUS_CODE_GID = 'KNEU/VEDAN.0141' ; [2018-09-24 08:09:08.0, 2018-09-24 08:09:08.0, 345944191, KNEU/VEDAN.20180924-S-00004, 2018-09-24 08:09:08.0, KNEU/VEDAN.20180924-S-00004]

      java.sql.SQLException: ORA-20000: Undefined time zone for location ORA-06512: at "GLOGOWNER.UTC", line 26
      can anyone advise on this issue please.

      Comment


      • #4
        All events/dates will be stored in DB level as UTC time.
        Thanks,
        Vinoth Gopalakrishnan
        http://www.vinoth.co/
        Reach out for OTM/GTM - Transportation/Logistics and Blockchain Consultations/Strategy

        Comment


        • #5
          Answering assuming utc conversion is done based on some requirement(all dates DB are in utc)
          This Error is due to missing timezone or incorrect timezone for the location you are converting. you need to also check if your below inner query is returning any value.
          SELECT S.SOURCE_LOCATION_GID
          FROM SS_STATUS_HISTORY SS ,
          SHIPMENT S
          WHERE SS.SHIPMENT_GID = S.SHIPMENT_GID
          AND SS.I_TRANSACTION_NO = $I_TRANSACTION
          AND S.SHIPMENT_GID = $GID
          AND SS.DOMAIN_NAME = 'KNEU/VEDAN'

          Comment

          Working...
          X