8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Sql » Here


Oracle SQL Articles

Getting Started

SQL for Beginners - YouTube Playlist

SQL for Beginners (Part 1) : The SELECT List - In this article we take a look at the type of things you are likely to see in the SELECT list of queries.

SQL for Beginners (Part 2) : The FROM Clause - In this article we take a look at the type of things you are likely to see in the FROM clause of queries.

SQL for Beginners (Part 3) : The WHERE Clause - In this article we take a look at the type of things you are likely to see in the WHERE clause of queries.

SQL for Beginners (Part 4) : The ORDER BY Clause - In this article we take a look at how you can influence the order of the data that is returned by queries.

SQL for Beginners (Part 5) : Joins - In this article we take a look at some of the common joins, both ANSI and non-ANSI, available in SQL.

SQL for Beginners (Part 6) : Set Operators - In this article we take a look at the SQL set operators available in Oracle.

SQL for Beginners (Part 7) : The GROUP BY Clause and HAVING Clause - In this article we take a look at the GROUP BY clause and HAVING clause in SQL.

SQL for Beginners (Part 8) : The INSERT Statement - In this article we take a look at some of the variations on the INSERT statement.

SQL for Beginners (Part 9) : The UPDATE Statement - In this article we take a look at some of the variations on the UPDATE statement.

SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements - In this article we take a look at the DELETE and TRUNCATE TABLE statements.

Analytic Functions

Analytic Functions - An introduction to analytic functions in Oracle.

Analytic Function Syntax Enhancements (WINDOW, GROUPS, EXCLUDE) in Oracle Database 21c - This article describes the syntax enhancements to analytic function introduced in Oracle database 21c.

Aggregation over INTERVAL Datatypes in Oracle Database 23c - Oracle 23c allows us to use the SUM and AVG aggregate and analytic functions with INTERVAL datatypes for the first time.

AVG (Mean) and MEDIAN Analytic Functions - Simple examples of how to use the AVG (mean) and MEDIAN analytic functions.

BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG Analytic Functions - This article gives an overview of the BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG analytic functions, introduced in Oracle database 21c.

CHECKSUM Analytic Function - This article gives an overview of the CHECKSUM analytic function introduced in Oracle database 21c.

CORR Analytic Function - Simple examples of how to use the CORR analytic function.

COUNT Analytic Function - Simple examples of how to use the COUNT analytic function.

COVAR_POP and COVAR_SAMP Analytic Functions - Simple examples of how to use the COVAR_POP and COVAR_SAMP analytic functions.

CUME_DIST Analytic Function - Simple examples of how to use the CUME_DIST analytic function.

FIRST and LAST Analytic Functions - Simple examples of how to use the FIRST and LAST analytic functions.

FIRST_VALUE and LAST_VALUE Analytic Functions - Simple examples of how to use the FIRST_VALUE and LAST_VALUE analytic functions.

KURTOSIS_POP and KURTOSIS_SAMP Analytic Functions - The KURTOSIS_POP and KURTOSIS_SAMP analytic functions were added in Oracle 21c to describe the "tailedness" or shape of a probability distribution.

LAG and LEAD Analytic Functions - Simple examples of how to use the LAG and LEAD analytic functions.

LISTAGG Function Enhancements in Oracle Database 12c Release 2 (12.2) - The LISTAGG function has been enhanced in Oracle Database Release 2 (12.2), allowing it to handle overflow errors gracefully.

LISTAGG DISTINCT in Oracle Database 19c - Oracle 19c includes the ability to remove duplicates from the LISTAGG results by including the DEFAULT keyword.

MIN and MAX Analytic Functions - Simple examples of how to use the MIN and MAX analytic functions.

NTH_VALUE Analytic Function - This article gives and overview of the NTH_VALUE analytic function, which is similar to the FIRST_VALUE and LAST_VALUE analytic functions.

NTILE Analytic Function - Simple examples of how to use the NTILE analytic function.

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1) - Learn about the new pattern matching analytic function clause available in Oracle 12c onward.

PERCENT_RANK Analytic Function - Simple examples of how to use the PERCENT_RANK analytic function.

