Mastering Hierarchical Queries in Oracle SQL

Unraveling Tree Structures with Hierarchical Queries in Oracle SQL

Overview: Introduction to hierarchical data (e.g., employee-manager). Explain CONNECT BY, PRIOR, and LEVEL.

SQL> ed
  1  SELECT
  2      empno,
  3      ename,
  4      mgr,
  5      LEVEL AS hierarchy_level
  6  FROM
  7      emp
  8  START WITH
  9      mgr IS NULL
 10  CONNECT BY
 11*     PRIOR empno = mgr
SQL> /

   EMPNO ENAME         MGR    HIERARCHY_LEVEL
________ _________ _______ __________________
    7839 KING                               1
    7566 JONES        7839                  2
    7788 SCOTT        7566                  3
    7698 BLAKE        7839                  2
    7499 ALLEN        7698                  3
    7521 WARD         7698                  3
    7844 TURNER       7698                  3
    7782 CLARK        7839                  2
    7934 MILLER       7782                  3

9 rows selected.

Use Case: Visualizing an organizational chart or category tree.

Oracle Data Redaction Key Features in Oracle 23ai

🛡️ Key Features in Oracle 23ai

1. Diverse Redaction Methods:

   • Full Redaction: Replaces entire data entries with default values (e.g., zeros for numbers, spaces for text).

   • Partial Redaction: Masks parts of data, such as displaying only the last four digits of a credit card number.

   • Regular Expression Redaction: Utilizes patterns to identify and redact data, ideal for formats like email addresses.

   • Random Redaction: Substitutes data with random values, maintaining data type consistency.

   • Nullify Redaction: Replaces data with null values, effectively hiding it.

   • No Redaction: Allows for testing policies without applying redaction. 

2. Enhanced SQL Support:

   • Redacted columns can now be used in SQL expressions, including CONCAT, SUM, TRIM, MIN, and MAX, within views and inline views.

   • Support for GROUP BY and DISTINCT clauses on redacted columns, facilitating complex queries without compromising data security.

   • Set operations involving redacted columns are now supported, ensuring consistent redaction across combined query results. 

3. Performance Optimizations:

   • Policy expressions evaluating to TRUE (e.g., 1=1) are optimized, reducing unnecessary evaluations and enhancing query performance. 

4. Integration with Database Features:

   • Redaction policies can be applied to columns involved in function-based indexes and extended statistics, allowing for comprehensive data protection without hindering database performance.

Dynamic Switching with SQL

Recently i wanted to run a script which would automatically switch to a PDB without too much effort to run show pdbs, alter session set container like commands, i found a Smarter way to use SQL to dynamically switch to a PDB without any effort , here is how to do it, make sure to pack this whole stuff in a .sql file and run it, however i am showing all steps 1 by 1 properly

SQLcl: Release 24.4 Production on Thu May 29 00:30:48 2025



Copyright (c) 1982, 2025, Oracle.  All rights reserved.



Last Successful login time: Thu May 29 2025 00:30:49 +05:30



Connected to:

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems

Version 23.8.0.25.05



SQL>

SQL>

SQL> -- Show current container

SQL> PROMPT === Current container:

=== Current container:

SQL> SHOW CON_NAME;

CON_NAME

------------------------------

RE2D3BGG7YWWTP7_PYDB

SQL>

SQL> -- Find the first open PDB

SQL> COLUMN pdb_name NEW_VALUE pdb_var

SQL> SELECT name AS pdb_name

  2  FROM   v$pdbs

  3  WHERE  open_mode = 'READ WRITE'

  4* AND    rownum = 1;



PDB_NAME

_______________________

RE2D3BGG7YWWTP7_PYDB



SQL>

SQL> -- Switch to that PDB

SQL> PROMPT === Switching to PDB: &pdb_var

=== Switching to PDB: RE2D3BGG7YWWTP7_PYDB

SQL> ALTER SESSION SET CONTAINER = &pdb_var;

old:ALTER SESSION SET CONTAINER = &pdb_var

new:ALTER SESSION SET CONTAINER = RE2D3BGG7YWWTP7_PYDB



Session altered.



SQL>

SQL> -- Confirm switch

SQL> PROMPT === Current container after switch:

=== Current container after switch:

SQL> SHOW CON_NAME;

CON_NAME

------------------------------

RE2D3BGG7YWWTP7_PYDB

SQL>

SQL> -- Sample SQL in the PDB

SQL> SELECT name FROM v$database;



NAME

___________

FCEG3XDN



SQL> show con_name

CON_NAME

------------------------------

RE2D3BGG7YWWTP7_PYDB

Now lets try to pack the commands in a script and run script

   SQLcl: Release 24.4 Production on Thu May 29 00:37:51 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Thu May 29 2025 00:37:53 +05:30

Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.8.0.25.05

SQL> set echo on
SQL> @s
SQL>
SQL> -- Show current container
SQL> PROMPT === Current container:
=== Current container:
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
RE2D3BGG7YWWTP7_PYDB
SQL>
SQL> -- Find the first open PDB
SQL> COLUMN pdb_name NEW_VALUE pdb_var
SQL> SELECT name AS pdb_name
  2  FROM   v$pdbs
  3  WHERE  open_mode = 'READ WRITE'
  4  AND    rownum = 1;

