This article presents how to quickly install Oracle Database 12C Release 2 (12.2.0.1) on Windows.

Software used:

Binaries Database 12C Release 2(12.2.0.1)

winx64_12201_database.zip - database software

Continue reading →

In Oracle 12C it’s possible to assign a role to a code procedure/function/package

GRANT role_name TO PROCEDURE|FUNCTION|PACKAGE code_name;

This kind of grant can be executed only by following user:

• SYS
• user with GRANT ANY ROLE privilege
• user who own code and was granted the role with ADMIN OPTION
• user who own code and was granted the role with DELEGATE OPTION

Top three options are commonly know. New option is DELEGATE OPTION. It enables to grant a role to procedure|function|package and nothing more.

Once the role is granted to a code the role is always enabled no matter if the code is created with PRAGMA AUTHID_USER or PRAGMA DEFINER and no matter if owner of the code calls it or other user.

Continue reading →

This article describes new options available in Oracle Database Release 12.2 for partitioning:

• extensions for list partitioning
  • automatic list partitioned table
  • multi-column list partitioned table
• read-only partitions and subpartitions
• filtering maintenance operations
• creating a table for exchange with a partitioned table
• online converting non-partitioned table to a partitioned table
• online split partition and subpartition

Continue reading →

This article presents installation of Oracle database 12C(12.2) on Fedora 25. Read following article to install Fedora 25 Linux: Install Fedora 25(for comfort set 4G memory for your virtual machine).

Software

Software for 12CR2 is available on OTN or edelivery

• OTN: Oracle Database 12c Release 2 (12.2.0.1) Software (64-bit)
• edelivery: Oracle Database 12c Release 1 (12.2.0.1) Software (64-bit)

Database software

linuxx64_12201_database.zip

Continue reading →

This article presents how to install Oracle 12C(12.2) Release 2 on Oracle Enterprise Linux 7 (OEL7).

Read following article how to install Oracle Enterprise Linux 7: Install Oracle Linux 7 (OEL7) (for comfort set 4G memory for your virtual machine before proceeding with Oracle software installation).

Software

Software for 12CR2 is available on OTN or edelivery

• OTN: Oracle Database 12c Release 2 (12.2.0.1) Software (64-bit)
• edelivery: Oracle Database 12c Release 1 (12.2.0.1) Software (64-bit)

Database software

linuxx64_12201_database.zip

Continue reading →

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 →

New function VALIDATE_CONVERSION has been introduced in Oracle Database Release 12.2.It determines wether a given expression can be converted to the requested data type. It can return:

• 1 – if conversion is possible or expression evaluates to NULL
• 0 – if conversion is not possible
• Error is expression returns error

Continue reading →

CAST function in release 12.2 enables to add error handler DEFAULT return_value ON CONVERSION ERROR in case conversion fails.

Continue reading →

New release 12.2 introduces possibility to generate long identifiers up to 128 characters for name like tables, views, triggers, columns and so on. In previous release it was limited to 30 characters.

Continue reading →