RANK and DENSE_RANK Analytic Functions - Simple examples of how to use the RANK and DENSE_RANK analytic functions.

RATIO_TO_REPORT Analytic Function - Simple examples of how to use the RATIO_TO_REPORT analytic function.

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1) - Simplify the syntax of Top-N queries using the row limiting clause of Oracle Database 12c.

ROW_NUMBER Analytic Function - Simple examples of how to use the ROW_NUMBER analytic function.

SKEWNESS_POP and SKEWNESS_SAMP Analytic Functions - The SKEWNESS_POP and SKEWNESS_SAMP analytic functions were added in Oracle 21c to measure asymmetry, or skew, in the distribution of data.

STDDEV, STDDEV_POP and STDDEV_SAMP Analytic Functions - Simple examples of how to use the STDDEV, STDDEV_POP and STDDEV_SAMP analytic functions.

String Aggregation Techniques - Several methods of combining multiple rows of data into a single row using aggregate functions.

SUM Analytic Function - Simple examples of how to use the SUM analytic function.

Top-N Queries - Top-N queries provide a method for limiting the number of rows returned from ordered sets of data.

VARIANCE, VAR_POP and VAR_SAMP Analytic Functions - Simple examples of how to use the VARIANCE, VAR_POP and VAR_SAMP analytic functions.

Analytic Functions Playlist

Oracle 9i

ANYDATA Type - This article presents an overview of the ANYDATA type.

ANSI ISO SQL Support In Oracle9i - Check out the new SQL enhancements to bring Oracle in line with the ANSI/ISO SQL: 1999 standards.

CASE Expressions And Statements - Learn how to use CASE expressions in both SQL and PL/SQL. In addition, learn how to use the CASE statement in PL/SQL.

DBMS_XPLAN - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.

Flashback Query - Get a consistent view of the your data at a previous point in time.

MERGE Statement - Use the MERGE statement to quickly upsert data into tables.

Metadata API (DBMS_METADATA) - Extract DDL or XML definitions of all database objects using this simple API.

Multitable Inserts - Multitable inserts were introduced in Oracle 9i to allow a single INSERT INTO .. SELECT statement to conditionally, or unconditionally, insert into multiple tables.

SQL New Features In Oracle9i - Check out the new SQL features with emphasis on those relevant for the Oracle9i Database: New Features For Administrators OCP exam.

SQL*Plus Web Reports - Generate HTML reports directly from SQL*Plus.

SQL/XML (SQLX) : Generating XML using SQL - Oracle9i Release 2 includes functionality to support the emerging SQL/XML standard to simplify XML generation from SQL queries.

XMLSEQUENCE - Use this operator to split multi-value results from XMLTYPE queries into multiple rows.

XMLType Datatype - Store XML documents in tables and query them using SQL.

Oracle 10g

Automatic SQL Tuning in Oracle Database 10g - This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g.

Commit Enhancements in Oracle 10g Database Release 2 - Use the WRITE clause of the COMMIT command or the COMMIT_WRITE parameter to influence the way redo information is processed by commit operations.

DML Error Logging in Oracle 10g Database Release 2 - Use this new feature to capture errors and allow DML operations to complete successfully.

MERGE Statement Enhancements in Oracle Database 10g - Learn about the enhancements to the merge statement in Oracle Database 10g.

Flashback New Features and Enhancements in Oracle Database 10g - Use the new and enhanced features of flashback technology to view old data, track data changes and recover from accidental data loss without reverting to LogMiner or point in time recoveries.

Flashback Query (AS OF) in Oracle Database 10g - Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause.

Flashback Transaction Query in Oracle Database 10g - Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries.

Flashback Version Query (VERSIONS BETWEEN) in Oracle Database 10g - Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.

SQL Access Advisor in Oracle Database 10g - Use this advisor to get suggestions for indexes and materialized views that might help system performance.

SQL*Plus Enhancements in Oracle Database 10g - Check out enhancements to SQL*Plus in Oracle Database 10g.

