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