Oracle Database 23ai is the newest release from Oracle, designed to handle modern data challenges, AI-powered workloads, and advanced analytics. This page brings together all Oracle Database 23ai tutorials and practical guides. More resources will be added over time to help you explore both database administration and developer-focused features.
As Oracle Database 26ai is now available, all examples shared here have also been validated on 26ai and include coverage of its enhanced capabilities, ensuring that you gain forward-compatible knowledge with the newest AI-powered features.
Section 1: Getting Started with Oracle Database 23ai
1. What is Oracle Database 23ai?
Learn what Oracle Database 23ai is, its release details, and key differences from earlier versions. This guide explains new AI-driven features, JSON and vector support, SQL Firewall, performance improvements, and why Oracle 23ai matters for developers and businesses.
2. Install Oracle Database 23ai on Windows
Provides a step-by-step guide to download, install, and configure Oracle Database 23ai on a Windows system. It explains the setup process clearly, including memory configuration, user accounts, and basic environment preparation for development or testing.
3. Install Oracle SQL Developer on Windows
Shows how to set up Oracle SQL Developer, the essential client tool for connecting to Oracle databases. The guide covers installation, initial configuration, and how to create connections with your Oracle Database 23ai instance for managing objects and running SQL queries.
4. Oracle Database 26ai New Features
Discover the major new features in Oracle Database 26ai: from AI-native vector search, JSON-relational duality and JavaScript stored procedures, to microservice messaging support and enhanced SQL & PL/SQL capabilities.
Section 2: Oracle Database 23ai SQL Basics
1. Create a Table in Oracle 23ai
Learn how to create tables in Oracle Database 23ai with SQL syntax, constraints, identity columns, partitions, and new features like vector datatype and blockchain tables. Step-by-step examples make it easy to build robust database structures.
2. Insert Data into Oracle 23ai Table
Learn step-by-step how to insert data into Oracle Database 23ai tables with real examples. Covers single row, multirow, JSON, and vector inserts, along with commits, bulk operations, error logging, and best practices to ensure accuracy and efficiency in modern applications.
3. Using SELECT Statement in Oracle 23ai
Master SQL SELECT statements in Oracle Database 23ai with this detailed tutorial. Explore step-by-step examples, learn to query data effectively, use analytic functions, and work with JSON for efficient data retrieval and analysis.
4. Using UPDATE Statement in Oracle 23ai
Learn how to use the UPDATE statement in Oracle Database 23ai with clear SQL examples. This tutorial covers updating single or multiple columns, applying conditions, using subqueries, working with CASE expressions, and exploring new 23ai features like JSON updates and vector data handling.
5. Using DELETE Statement in Oracle 23ai
Master the SQL DELETE statement in Oracle Database 23ai with this step-by-step guide. Learn how to remove single or multiple rows, use WHERE and EXISTS conditions, safely clear entire tables, and handle JSON or vector data updates while maintaining data integrity.
6. Using MERGE Statement in Oracle 23ai
Learn how to use the MERGE statement in Oracle Database 23ai to efficiently insert or update rows in a single command. This guide covers examples with employees, subqueries, JSON data, and vector embeddings, along with best practices to keep your data consistent and optimized.
7. Data Types in Oracle Database 23ai
Explore data types in Oracle Database 23ai with clear explanations and SQL examples. Learn about character, numeric, date, LOB, JSON, VECTOR, and user-defined types, plus how to store, update, and query modern data like embeddings using Oracle’s powerful new features.
8. WHERE Clause in Oracle Database 23ai
Master the WHERE clause in Oracle Database 23ai with step-by-step examples. Filter rows using numbers, text, dates, ranges, NULL checks, subqueries, and advanced JSON and vector filters. Build precise and efficient queries to handle both traditional and AI-driven data.
9. GROUP BY in Oracle Database 23ai
Learn how to use the GROUP BY clause in Oracle Database 23ai. This detailed tutorial explains grouping rows, using aggregate functions, applying HAVING, working with multiple columns, and performing advanced JSON and vector grouping for powerful SQL data analysis.
10. ORDER BY in Oracle Database 23ai
Learn how to use the ORDER BY clause in Oracle Database 23ai with step-by-step SQL examples. Sort rows by numbers, text, dates, expressions, and NULL values, plus advanced ordering by JSON fields and vector similarity. Master ORDER BY for clear and AI-driven query results.
Section 3: Oracle Database 23ai SQL Advanced Tutorial
1. SQL Query Calculate Business Days Excluding Holidays
Learn how to calculate business days in Oracle SQL with practical examples. This step-by-step guide covers date functions, weekends, holidays, and working-day logic to help you write efficient queries for real-world scenarios.
2. SQL Query to Find Overlapping Date Ranges
Discover how to detect overlapping date ranges in Oracle SQL with practical examples. Learn simple and efficient methods including self-joins, EXISTS, window functions, cross-table joins, business day overlaps, indexing strategies, and stored procedure implementations.
3. SQL Query to Implement Cohort Analysis
Implement customer retention cohort analysis using Oracle SQL. Learn how to group users by acquisition period, calculate monthly retention rates, analyze revenue and engagement, and optimize performance with real SQL examples for data-driven retention insights.
4. SQL Query to Identify and Handle Duplicate Records
Discover how to identify and manage duplicate records in Oracle SQL. Learn practical techniques using GROUP BY, window functions like ROW_NUMBER(), EXISTS, and ROWID methods. Includes deletion strategies, prevention via constraints and triggers, plus performance tips.
5. SQL Query to Parse and Extract JSON Data
Learn how to parse and extract JSON data in Oracle using JSON_TABLE. This practical guide covers mapping nested objects, handling arrays, extracting defined columns, using ORDINALITY, error handling, indexing for performance, and integrating JSON with relational data effectively.
6. SQL Query to Calculate Churn Rate
Master churn rate and retention metric calculations using SQL. Learn cohort segmentation, active customer analysis, revenue and subscription decay, time-series tracking, automated reporting, and performance optimization for clear customer-focused insights.
7. SQL Query to Perform ABC Analysis
Learn how to perform ABC inventory analysis in Oracle SQL. Use window functions to calculate annual value, cumulative percentages, and categorize items into A/B/C. Includes multi-criteria enhancements, SQL examples, summary stats, and automation techniques.
8. SQL Query to Calculate Running Totals
Master running totals in Oracle SQL using window functions like SUM() OVER(), partitioning, custom frame clauses, conditional logic, and alternative methods such as self-joins. Learn performance optimization, indexing, materialized views, and real-world use cases.
9. SQL Query to Find Gap in Sequential Data
Learn how to identify gaps (missing sequences) and islands (continuous runs) in Oracle SQL. This guide uses window functions like LAG, ROW_NUMBER() OVER, LEAD, and CTEs to find missing date or numeric ranges, spot consecutive sequences, and optimize performance.
10. SQL Query to Use LISTAGG for String Aggregation
Master the Oracle SQL LISTAGG function for string aggregation. Learn syntax, delimiters, ordering, DISTINCT, overflow control, and performance tips with real examples. Includes fallback methods like XMLAGG and automation techniques for efficient string handling.
11. SQL Inner Join
Comprehensive Oracle SQL INNER JOIN guide. Learn syntax, use-case scenarios, join conditions, table aliases, and performance best practices. Includes examples using sample tables, multi-table joins, aggregate joins, common pitfalls, and optimization tips for clean, efficient queries.
12. SQL Left Outer Join
Master Oracle SQL LEFT OUTER JOIN operations. Learn basic and advanced syntax, practical examples, handling unmatched records, NULL substitution, multiple-table joins, performance optimization, and real-world usage scenarios for robust data retrieval.
13. SQL Full Outer Join
Master Oracle SQL FULL OUTER JOIN for complete data reconciliation. Learn syntax basics, full vs. full outer joins, real-world examples, result interpretation, NULL handling, performance tips, and best practices for identifying mismatches across datasets.
14. SQL Explain Plan
Learn how to use Oracle SQL’s EXPLAIN PLAN to analyze execution paths. Understand how to set up PLAN_TABLE, interpret operation steps, costs, join methods, query DBMS_XPLAN, and optimize SQL performance effectively.
Section 4: Oracle Database 23ai PL/SQL Basics
1. Hello World Program in Oracle 23ai PL/SQL
Discover how to create a Hello World program in Oracle Database 23ai PL/SQL. From simple DBMS_OUTPUT printing to variables, exception handling, JSON objects, and vector usage, this tutorial covers the essential first steps for learning PL/SQL in Oracle 23ai.
2. Variables in Oracle 23ai PL/SQL
Learn how to declare and use variables and constants in Oracle 23ai PL/SQL. Covers initialization, NOT NULL rules, %TYPE, %ROWTYPE, JSON and VECTOR variables, native BOOLEAN in SQL, and best practices to write robust, AI-ready PL/SQL programs.
3. IF END IF in Oracle 23ai PL/SQL
Master IF statements in Oracle Database 23ai PL/SQL with step-by-step examples. Learn simple conditions, nested checks, JSON integration, vector similarity handling, and real-world business scenarios to make your programs dynamic and intelligent.
4. CASE Statement in Oracle 23ai PL/SQL
Master CASE statements in Oracle Database 23ai PL/SQL with step-by-step examples. Learn simple and searched CASE usage in SQL and PL/SQL blocks, work with JSON fields, handle vectors, and apply real-world business rules for more efficient and cleaner conditional logic.
5. LOOP in Oracle 23ai PL/SQL
Learn how to use loops in Oracle Database 23ai PL/SQL with practical examples. Explore LOOP, WHILE, FOR, reverse, and nested loops, plus new Oracle 23ai features like JSON iteration and vector similarity handling for building efficient and modern database applications.
6. CUSOR in Oracle 23ai PL/SQL
Learn how to use cursors in Oracle Database 23ai PL/SQL with sample data and examples. Explore implicit and explicit cursors, cursor FOR loops, parameterized cursors, JSON processing, and vector similarity handling to process multi-row results efficiently in modern PL/SQL applications.
7. Handling Errors in Oracle 23ai PL/SQL
Learn how to handle exceptions in Oracle Database 23ai PL/SQL with detailed examples. Cover NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, custom exceptions, JSON errors, and vector mismatches. Improve reliability with structured error handling and logging.
8. STORED PROCEDURES in Oracle 23ai PL/SQL
Master writing procedures in Oracle Database 23ai PL/SQL with detailed syntax, IN/OUT parameter examples, error handling, JSON integration, and vector search usage. A complete tutorial for developers to build efficient and modern database applications.
9. FUNCTIONS in Oracle 23ai PL/SQL
Learn to create functions in Oracle Database 23ai PL/SQL with detailed syntax, examples, IN parameters, error handling, JSON integration, and vector similarity search. A complete guide for developers to build reusable logic and AI-powered solutions in Oracle 23ai.
10. PACKAGES in Oracle 23ai PL/SQL
Learn how to create and use packages in Oracle Database 23ai PL/SQL. This guide covers package syntax, procedures, functions, IN/OUT parameters, error handling, JSON integration, and vector search examples for building modern and reusable database applications.
11. EXPLICIT CURSOR in Oracle 23ai PL/SQL
Learn how to use explicit cursors in PL/SQL with Oracle Database 23ai. This guide covers cursor syntax, parameterized cursors, attributes, exception handling, JSON data iteration, and vector similarity queries. A complete tutorial for database developers.
12. TRIGGERS in Oracle 23ai PL/SQL
Master Oracle Database 23ai PL/SQL triggers with this complete guide. Explore BEFORE, AFTER, INSTEAD OF, and compound triggers with real examples. Learn how to create, test, and manage triggers for auditing, data validation, and business rules in Oracle 23ai.
Basic Programs in PL/SQL
PL/SQL Program To Add Two Numbers
PL/SQL Program for Prime Number
PL/SQL Program to Find Factorial of a Number
PL/SQL Program to Print Table of a Number
PL/SQL Program for Reverse of a Number
PL/SQL Program for Fibonacci Series
PL/SQL Program to Check Number is Odd or Even
PL/SQL Program to Reverse a String
PL/SQL Program for Palindrome Number
PL/SQL Program to Swap two Numbers
PL/SQL Program for Armstrong Number
PL/SQL Program to Find Greatest of Three Numbers
PL/SQL Program to Print Patterns
From 3 November 2025 onwards, all tutorials listed below have been created and tested using Oracle Database 26ai. Although Oracle Database 26ai was available earlier, this date marks the point at which this page began using it for all new content.
Section 5: Character Functions in Oracle Database 26ai
Character Functions in Oracle Database 26ai are built-in SQL functions used to manipulate, format, and analyze character (string) data. They help in transforming text values by changing case, extracting substrings, concatenating strings, removing unwanted characters, and more.A
ASCII
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
LENGTH
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TRANSLATE USING
TRIM
UPPER
Section 6: Numeric Functions in 26ai
Numeric Functions in Oracle are built-in SQL functions designed to perform operations on numeric data. These functions allow you to carry out mathematical calculations, transform number values, perform rounding, generate numeric results, and support analytical computations within SQL queries.
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL (number)
COS
COSH
EXP
FLOOR (number)
LN
LOG
MOD
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET
Section 7: Date and Time Functions in 26ai
Oracle Date and Time functions are built-in SQL functions used to handle, format, and manipulate date and time values. They allow you to extract date parts, convert between date and timestamp types, perform date arithmetic, handle time zones, and compare or adjust dates and times.
ADD_MONTHS
CEIL (datetime)
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FLOOR (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ORA_DST_AFFECTED
ORA_DST_CONVERT
ORA_DST_ERROR
ROUND (datetime)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TRUNC (datetime)
TZ_OFFSET
Section 8: Comparison Functions in 26ai
Oracle Comparison functions are built-in SQL functions used to compare values and return results based on logical evaluations. They help check equality, inequality, match patterns, handle NULL comparisons, and determine value conditions within queries.
Section 9: Conversion Functions in 26ai
Oracle Conversion functions are built-in SQL functions used to convert data from one type to another, such as numbers to text, text to dates, or dates to timestamps. They ensure data is in the correct format for processing, comparison, or calculations.
ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_BLOB (bfile)
TO_BLOB (raw)
TO_CHAR (bfile|blob)
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB (bfile|blob)
TO_CLOB (character)
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TREAT
UNISTR
VALIDATE_CONVERSION
Section 10: NULL Handling Functions in 26ai
Oracle Database 26ai NULL functions are built-in SQL functions used to handle NULL values and substitute them with meaningful data. They help avoid unexpected NULL results in expressions or calculations by returning alternative values when NULL is encountered.
COALESCE
LNNVL
NANVL
NULLIF
NVL
NVL2
Section 11: Large Object Functions in 26ai
Oracle Large Object (LOB) functions are built-in SQL functions used to store, manage, and manipulate large data such as text, images, audio, video, and documents. They support operations on CLOB, BLOB, NCLOB, and BFILE types, enabling reading, writing, modifying, and accessing large content efficiently.
Section 12: Encoding Decoding Functions in 26ai
Oracle Encoding and Decoding functions convert data into encoded formats for security or transport and decode it back to readable form. They are useful for data masking, secure storage, and safe data transmission.
DECODE
DUMP
ORA_HASH
STANDARD_HASH
VSIZE
Section 13: JSON Functions in 26ai
Oracle JSON functions are built-in SQL functions that store, query, validate, and manipulate JSON data within the database. They help parse JSON, extract values, update content, and generate JSON output, enabling seamless handling of semi-structured data for modern applications.
JSON_QUERY
JSON_TABLE
JSON_VALUE
JSON_ARRAY
JSON_ARRAYAGG
JSON_OBJECT
JSON_OBJECTAGG
JSON Type Constructor
JSON_SCALAR
JSON_SERIALIZE
JSON_TRANSFORM
JSON_DATAGUIDE
Closing Note
These are the current tutorials on Oracle Database 23ai and 26ai, designed to help both beginners and experienced Oracle developers. More tutorials and practical guides will be added regularly to cover new features, advanced SQL and PL/SQL techniques, and real-world use cases as Oracle 23ai evolves.