SQL trace, 10046, trcsess and tkprof in Oracle - An article that combines all previous SQL Trace, event 10046 and tkprof information, along with information on trcsess and DBMS_MONITOR from Oracle 10g Onward.

XMLTABLE : Convert XML Data into Rows and Columns using SQL - Use the XMLTABLE operator to project columns on to XML data, allowing you to query it directly from SQL.

Oracle 11g

Automatic SQL Tuning in Oracle Database 11g Release 1 - Take advantage of the Automatic SQL Tuning features of Oracle 11g Release 1.

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) - Avoid unnecessary "ORA-00054: resource busy" errors in 11g.

Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1 - See how finer grained dependency management in 11g reduces the extent of invalidations associated with schema changes.

Invisible Indexes in Oracle Database 11g Release 1 - Take control of the indexes available to the optimizer by using invisible indexes in 11g.

Miscellaneous New Features in Oracle Database 11g Release 1 - A collection of new features and enhancements listed in the miscellaneous section of

PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1 - Learn how to use the new PIVOT and UNPIVOT operators in Oracle Database 11g Release 1.

Query Result Cache in Oracle Database 11g Release 1 - Improve the performance of SQL across the whole database instance by caching query results.

Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_SQL_DETAIL) - Monitor the performance of SQL queries while the are running.

Recursive Subquery Factoring : Hierarchical Queries Using Recursive WITH Clauses - This articles gives an overview of how to query hierarchical data in Oracle databases using recursive subquery factoring.

SQL Access Advisor in Oracle Database 11g Release 1 - Use this advisor to get suggestions for indexes, materialized views and partitioning schemes to improve system performance.

SQL Performance Analyzer in Oracle Database 11g Release 1 - Compare the performance of the statements in a SQL tuning set before and after database changes.

SQL Plan Management in Oracle Database 11g Release 1 - Maintain consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

UTL_MATCH : String Matching by Testing Levels of Similarity/Difference - Use the UTL_MATCH package to determine the similarity between two strings.

Oracle 12c

Oracle SQL and PL/SQL Enhancements in Oracle Database 12c Release 2 (12.2) - This page provides links to all the separate articles on SQL and PL/SQL enhancements in Oracle Database 12c Release 2 (12.2).

Adaptive Plans in Oracle Database 12c Release 1 (12.1) - See how the 12c optimizer uses adaptive plans to adjust execution plans on the fly.

Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2) - This article provides links to articles on the individual features of Adaptive Query Optimization.

APPROX_COUNT_DISTINCT : Quick Distinct Count in Oracle Database 12cR1 (12.1.0.2) - Use the APPROX_COUNT_DISTINCT to get quick counts of distinct values in 12.1.0.2 onward.

Approximate Query Processing in Oracle Database 12c Release 2 (12.2) - Oracle Database 12c Release 2 (12.2) extends the concept of approximate query processing by the addition of new functions and transparent conversion to approximate query processing.

Automatic Reoptimization in Oracle Database 12c Release 1 (12.1) - See how statistics feedback and performance feedback are used by automatic reoptimization in Oracle Database 12c Release 1.

Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1) - Discover how Oracle Database 12c allows you to grant roles directly to PL/SQL program units.

Column-Level Collation and Case-Insensitive Database in Oracle Database 12c Release 2 (12.2) - Oracle Database 12c Release 2 (12.2) lets you specify the collation used for columns that hold string data, allowing you to easily perform case insensitive queries, as well as control the order of queried data.

Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES) - Learn how to plug security holes related to invoker rights PL/SQL code in Oracle Database 12c Release 1.

Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER) - This article describes support for invoker rights function calls in views in Oracle Database 12c Release 1.

Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1) - Use data redaction to protect sensitive data in Oracle Database 12c Release 1 (12.1).

Data Redaction (DBMS_REDACT) Enhancements in Oracle Database 12c Release 2 (12.2) - This article demonstrates a number of the enhancements to Data Redaction in Oracle Database 12c Release 2 (12.2).

DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c Release 1 (12.1) - Easily display the true complexity of SQL statements that reference views.

