oracle_bugs_wrong_hour_extract [My wiki]

Wrong function extract

Very interesting “Feature” which I was shown in oracle is the function “extract hour from timestamp”, it appears that Oracle will provide an hour which is wrong:



select (extract (hour from current_timestamp)),current_timestamp from dual;

(EXTRACT(HOURFROMCURRENT_TIMESTAMP)) CURRENT_TIMESTAMP
------------------------------------ -------------------
9                                    2017-11-14 10:44:00

Solution This behavior is somehow normal however, since the function: extract (hour from current_timestamp) is supposed to return you the hour in UTC format, excluding the timezone, therefore if the hour is 18:00 and the timezone is +02:00 it will return you 16:00, instead of 18:00. There are 2 ways how to negate this:

Use the current_scn:

SQL> select (extract (hour from (scn_to_timestamp(current_scn)))) from v$database;

(EXTRACT(HOURFROM(SCN_TO_TIMESTAMP(CURRENT_SCN))))
--------------------------------------------------
                                                17

SQL>

Use the sum of the UPC hour and the time zone:

  * Unordered List Item
SQL> Select (extract (HOUR from current_timestamp)), current_timestamp from dual; -------- We will extract the UTC value for hour 

SQL> Select (extract (HOUR from current_timestamp)) UTC_HOUR, (extract (TIMEZONE_HOUR from current_timestamp)) SESSION_TZ, (extract (HOUR from current_timestamp))+(extract (TIMEZONE_HOUR from current_timestamp)) SESSION_HOUR ,current_timestamp from dual;
  UTC_HOUR SESSION_TZ SESSION_HOUR CURRENT_TIMESTAMP
---------- ---------- ------------ ---------------------------------------------------------------------------
        16          1           17 16-NOV-17 17.26.55.236687 +01:00

Object exists but cannot be replaced

There is a strange phenomena on Oracle, when you try to re-create a procedure which points to a synonym. For example, if you have a schema, which has a synonym pointing to a procedure of other schema and you try to create a new procedure under this user with the same name as the existing procedure you get:

EURAGIN > @PK_SINGLE_WALLET.sql
13:53:17 415  /
CREATE OR REPLACE PACKAGE UAGVDEIO.PK_SINGLE_WALLET AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Even though I specify the “OR REPLACE” clause, Oracle still fail. That was very mind itching problem. So let's see who has what:

OWNER                          STATUS  CREATED             OBJECT_TYPE
------------------------------ ------- ------------------- -------------------
UAGPLZ2017                     VALID   2018-05-10 11:19:47 SYNONYM
UAGUWB2017                     VALID   2018-05-10 11:20:00 SYNONYM
UAGSWL2017                     VALID   2018-05-10 11:20:43 SYNONYM
UAGNP17UCWB                    VALID   2018-05-10 11:21:35 SYNONYM
UAGINDBM                       VALID   2018-05-10 11:24:32 PACKAGE BODY
UAGINDBM                       VALID   2018-05-10 11:22:25 PACKAGE       <- 3rd Schema
UAGVDEIO                       VALID   2018-05-10 11:24:36 SYNONYM
AGVDEIO                        VALID   2019-03-14 13:42:08 SYNONYM

So the ACTUAL package is owned by 3RD SCHEMA :) and the others are synonyms. So we have to just add the it to the correct schema:

14:07:59 SYS @ EURAGIN > @PK_SINGLE_WALLET.sql
14:08:05 415  /

Package created.

Elapsed: 00:00:00.56
14:08:06 SYS @ EURAGIN > @PK_SINGLE_WALLET_body.sql
14:08:10 1012  /

Package body created.

Elapsed: 00:00:00.40
14:08:11 SYS @ EURAGIN >