Oracle 12.2 New Features for Developers – Conditional Compilation

Hi,

We continue this series of blogs on New Features for Developers with some updated conditional compilation features.

This feature allows for you to get rid of the upper and lower functions when comparing character strings to literals. You can have ‘Hotka’, ‘hotka’, and ‘HOTKA’ all stored in the database. It is common practice to use either the upper or the lower function when looking up my last name.

Oracle12.2 will allow for you to tell the database that these 3 storage options are all the same.

This is called column collation. This feature makes any data case and accent insensitive.

This feature also has to have the MAX_STRING_SIZE set to extended. Oracle has been talking about Oracle12 features for a long time. I discussed the options with this setting clear back in Sept 2013. Do remember, there is no going back to the standard setting once this is set to extended.

You can alter a table so that any future data that goes into character fields picks up the new collation feature. Existing data won’t be changed.

ALTER TABLE emp DEFAULT COLLATION BINARY_AI;
ALTER TABLE emp MODIFY (ename COLLATE BINARY_AI);

Now, you will not need to use the upper or lower function when comparing this column data to a literal or the contents of another column perhaps.

According to Chris’s presentation, when you alter the column, the data is immediately reflective of the new feature.

Oracle VP Tirthankar Lahiri on the Oracle12.2 release date: “Oracle is presenting features for Oracle database 12c Release 2 on Oracle cloud. Features and enhancements related to the on-premises versions of Oracle Database 12c Release 2 are not being announced at this time”.

Dan Hotka
Oracle ACE Director
Author/Instructor/CEO