Dynamic Statistics in Oracle Database 12c Release 1 (12.1) - This article describes the changes made to dynamic sampling in Oracle Database 12c Release 1 (12.1).

Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2) - This article provides an overview of how to index JSON data in Oracle Database 12c Release 1 (12.1.0.2).

JSON Support in Oracle Database 12c Release 1 (12.1.0.2) - This article describes the server side support for JSON in the database.

JSON Support Enhancement in Oracle Database 12c Release 2 (12.2) - This article describes the server side support for JSON in the database.

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 (12.1) - Learn the new variations on inline views and joins available in Oracle 12c.

LISTAGG Function Enhancements in Oracle Database 12c Release 2 (12.2) - The LISTAGG function has been enhanced in Oracle Database Release 2 (12.2), allowing it to handle overflow errors gracefully.

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c (12.1.0.2 and 12.2) - The PDB CONTAINERS clause allows data to be queried across multiple PDBs in Oracle Database 12c (12.1.0.2 and 12.2).

Oracle SQL and PL/SQL Enhancements in Oracle Database 12c Release 2 (12.2) - This page provides links to all the separate articles on SQL and PL/SQL enhancements in Oracle Database 12c Release 2 (12.2).

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1) - Learn about the new pattern matching analytic function clause available in Oracle 12c onward.

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c Release 1 (12.1) - Oracle 12c now supports the binding of additional PL/SQL-only data types to SQL.

Real-Time Database Operation Monitoring in Oracle Database 12c Release 1 - Oracle 12c extends real-time SQL monitoring to allow monitoring of multiple SQL and PL/SQL calls as a single operation.

Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2) - Oracle 12.2 introduced the concept of real-time materialized views, which allow a statement-level wind-forward of a stale materialised view, making the data appear fresh to the statement.

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1) - Simplify the syntax of Top-N queries using the row limiting clause of Oracle Database 12c.

SQL Plan Directives in Oracle Database 12c Release 1 (12.1) - This article explains the role of SQL Plan Directives in Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2).

SQL/JSON Functions in Oracle Database 12c Release 2 (12.2) - This article gives basic examples of the SQL/JSON functions introduced in Oracle Database 12c Release 2 (12.2).

Temporal Validity in Oracle Database 12c Release 1 (12.1) - Make querying of effective date ranges simpler using temporal validity in Oracle database 12c.

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1) - Define PL/SQL functions and procedures in the WITH clause of SQL statements.

Oracle 18c

Oracle SQL and PL/SQL Enhancements in Oracle Database 18c - This page provides links to all the separate articles on SQL and PL/SQL enhancements in Oracle Database 18c.

Approximate Top-N Query Processing (APPROX_RANK, APPROX_SUM, APPROX_COUNT) In Oracle Database 18c - The APPROX_RANK, APPROX_SUM and APPROX_COUNT functions were introduced in Oracle 18c to allow approximate top-n query processing.

ALTER SYSTEM CANCEL SQL : Cancel a SQL Statement in a Session in Oracle Database 18c - The ability to cancel a SQL statement using the ALTER SYSTEM CANCEL SQL statement was introduced in Oracle Database 18c as an alternative to killing a rogue session.

Inline External Tables in Oracle Database 18c - Oracle Database 18c allows you to access data in flat files using an inline external table defined in a SELECT statement.

JSON Support Enhancements in Oracle Database 18c - Oracle 18c includes several enhancements to the JSON functionality in the database. This article details some of them, as well as linking to separate articles on others.

JSON Data Guide Enhancements in Oracle Database 18c - This article covers the enhancements to the JSON Data Guide functionality in Oracle Database 18c.

JSON_EQUAL Condition in Oracle Database 18c - The JSON_EQUAL condition was introduced in 18c to allow JSON documents to be compared regardless of member order or document formatting.

JSON_TABLE Enhancements in Oracle Database 18c - This article covers the enhancements to the JSON_TABLE function in Oracle Database 18c.

Online MERGE PARTITION and MERGE SUBPARTITION in Oracle Database 18c - In Oracle Database 18c the MERGE PARTITION and MERGE SUBPARTITION operations on heap tables can be performed online so they don't block DML.

