/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 2
ORACLE 23AI SQL ENHANCEMENTS
=================================================================
Oracle 23ai introduces several SQL improvements aimed at:
β’ Simplifying syntax
β’ Improving compatibility with SQL standards
β’ Enhancing analytic capabilities
β’ Improving performance of queries
Topics Covered in This Section
1. No need for DUAL table
2. FETCH FIRST row limiting
3. LATERAL joins
4. REGEXP enhancements
5. Analytic window functions
6. GROUP BY expression improvements
7. PIVOT and UNPIVOT operations
8. Date and Time enhancements
9. String manipulation improvements
===============================================================*/
/*===============================================================
1. NO NEED FOR DUAL TABLE
===============================================================
In earlier Oracle versions:
SELECT SYSDATE FROM DUAL;
Oracle 23ai allows system expressions without referencing DUAL.
This simplifies queries and improves SQL readability.
===============================================================*/
/* OLD STYLE */
SELECT SYSDATE FROM DUAL;
/* ORACLE 23AI STYLE */
SELECT SYSDATE;
/* Other examples */
SELECT USER;
SELECT CURRENT_TIMESTAMP;
SELECT SESSIONTIMEZONE;
/*===============================================================
2. FETCH FIRST ROW LIMITING
===============================================================
Older Oracle versions used:
ROWNUM
or complex subqueries.
Oracle 23ai supports SQL standard syntax.
FETCH FIRST N ROWS ONLY
===============================================================*/
/* Return top 3 employees */
SELECT *
FROM employees_ai
FETCH FIRST 3 ROWS ONLY;
/* Return top salaries */
SELECT employee_id, salary
FROM employees_ai
ORDER BY salary DESC
FETCH FIRST 2 ROWS ONLY;
/* Pagination Example */
SELECT *
FROM employees_ai
ORDER BY employee_id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY;
/*===============================================================
3. LATERAL JOIN
===============================================================
LATERAL allows a subquery to reference columns
from the outer query.
This enables powerful correlated subqueries.
Syntax
FROM table1,
LATERAL(subquery)
===============================================================*/
/* Create departments table */
DROP TABLE departments_ai PURGE;
CREATE TABLE departments_ai
(
department_id NUMBER,
department_name VARCHAR2(50)
);
INSERT INTO departments_ai VALUES (10,'SALES');
INSERT INTO departments_ai VALUES (20,'IT');
INSERT INTO departments_ai VALUES (30,'HR');
COMMIT;
/* Example using LATERAL */
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees_ai e,
LATERAL
(
SELECT department_name
FROM departments_ai d
WHERE d.department_id = e.department_id
) d;
/*===============================================================
4. REGULAR EXPRESSION ENHANCEMENTS
===============================================================
Oracle supports powerful pattern matching
using REGEXP functions.
Key functions
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
REGEXP_INSTR
===============================================================*/
/* Replace word */
SELECT REGEXP_REPLACE('Hello World','World','Oracle')
AS replaced_text
FROM DUAL;
/* Extract numbers */
SELECT REGEXP_SUBSTR('Order12345','[0-9]+')
AS extracted_number
FROM DUAL;
/* Pattern match */
SELECT first_name
FROM employees_ai
WHERE REGEXP_LIKE(first_name,'^J');
/*===============================================================
5. ANALYTIC WINDOW FUNCTIONS
===============================================================
Analytic functions allow calculations
across rows without collapsing results.
Examples
RANK
ROW_NUMBER
DENSE_RANK
LAG
LEAD
SUM OVER
===============================================================*/
/* Ranking employees by salary */
SELECT
employee_id,
salary,
RANK() OVER
(
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees_ai;
/* Row numbering */
SELECT
employee_id,
ROW_NUMBER() OVER
(
ORDER BY salary DESC
) AS row_num
FROM employees_ai;
/* LAG function (previous salary) */
SELECT
employee_id,
salary,
LAG(salary,1,0) OVER
(
PARTITION BY department_id
ORDER BY hire_date
) AS previous_salary
FROM employees_ai;
/* LEAD function (next salary) */
SELECT
employee_id,
salary,
LEAD(salary,1,0) OVER
(
PARTITION BY department_id
ORDER BY hire_date
) AS next_salary
FROM employees_ai;
/*===============================================================
6. GROUP BY EXPRESSION ENHANCEMENTS
===============================================================
Oracle 23ai allows grouping by expressions.
Example grouping salaries by ranges.
===============================================================*/
SELECT
FLOOR(salary/1000)*1000 AS salary_range,
COUNT(*) employee_count
FROM employees_ai
GROUP BY FLOOR(salary/1000)*1000
ORDER BY salary_range;
/*===============================================================
7. PIVOT OPERATION
===============================================================
PIVOT converts rows into columns.
Used heavily in reporting and BI.
===============================================================*/
SELECT *
FROM
(
SELECT year,quarter,revenue
FROM financial_data
)
PIVOT
(
SUM(revenue)
FOR quarter
IN
(
'Q1' AS Q1,
'Q2' AS Q2,
'Q3' AS Q3,
'Q4' AS Q4
)
);
/*===============================================================
8. UNPIVOT OPERATION
===============================================================
UNPIVOT converts columns back into rows.
Useful for normalization and data reshaping.
===============================================================*/
SELECT year,quarter,revenue
FROM financial_data
UNPIVOT
(
revenue
FOR quarter
IN
(
Q1,Q2,Q3,Q4
)
);
/*===============================================================
9. STRING FUNCTION ENHANCEMENTS
===============================================================
REPLACE
TRANSLATE
REGEXP functions
===============================================================*/
SELECT REPLACE('Oracle Database','Database','23ai')
FROM DUAL;
SELECT TRANSLATE('123ABC','123','###')
FROM DUAL;
/*===============================================================
10. DATE AND TIME ENHANCEMENTS
===============================================================
Oracle supports powerful timezone operations.
===============================================================*/
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Kolkata';
/* Date arithmetic */
SELECT
SYSDATE,
SYSDATE + 7 AS next_week,
SYSDATE - 30 AS last_month
FROM DUAL;
/*===============================================================
END OF PART 2
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/