Note:

Explore SQL Features in Oracle Database 23ai

Introduction

We will learn 10 features in this tutorial that you need to know and how they compare to their existing counterparts. These features are:

Objectives

Prerequisites

Feature 1: Use FROM Clause

An interesting feature introduced in Oracle Database 23ai is optionality of FROM clause in SELECT statements. Up to this version the FROM clause was required.

Here are some potential benefits of a SELECT without FROM feature in Oracle Database 23ai.

Feature 2: Use BOOLEAN Datatype

Oracle Database 23ai introduces the new BOOLEAN data type. This leverages the use of true boolean columns/variables, instead of simulating them with a numeric value or Varchar. The ability to write boolean predicates simplifies the syntax of SQL statements.

  1. Create a table called TEST_BOOLEAN.

    CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN);

Table TEST_BOOLEAN created.

Elapsed: 00:00:00.004

  2. Enter data into the new table. The value IS_SLEEPING will be NOT NULL set to FALSE as default.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    Image showing how to use Boolean for SQL 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    Here, you can see the different types of boolean input for Mick, Keith, and Ron. All are valid. For Mick, the default FALSE value is used - Mick is not sleeping.

    1 row inserted.

Elapsed: 00:00:00.006

    For Keith, we use a NO value - Keith is not sleeping.

    INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');

1 row inserted.

Elapsed: 00:00:00.002

    And for Ron we use a 1 value - Ron is sleeping.

  3. See some results based on our boolean values.

    SELECT * FROM test_boolean;

    You no longer need to remember what kind of boolean system you put in place. As we have shown, using 0/1, True/False, Yes/No, or any other common inputs will return an accurate table value.

Feature 3: Use IF NOT EXISTS DDL Clause

Starting with Oracle Database 23ai, the new IF NOT EXISTS DDL clause allows to decide how DDL errors will be handled. This simplifies the DDL scripting, as potential errors due to objects existence or inexistence can be hidden to the scripting.

  1. First, test without using this new feature. Run the following statement.

    DROP TABLE DEPT;

    Since there is no existing DEPT table to drop, we will see an error: ORA-00942: table or view does not exist.

  2. However, in Oracle Database 23ai, we are able to use DROP IF EXISTS without an error. This gives us peace of mind while avoiding errors. Now, run the same statement, but include this new IF EXISTS feature.

    DROP TABLE IF EXISTS DEPT;

  3. Similarly, we can use this feature to create tables, if they do not already exist. Create that DEPT table.

    CREATE TABLE IF NOT EXISTS DEPT
	(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

    Image showing how to use IF [NOT] EXISTS DDL clause

  4. Use this feature to create more sample tables throughout this tutorial. Here, we will make an employee table called EMP.

    CREATE TABLE IF NOT EXISTS EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table EMP created.

Elapsed: 00:00:00.006

Feature 4: Use INSERT for Multiple Rows

Another interesting feature ensuring better coexistence and compatibility with other frequently used database management systems is multivalue INSERT statement.

  1. In previous versions of Oracle database, as an example, inserting multiple rows required a separate insert statement for each row.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES');
INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO');
INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');

    Oracle Database 23ai introduced the new syntax allowing for inserting all these rows in a single one INSERT statement, so you may insert several tuples in one DML. Run the following statement.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON'),
(50,'HR','LOS ANGELES'),
(60,'IT','SAN FRANCISCO'),
(70,'MANUFACTURING','DETROIT');

    Image showing how to use Multivalue INSERTs

    In addition to better compatibility with other databases, this statement can be used to ensure consistency of some insert operations in auto-commit mode. This could be important, for example, for Oracle APEX applications using this mode to work on data.

  2. Run the following statement to use this feature to fill in values for the EMP table.

    INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30),
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30),
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30),
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30),
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30),
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20),
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30),
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20),
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

Feature 5: Use New VALUE Constructor

Starting from Oracle database 23ai, table values constructor has been extended. It can now be used in INSERT statements, to create several rows in a single command. It can also be used in SELECT statements, and in the view factorization syntax. In this last case, it simplifies the syntax of the statements, and avoids using the DUAL table.

The following statement looks like a kind of table function on the fly.

SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);

Image showing how to use New table value constructor

Feature 6: Use Aliases in GROUP BY Clause

Oracle Database 23ai introduces the ability to use aliases in GROUP BY clause of a SELECT statement. This feature simplifies writing queries with complex expressions as well as ensures better compatibility with some other relational databases, like Teradata, MySQL and PostgreSQL.

For example:

SELECT to_char(hiredate,'YYYY') "Year", count(*)
FROM emp
GROUP BY to_char(hiredate,'YYYY');

In Oracle Database 23ai this can be written in a simpler way as shown in the following image:

Image showing how to use Aliases in GROUP BY clause

Feature 7: Use RETURNING Clause of UPDATE and MERGE Statement