Private Temporary Tables in Oracle Database 18c - Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.

Scalable Sequences in Oracle Database 18c - Scalable sequences are designed to reduce problems with hot index blocks during large scale data loads into tables with sequence generated primary keys on single instance and RAC databases.

TREAT(... AS JSON) in Oracle Database 18c - In Oracle 18c the TREAT function can be used in SQL to change the declared type of an expression to JSON, where the expression is an object containing JSON data.

TO_UTC_TIMESTAMP_TZ Function in Oracle Database 18c - Oracle 18c introduced the new TO_UTC_TIMESTAMP_TZ function to help deal with ISO 8601 date strings.

Oracle 19c

GeoJSON Data Support in Oracle Database 19c - In this article we demonstrate the support for GeoJSON data in Oracle Database 19c. Specifically the mapping between GeoJSON data and the Oracle Spatial SDO_GEOMETRY object type.

JSON Support Enhancements in Oracle Database 19c - Oracle 19c includes several enhancements to the JSON functionality in the database. This article details some of them, as well as linking to separate articles on others.

JSON_MERGEPATCH in Oracle Database 19c - The JSON_MERGEPATCH function is used to modify parts of a JSON document in select and update operations.

JSON_OBJECT Enhancements in Oracle Database 19c - The SQL/JSON function JSON_OBJECT got a whole lot easier to use in Oracle 19c.

JSON_SERIALIZE in Oracle Database 19c - The JSON_SERIALIZE function converts a JSON document from any supported data type to text.

LISTAGG DISTINCT in Oracle Database 19c - Oracle 19c includes the ability to remove duplicates from the LISTAGG results by including the DEFAULT keyword.

Mapping of JSON Data To and From SQL Object Types in Oracle Database 19c - Oracle database 19c allows JSON data to instantiate user-defined object type instances, and user defined object-type instances can be converted to JSON data.

Materialized View Support for Queries Containing JSON_TABLE in Oracle Database 19c - Oracle 19c can allegedly perform query rewrites of statements using SQL/JSON functions (JSON_VALUE, JSON_EXISTS etc.) to use a materialized view containing JSON_TABLE where appropriate.

SQL NESTED Clause Instead of JSON_TABLE in Oracle Database 19c - The SQL NESTED clause allows us to write the equivalent of the LEFT OUTER JOIN JSON_TABLE using a simplified syntax.

SQL Quarantine in Oracle Database 19c - Oracle Database 19c introduced an extension of Runaway Query Management called SQL Quarantine.

Oracle 21c

Analytic Function Syntax Enhancements (WINDOW, GROUPS, EXCLUDE) in Oracle Database 21c - This article describes the syntax enhancements to analytic function introduced in Oracle database 21c.

ANY_VALUE Aggregate Function in Oracle Database 21c - The ANY_VALUE function allows us to safely drop columns out of a GROUP BY clause to reduce any performance overhead.

BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG Analytic Functions - This article gives an overview of the BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG analytic functions, introduced in Oracle database 21c.

Blockchain Tables in Oracle Database 21c - A blockchain table is a tamper-proof, insert-only table with an associated table-level and row-level retention period.

CHECKSUM Analytic Function - This article gives an overview of the CHECKSUM analytic function introduced in Oracle database 21c.

DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c (COMPARE_PLANS, COMPARE_CURSORS and COMPARE_EXPLAIN) - This article demonstrates how to compare execution plans using the DBMS_XPLAN package in Oracle Database 19c and 21c.

JSON Support Enhancements in Oracle Database 21c - Oracle 21c includes several enhancements to the JSON functionality in the database. This post acts as a links page to separate articles on each feature.

JSON Data Type in Oracle 21c - The JSON data type was introduced in the Oracle 20c preview release to provide native JSON support and improve the performance of JSON processing. It has become generally available in Oracle 21c.

KURTOSIS_POP and KURTOSIS_SAMP Analytic Functions - The KURTOSIS_POP and KURTOSIS_SAMP analytic functions were added in Oracle 21c to describe the "tailedness" or shape of a probability distribution.

