New Features

PLSQL – ACCESSIBLE BY Clause

In 12c onwards, if you wanted to limit package A to only be accessible by package B.

PACKAGE package_A
ACCESSIBLE BY (package_B)
IS
PROCEDURE someProcedure;
END;

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

The DBMS_JOB package has been deprecated since 12cR2. Oracle 19c takes the demise of the DBMS_JOB package a step further by converting any DBMS_JOB jobs to DBMS_SCHEDULER jobs.

Pluggable databases are new features in Oracle 12c.
Alter session set container =pdb1;

Fetch FIRST 10 Rows ONLY.

New Feature of 12c to fetch Top-n rows.

SELECT first_name, last_name, date_of_birth
FROM student
ORDER BY date_of_birth
FETCH FIRST 10 ROWS ONLY

Create an Auto Increment Column 

CREATE TABLE idtest (
new_id NUMBER GENERATED AS IDENTITY,
first_name VARCHAR2(100)
last_name VARCHAR2(100)
);
or new_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,

In Oracle 12c, you can:

• Use sequences as the default value for a column
• Use default values only when the specified value is NULL

Before Oracle 12c, this had to be done using INSERT triggers.

CREATE SEQUENCE seq_test;

CREATE TABLE default_test (
seq_val NUMBER DEFAULT seq_test.NEXTVAL PRIMARY KEY,  <--
null_val NUMBER DEFAULT ON NULL 99                    <--
normal_val NUMBER
);

In Oracle 12c, you can take advantage of an increased column size for some data types.

The VARCHAR2, NVARCHAR2 and RAW data types allow for a larger number of characters than previous versions.

Data Type	Oracle 11g Limit	Oracle 11g PL/SQL Limit	Limit in Oracle 12c
VARCHAR2	2K	4K	32K
NVARCHAR2	2K	4K	32K
RAW	2K	4K	32K

How can you use these new maximum sizes?

You need to change a setting within the init.ora file. The init.ora file is Oracle’s initialization file, which is read every time the database starts up.

The setting is called MAX_STRING_SIZE. There are two values for this:

• STANDARD – The original sizes.
• EXTENDED – The new larger sizes.

Immutable Tables in Oracle Database 19c and 21c 

 An immutable table is a tamper-proof, insert-only table with an associated table-level and row-level retention period

create immutable table it_t1 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint it_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 365 days after insert;

SELECT row_retention,
       row_retention_locked, 
       table_inactivity_retention
FROM   user_immutable_tables 
WHERE  table_name = 'IT_T1';