This clause had been implemented earlier as a part of the EXECUTE IMMEDIATE statement. However in Oracle Database 23ai we can find it as a part of traditional, static DML statements.

  1. In this case, it allows for obtaining old and new values of columns from a row processed. First, let us see King’s current salary.

    SELECT ename, sal FROM emp WHERE ename = 'KING';

  2. To use variables in LiveSQL, we will wrap out statements in PL/SQL. Run this script. It will first create the variables for old and new salary, then update King’s salary using the RETURNING clause to set our variables. We will then take a look at the results.

    BEGIN
	DECLARE
		old_salary NUMBER;
		new_salary NUMBER;
	BEGIN
		UPDATE emp
		SET sal = sal + 1000
		WHERE ename = 'KING'
		RETURNING OLD sal, NEW sal INTO old_salary, new_salary;
		DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary);
		DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary);
	END;
END;

    Image showing how to use RETURNING clause of UPDATE and MERGE statement 

    Old Salary: 6000
New Salary: 7000

    This example used an UPDATE statement, but the RETURNING clause can be used similarly with MERGE statements.

Feature 8: Use Joins in UPDATE and DELETE

You may update table data using joins based on foreign table conditions. There is no need for sub selects or IN clause.

  1. Run the following statement to see the employee salary information from the research department.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

  2. To update the salary information, prior to Oracle Database 23ai we need to use a nested statement.

    UPDATE emp e set e.sal=e.sal*2
WHERE e.deptno in
(SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno
and d.dname='RESEARCH');

    With Oracle Database 23ai, you can use it like this:

    UPDATE emp e set e.sal=e.sal*2  FROM dept d  WHERE e.deptno=d.deptno  and d.dname='RESEARCH';

    Image showing how to use Joins in UPDATE and DELETE

  3. You can see the salary has been successfully updated.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

    Image showing how to use Joins in UPDATE and DELETE

Feature 9: Use Annotations

Annotations are optional metadata for database objects. An annotation is either a name-value pair or a name by itself. The name and optional value are freeform text fields. An annotation is represented as a subordinate element to the database object to which the annotation has been added. Supported schema objects include tables, views, materialized views, and indexes. With annotations you may store and retrieve metadata about a database objects. You can use it to customize business logic, user interfaces or provide metadata to metadata repositories. It can be added with CREATE or ALTER statement on table or column level.

With annotations you may store and retrieve metadata about database objects. You can use it to customize business logic, user interfaces or provide metadata to metadata repositories.

  1. Create an annotated table EMP_ANNOTATED_NEW with column and table annotations.

    CREATE TABLE emp_annotated_new
	(empno number annotations(identity, display 'person_identity', details 'person_info'),
	ename varchar2(50),
	salary number annotations (display 'person_salary', col_hidden))
	annotations (display 'employee_table');

    Image showing how to use Annotations, new metadata for database objects

  2. Data dictionary views such as USER_ANNOTATIONS and USER_ANNOTATIONS_USAGE can help to monitor the usage.

    SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;

    Image showing how to use Annotations, new metadata for database objects

Feature 10: Use SQL Domains

A SQL domain is a dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints and is created with a CREATE DOMAIN statement. Domains provide constraints, display, ordering and annotations attributes. After you define a SQL domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain’s optional properties and constraints to those columns.

SQL domains allow users to declare the intended usage for columns. They are data dictionary objects so that abstract domain specific knowledge can be easily reused.

  1. Create a domain named yearbirth and table named person.

    CREATE DOMAIN yearbirth as number(4)
constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900))
display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100)
order (yearbirth -1900)
annotations (title 'yearformat');

    Image showing how to use Light weight object types with SQL Domains

    CREATE TABLE person
(id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');

    Image showing how to use Light weight object types" with SQL Domains

    ``` desc person;

    Name Null? Type

ID NUMBER(5) NAME VARCHAR2(50) SALARY NUMBER PERSON_BIRTH NUMBER(4) DOMAIN YEARBIRTH

INSERT INTO person values (1,’MARTIN’,3000, 1988);

2. With the new function `DOMAIN_DISPLAY` you can display the property.

SELECT DOMAIN_DISPLAY(person_birth) FROM person;


   ![Image showing how to use Light weight object types" with SQL Domains ](./images/Picture14.png "Light weight object types with SQL Domains")

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.

SELECT * FROM user_annotations_usage;


   Define reusable domain types (lightweight objects).

CREATE DOMAIN EmailAddress AS VARCHAR2(100) CHECK (REGEXP_LIKE(VALUE, ‘^[^@]+@[^@]+.[^@]+$’));

CREATE TABLE users ( user_id NUMBER, email EmailAddress ); ```

Image showing how to use Light weight object types" with SQL Domains

Limitations and Restrictions in Oracle Database 23ai Features

General Restrictions

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.