Enhanced LISTAGG Oracle Database 12C release 2 (12.2)

In previous releases there was always problem with function LISTAGG. If list of concatenated expression exceeds maximum length supported by VARCHAR2 type, then ORA-01489 is raised.

In new version Oracle Database Release 12.2 addressed this issue by adding extra clause ON OVERFLOW TRUNCATE to hide this error. In my opinion it’s not full solution but in some cases can be useful.

LISTAGG ( [ALL] <measure_column> [,<delimiter>] 
 [ON OVERFLOW TRUNCATE [truncate_literal] [WITH | WITHOUT COUNT] | 
  ON OVERFLOW ERROR]) 
WITHIN GROUP (ORDER BY <oby_expression_list>)

I recommend to read following article as well:

String concatenation aggregation methods

Continue reading

Install Fedora 25

This article presents how to install Fedora 25 – Live Workstation version.

I assume you have already downloaded Fedora 25 64 bit Live Workstation(about 1,3 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Linux Fedora 64 bit. 2GB ram and 64G(includes extra space for Oracle installations) for disk is enough plus increase video memory to 128M. Rest of options you can keep default.

Continue reading

Oracle SecureFile LOBs in 11G – Oracle Database 11G release 2 (11.2)

This article describes new feature of 11g SecureFile LOB.

In new version 11g Oracle decided to create new type large objects (LOB) called Secure Files. It offers many new benefits versus old version of LOB called in 11g release BasicFile:

  • performance
  • intelligent compression
  • transparent decryption
  • easier management and development

Continue reading

ORA-22992, ORA-64202 cannot use LOB locators selected from remote tables

If you want try to select CLOB column via database link then you get error ORA-22992

select * from <TABLE_NAME>@<DATABASE LINK>;

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

This article shows how to avoid this error

Continue reading