PDB_NAME
_______________________
RE2D3BGG7YWWTP7_PYDB

SQL>
SQL> -- Switch to that PDB
SQL> PROMPT === Switching to PDB: &pdb_var
=== Switching to PDB: RE2D3BGG7YWWTP7_PYDB
SQL> ALTER SESSION SET CONTAINER = &pdb_var;
old:ALTER SESSION SET CONTAINER = &pdb_var
new:ALTER SESSION SET CONTAINER = RE2D3BGG7YWWTP7_PYDB

Session altered.

SQL>
SQL> -- Confirm switch
SQL> PROMPT === Current container after switch:
=== Current container after switch:
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
RE2D3BGG7YWWTP7_PYDB
SQL>
SQL> -- Sample SQL in the PDB
SQL> SELECT name FROM v$database;

NAME
___________
FCEG3XDN

SQL>
SQL>
SQL> show pdbs

   CON_ID CON_NAME                OPEN MODE     RESTRICTED
_________ _______________________ _____________ _____________
      244 RE2D3BGG7YWWTP7_PYDB    READ WRITE    NO
      

There you go, enjoy

Oracle Database 23ai — Key New Features

 

1. Boolean Data Type in SQL

• BOOLEAN columns can now be created and queried directly in SQL tables.

• Eliminates workarounds for true/false flags.

2. IF [NOT] EXISTS / DROP IF EXISTS in DDL

• Safer and cleaner creation and dropping of database objects without pre-checks.

3. SQL Macros Enhancements

• Expanded support for parameterized, reusable SQL snippets.

• Scalar and table macros make SQL code DRYer and easier to maintain.

4. Property Graph Query Support

• Native graph querying capabilities with PGQL (Property Graph Query Language).

• Makes graph analytics more seamless within the database.

5. Immutable Tables

• Write-once, read-many tables to enforce immutability for audit and compliance.

6. JSON Relational Duality Views

• Combine relational and JSON representations seamlessly.

• Easier to query and manipulate JSON data alongside relational data.

7. PL/SQL Enhancements

• PRAGMA UDF for faster scalar UDF execution.

• Support for BOOLEAN in SQL and PL/SQL.

• Improved compiler optimizations (dead code elimination, smarter compilation).

• Enhanced exception handling and stack tracing.

• INHERIT privileges for finer security control in code execution.

8. SQL Domain Types

• User-defined domain types for better data integrity and governance.

9. Enhanced In-Memory Vector Joins

• Better performance for analytics using vector joins in in-memory structures.

How to use CONTINUE inside a PL/SQL loop

CONTINUE inside a PL/SQL loop

Today lets see how to use CONTINUE inside a PL/SQL loop. The CONTINUE statement skips the rest of the current iteration and moves to the next one.

SQL> DECLARE
  2    v_counter NUMBER := 0;
  3  BEGIN
  4    FOR i IN 1..10 LOOP
  5      -- Skip even numbers
  6      IF MOD(i, 2) = 0 THEN
  7        CONTINUE;
  8      END IF;
  9
 10      v_counter := v_counter + 1;
 11      DBMS_OUTPUT.PUT_LINE('Processing odd number: ' || i);
 12    END LOOP;
 13
 14    DBMS_OUTPUT.PUT_LINE('Total odd numbers processed: ' || v_counter);
 15  END;
 16* /

PL/SQL procedure successfully completed.

Explanation: The loop runs from 1 to 10. When the number is even (MOD(i, 2) = 0), CONTINUE skips to the next iteration. Only odd numbers are processed and counted.

INHERIT Privilege for Code Objects in 23ai

'The INHERIT privilege in Oracle controls the ability to run invoker's rights code securely.'

'It helps prevent unauthorized access when executing PL/SQL units owned by another user.'

'You must explicitly grant INHERIT privileges to allow one user to run code with another’s context.'

'This privilege is especially important when dealing with definer's rights procedures.'

'Oracle introduced this feature to enhance security and reduce the risk of privilege escalation.'

New privilege that controls who can inherit invoker privileges, for more secure PL/SQL execution.

SQLcl: Release 24.4 Production on Thu May 29 00:09:26 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Thu May 29 2025 00:09:27 +05:30

Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.8.0.25.05

SQL> GRANT INHERIT PRIVILEGES ON USER hr TO app_user;

Grant succeeded.

Use Case: Secure execution of procedures that use AUTHID CURRENT_USER.

23ai new feature - IF [NOT] EXISTS for DDL

Smart DDL with Conditional Create and Drop

'Skip the pain of pre-checks when creating or dropping objects.'

"SQL> -- Only create table if it doesn't already exist
SQL> CREATE TABLE IF NOT EXISTS dept (
  2    dept_id NUMBER,
  3    dept_name VARCHAR2(100)
  4* );

Table DEPT created.

SQL>
SQL> -- Safely drop table if it exists
SQL> DROP TABLE IF EXISTS dept;

Table DEPT dropped."

'Use Case: Safer and cleaner DDL in deployment scripts.'