SKEWNESS_POP and SKEWNESS_SAMP Analytic Functions - The SKEWNESS_POP and SKEWNESS_SAMP analytic functions were added in Oracle 21c to measure asymmetry, or skew, in the distribution of data.

SQL Macros in Oracle Database 21c - SQL Macros improve code reuse by factoring out common expressions and statements into reusable components.

SQL Set Operator Enhancements (EXCEPT, EXCEPT ALL, MINUS ALL, INTERSECT ALL) in Oracle Database 21c - Oracle 21c includes a number of enhancements to SQL set operators including EXCEPT, EXCEPT ALL, MINUS ALL and INTERSECT ALL.

Oracle 23c

Annotations in Oracle Database 23c - This article describes the use of annotations to document our database objects in Oracle database 23c.

Aggregation over INTERVAL Datatypes in Oracle Database 23c - Oracle 23c allows us to use the SUM and AVG aggregate and analytic functions with INTERVAL datatypes for the first time.

Auditing Enhancements in Oracle Database 23c - This post describes some of the auditing enhancements in Oracle database 23c.

Automatic PL/SQL to SQL Transpiler in Oracle Database 23c -
The automatic SQL transpiler in Oracle 23c allows some functions to be converted into SQL expressions to reduce the overhead of function calls in SQL.

Blockchain Table Enhancements in Oracle Database 23c - Blockchain tables were first introduced in Oracle 21c, and backported to Oracle 19c. This article demonstrates the enhancements to blockchain tables in Oracle 23c.

Boolean Data Type in Oracle Database 23c - Oracle database 23c introduced the boolean data type in SQL.

CASE Statement and CASE Expression Enhancements in Oracle Database 23c - In Oracle database 23c the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

CEIL and FLOOR Support DATE, TIMESTAMP, and INTERVAL Data Types in Oracle Database 23c - In Oracle database 23c the CEIL and FLOOR functions support the DATE, TIMESTAMP and INTERVAL data types.

DB_DEVELOPER_ROLE Role in Oracle Database 23c - Oracle database 23c introduced a new DB_DEVELOPER_ROLE role, to provide the basic roles and privileges Oracle believe are necessary for a database developer.

DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23c - In Oracle 23c we can define a column as DEFAULT ON NULL FOR INSERT AND UPDATE. This replaces explicit null values with the default value in update statements.

Direct Joins for UPDATE and DELETE Statements in Oracle Database 23c - From Oracle database 23c onward we are allowed to use direct joins to tables to drive UPDATE and DELETE statements.

DML RETURNING Clause Enhancements in Oracle Database 23c - This article demonstrates the enhancements to the DML RETURNING clause introduced in Oracle database 23c.

Domains in Oracle Database 23c - This article demonstrates the use of Domains in Oracle database 23c.

FUZZY_MATCH and PHONIC_ENCODE Data Quality Operators in Oracle Database 23c - Oracle database 23c introduced the FUZZY_MATCH and PHONIC_ENCODE data quality operators to perform fuzzy string matching.

GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23c - From Oracle 23c onward we can use the column alias in GROUP BY and HAVING clauses, or the column position in the GROUP BY clause.

Hybrid Partitioned Table Enhancements in Oracle Database 23c - This article demonstrates hybrid partitioned tables with interval and auto-list partitioning in Oracle Database 23c.

IF [NOT] EXISTS DDL Clause in Oracle Database 23c - Prevent errors during object creation and removal using the IF [NOT] EXISTS DDL clause in Oracle Database 23c.

Immutable Table Enhancements in Oracle Database 23c - Immutable tables were introduced to Oracle 21.3 and 19.11 at the same time, so it could be considered a 19c and 21c new feature. This article demonstrates the enhancements to immutable tables in Oracle 23c.

JSON_ARRAY Using Subqueries in Oracle Database 23c - From Oracle database 23c onward the JSON_ARRAY function can accept a subquery as input, which brings it in line with the current SQL/JSON standard.

JSON Data Type Constructor Enhancements in Oracle Database 23c - The JSON data type constructor can now accept collections, object types and record types as input.

