• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: BUG

ORA12R2 – IMPdp may change segment_column_id

25 Monday May 2020

Posted by troroblog in BUG, impdp

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 2

This is a case we faced just a couple of days ago and somehow I cannot believe what I see.

We have a table containing a invisible column which was added to the table and therefor was placed at the end of the segment.


Testcase

drop table myemp;

create table myemp (empno    number(4)     not null
                   ,ename    varchar2(10)
                   ,job      varchar2(10)
                   ,mgr      number(4)
                   ,hiredate date
                   ,sal      number(6)
                   ,comm     number(4)
                   ,deptno   number(2));

alter table myemp add constraint myemp_pk primary key (empno);

Insert into MYEMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7369,'SMITH','CLERK',7902,date '1980-12-17',800,null,20);
...
commit;

alter table myemp add birthday date invisible;

update myemp set birthday = add_months(hiredate,-18);

commit;

col column_name format a15

select column_name
      ,hidden_column
      ,column_id
      ,segment_column_id
      ,internal_column_id
  from user_tab_cols
 where table_name = 'MYEMP';

COLUMN_NAME HID COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- --- --------- ----------------- ------------------
EMPNO       NO          1                 1                  1
ENAME       NO          2                 2                  2
JOB         NO          3                 3                  3
MGR         NO          4                 4                  4
HIREDATE    NO          5                 5                  5
SAL         NO          6                 6                  6
COMM        NO          7                 7                  7
DEPTNO      NO          8                 8                  8
BIRTHDAY    YES                           9                  9

9 rows selected.


expdp usr/pwd tables=MYEMP directory=EXP dumpfile=myemp.dmp logfile=myemp.log reuse_dumpfiles=yes

drop table myemp;

impdp usr/pwd tables=MYEMP directory=EXP dumpfile=myemp.dmp logfile=myemp.log

select column_name
      ,hidden_column
      ,column_id 
      ,segment_column_id 
      ,internal_column_id
  from user_tab_cols
 where table_name = 'MYEMP';

COLUMN_NAME HID COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- --- --------- ----------------- ------------------
BIRTHDAY    YES                           1                  1
EMPNO       NO          1                 2                  2
ENAME       NO          2                 3                  3
JOB         NO          3                 4                  4
MGR         NO          4                 5                  5
HIREDATE    NO          5                 6                  6
SAL         NO          6                 7                  7
COMM        NO          7                 8                  8
DEPTNO      NO          8                 9                  9

9 rows selected.

Notes to this behaviour:

  • Oracle 19 does it correct, segment_column_id, internal_column_id remains unchanged
  • It is a problem of impdp
    • I tried to import an Oracle 12 export using the import utility of Oracle 19 and it turned out ok
    • I tried to import an Oracle 19 export using the import utility of Oracle 12 and it turned out wrong
  • The data is correct
  • I have not tried with Oracle 18c

Is it a bug?

Yes it is in my opinion – I just have to convince Oracle Support of this. They were able to reproduce but offered a workaround … which is not really an option for us. We will move to Oracle 19c in the next couple of months and we have this problem in our development environment only but still I was really surprised facing something like this…

Update (May 27, 2020)

The problematic piece of code is not datapump but DBMS_METADATA which returns the columns in the wrong order.

Oracle has opened a new SR for this – I am keeping an eye on it.

ORACLE 18c – TO_UTC_TIMESTAMP_TZ

29 Saturday Feb 2020

Posted by troroblog in BUG, ORACLE 18c

≈ Leave a comment

Tags

ORACLE, SQL

TO_UTC_TIMESTAMP_TZ takes a ISO8601 formatted date string and converts it to a TIMESTAMP WITH TIMEZONE datatype.

According to documentation this is the ebnf of to_utc_timestamp_tc

to_utc_timestamp_tz

The varchar parameter may be either a date (only) or a date with time combination. The format of the parameter is:

Date only:

YYYY-MM-DD

Date / time:

YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]]][Z|(+|-)hh:mm]

Example

with data (d) as (select '2020-02-28T21:32:52'       from dual union all
                  select '2020-02-28T21:32:52+02:00' from dual union all
                  select '2020-02-28T19:32:52Z'      from dual union all
                  select '2020-02-28T19:32:52+00:00' from dual union all
                  select '2020-02-28T21:32:52.12679' from dual union all
                  select '2020-02-28')
select to_utc_timestamp_tz(d) as converted
  from data;

CONVERTED 
---------------------------------------------
28.02.2020 21:32:52.000000000 GMT
28.02.2020 21:32:52.000000000 +02:00
28.02.2020 19:32:52.000000000 GMT
28.02.2020 19:32:52.000000000 GMT
28.02.2020 21:32:52.126790000 GMT
28.02.2020 00:00:00.000000000 GMT

6 rows selected.

DEFAULT RETURN VALUE ON CONVERSION ERROR

As most of the other conversion functions documentation says that TO_UTC_TIMESTAMP_TZ supports the on conversion error clause too. But when trying to use it you will get an error.

with data (d) as (select '2018-10-26T21:32:52'       from dual union all
                  select '2018-10-26T21:32:52+02:00' from dual union all
                  select '2018-10-26T19:32:52Z'      from dual union all
                  select '2018-10-26T19:32:52+00:00' from dual union all
                  select '2018-10-26T21:32:52.12679' from dual)
select to_utc_timestamp_tz(d default null on conversion error) converted
  from data;

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause: 
*Action:
Error at Line: 11 Column: 30

Documentation Bug

I have filed an SR with Oracle Support regarding this error. It has been classified as documentation bug:

DOC 30936692 – TAKE OUT “[ DEFAULT RETURN_VALUE ON CONVERSION ERROR ]” OPTIONAL CLAUSE FROM TO_UTC_TIMESTAMP_TZ

and will be removed from future (>20) documentations.


Conclusion

Somehow I hopped, that Oracle would add the missing functionality concerning the conversion error handling to the TO_UTC_TIMESTAMP_TZ function rather than declaring it as a documentation bug. 

Not having a decent way to handle exceptions (besides building a user defined function with an exception handler) will keep me away from using this function.

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...