JSON-Relational Duality Views in Oracle Database 23c - JSON-relational duality views expose our relational data as JSON documents, allowing both query and DML operations to be performed using conventional SQL or directly using JSON.

JSON Schema in Oracle 23c - In Oracle 23c a JSON Schema can validate the structure and contents of JSON documents in your database.

JSON_SERIALIZE : ORDERED Keyword in Oracle Database 23c - In Oracle 23c the ORDERED keyword has been added to the JSON_SERIALIZE function. When present, the members are serialized in ascending alphabetical order by field name.

JSON_TRANSFORM Enhancements in Oracle Database 23c - In Oracle 23c the JSON_TRANSFORM function has lots of new operations, conditional control and richer path support.

JSON_VALUE Function Enhancements in Oracle Database 23c - In Oracle database 23c the RETURNING clause of the JSON_VALUE function has been enhanced allowing it to convert JSON data to user-defined types.

Loading JSON Data using External Tables in Oracle Database 23c - In Oracle database 23c loading JSON data using external tables got much simpler.

Lock-Free Reservations to Prevent Blocking Sessions in Oracle Database 23c - In Oracle 23c we can use Lock-Free Reservations to reduce the incidents of blocking sessions for frequently updated numeric column values.

Logical Partition Change Tracking (LPCT) for Materialized Views in Oracle Database 23c - In Oracle database 23c Logical Partition Change Tracking (LPCT) allows materialized view staleness to be tracked at the level of a logical partition.

Materialized View Concurrent Refreshes in Oracle Database 23c - In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.

Materialized View Enhancements in Oracle Database 23c - This post introduces some of the materialized view enhancements in Oracle database 23c.

Materialized View Support for ANSI Joins in Oracle Database 23c - In Oracle 23c materialized views support query rewrites for SQL statements using ANSI or Oracle style joins.

MAX_COLUMNS : Increase the Maximum Number of Columns for a Table (Wide Tables) in Oracle Database 23c - Oracle 23c introduced the MAX_COLUMNS initialization parameter, which allows us to have up to 4096 columns in a table.

Precheck Constraints using JSON Schema in Oracle Database 23c - In Oracle database 23c we can use the PRECHECK keyword to mark check constraints as being validated externally by an application.

Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c - In Oracle database 23c the JSON_QUERY and JSON_VALUE functions can include multiple predicates in a single JSON path expression, and use the PASSING clause to support variables.

Read-Only PDB Users in Oracle Database 23c - Oracle database 23c allows us to make PDB users read-only, which makes a connected session act like the database is opened in read-only mode, preventing the session from performing write operations.

Removal of Touch-Once Restriction after Parallel DML (Unrestricted Direct Loads) in Oracle Database 23c - Oracle database 23c makes parallel DML more flexible by removing the touch-once restriction after parallel DML. This is also know as unrestricted direct loads.

Rename LOB Segments in Oracle Database 23c - The article demonstrates how to rename a LOB segment in Oracle 23c.

Schema Privileges in Oracle Database 23c - Schema privileges allow us to simplify grants where a user or role needs privileges on all objects in a schema.

SELECT Without FROM Clause in Oracle Database 23c - From Oracle 23c onward we can use a SELECT without a FROM clause in some circumstances.

SQL Property Graphs and SQL/PGQ in Oracle Database 23c - Oracle have had a Graph Server and Client product for some time, but in Oracle database 23c some of the property graph functionality has been built directly into the database.

Staging Tables in Oracle Database 23c - Oracle database 23c introduces the FOR STAGING clause in the CREATE TABLE command to create a variation of heap tables, which have optimal configuration for fast data ingestion.

Table Values Constructor in Oracle Database 23c - The table values constructor allows us to define multiple rows using a single constructor for use in SQL statements.

XML, JSON and Oracle Text Search Index Enhancements in Oracle Database 23c - In Oracle 23c the CREATE SEARCH INDEX statement allows us to create search indexes on XML, JSON and text data, making the syntax consistent between them.

Miscellaneous

ALL, ANY and SOME Comparison Conditions in SQL - A quick look at some comparison conditions you may not be used to seeing in SQL against an Oracle database.

APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns. - The APEX_DATA_PARSER package allows you to easily convert simple CSV, JSON, XML and XLSX formatted data into rows and columns from SQL.

APPEND Hint - Use the APPEND hint to improve the performance of load operations.

CHECKSUM Analytic Function - This article gives an overview of the CHECKSUM analytic function introduced in Oracle database 21c.

DBMS_SQLHASH Package - The DBMS_SQLHASH package allows us to generate a hash based on the result set returned by a query.

Deadlocks - This article shows the steps necessary to identify the offending application code when a deadlock is detected.

DML RETURNING INTO Clause - This article describes the use of the RETURNING INTO clause in DML.

Dynamic IN-Lists - This article presents a number of methods for parameterizing the IN-list of a query.

Efficient Function Calls From SQL - This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.

Efficient SQL Statements - A brief non-version specific guide to writing efficient SQL statements.

Hierarchical Queries in Oracle - Easily query hierarchical data in Oracle databases using SQL.

How do I learn to tune SQL? - A brief overview of the topic of SQL tuning for beginners.

Identifying Host Names and IP Addresses - This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

Join Elimination - This article describes how join elimination can improve performance of queries by removing tables from the plan.

List Files in a Directory From PL/SQL and SQL : Comparison of Methods - This article compares the methods available to list files in a directory on the database server.

List Files in a Directory From PL/SQL and SQL : External Table - This article shows how to list files in a directory on the database server using an external table.

List Files in a Directory From PL/SQL and SQL : Java - This article shows how to list files in a directory on the database server using a Java in the database.

List Files in a Directory From PL/SQL and SQL : DBMS_BACKUP_RESTORE - This article shows how to list files in a directory on the database server using the DBMS_BACKUP_RESTORE package.

List Files in a Directory From PL/SQL and SQL : DBMS_SCHEDULER - This article shows how to list files in a directory on the database server using the DBMS_SCHEDULER package.

Literals, Substitution Variables and Bind Variables - The article compares the affect of using literals, substitution variables and bind variables on memory and CPU usage.

Naming Conventions - Some of the typical naming conventions used for objects in Designer and the database.

NULL-Related Functions (NVL, DECODE, NVL2, COALESCE, NULLIF, LNNVL, NANVL, SYS_OP_MAP_NONNULL) - A summary of the functions available for handling NULL values.

Oracle Dates, Timestamps and Intervals - An overview of the usage of dates, timestamps and intervals in Oracle databases.

Overlapping Date Ranges - This article presents simple methods to test for overlapping date ranges.

Recompiling Invalid Schema Objects - This article presents several methods for recompiling invalid schema objects.

Regular Expression Support in Oracle (REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_LIKE) - Using regular expressions to solve some questions I've been asked over the years.

ROLLUP, CUBE, GROUPING Functions and GROUPING SETS - An overview of some functionality available for aggregation in data warehouses.

Schema Owners and Application Users - Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.

String Aggregation Techniques - Several methods of combining multiple rows of data into a single row using aggregate functions.

Top-N Queries - Top-N queries provide a method for limiting the number of rows returned from ordered sets of data.

Updates Based on Queries - This article describes how a table can be updated using data from another table.

Using Ref Cursors To Return Recordsets - Return recordsets from Oracle stored procedures.

Validate an XML Document Against an XML Schema (XSD) in an Oracle Database - The article gives some simple examples of validating XML documents against a XML Schema (XSD) in an Oracle database.

Virtual Indexes - Use virtual indexes to test alternate indexing strategies with no impact on the rest of your system.

WITH Clause : Subquery Factoring in Oracle - Use the WITH clause in Oracle to reduce repetition and simplify complex SQL statements.

Liquibase : Deploying Oracle Application Express (APEX) Applications - This article demonstrates how to deploy Oracle Application Express (APEX) applications using Liquibase.

SQLcl : Deploying Oracle Application Express (APEX) Applications using the SQLcl implementation of Liquibase - This article demonstrates how to deploy Oracle Application Express (APEX) applications using the SQLcl implementation of Liquibase.