Performance Tuning with Subquery Factoring and Inline Views

Speed Up Complex Queries: Using Subquery Factoring and Inline Views in Oracle SQL

Overview: Discuss how subquery factoring (WITH) and inline views help break down complex logic, reduce repeated computations, and improve readability and performance. Touch on materialization hints if needed. Example Query:

WITH top_performers AS (
    SELECT employee_id, salary, department_id
    FROM employees
    WHERE salary > 10000
)
SELECT 
    d.department_name,
    tp.employee_id,
    tp.salary
FROM 
    top_performers tp
JOIN 
    departments d ON tp.department_id = d.department_id;

Use Case: Optimize queries that reuse subsets of data (e.g., high-salary employees) and reduce redundancy.

Querying and Analyzing JSON Data in Oracle SQL

Modern SQL: Querying JSON Data in Oracle Like a Pro

Overview: With more systems storing data in JSON format, Oracle's support for JSON functions is a game-changer. Introduce JSON_VALUE, JSON_TABLE, JSON_EXISTS, and how to use them in real scenarios. Example Query:

SQL> ed
  1  SELECT
  2      empno,
  3      ename,
  4      json_value(extra_info, '$.linkedin') AS linkedin_profile
  5  FROM
  6      emp
  7  WHERE
  8*     json_exists(extra_info, '$.linkedin');
SQL> /

Use Case: Extract social media profiles or preferences stored in a JSON column (e.g., extra_info) for analytics or personalization.

CTEs and WITH Clause for Clean, Reusable SQL

Write Cleaner SQL: Harnessing the WITH Clause (CTE) in Oracle

Overview: Benefits of CTEs: readability, reusability, complex nesting. Example Query:

SQL> ed
  1  WITH department_avg AS (
  2      SELECT deptno, AVG(sal) AS avg_salary
  3      FROM emp
  4      GROUP BY deptno
  5  )
  6  SELECT e.empno, e.sal, d.avg_salary
  7  FROM emp e
  8  JOIN department_avg d ON e.deptno = d.deptno
  9* WHERE e.sal > d.avg_salary;
SQL> /

   EMPNO     SAL                                   AVG_SALARY
________ _______ ____________________________________________
    7566    2975    2258.333333333333333333333333333333333333
    7698    2850                                         1800
    7788    3000    2258.333333333333333333333333333333333333
    7839    5000    2916.666666666666666666666666666666666667

Use Case: Identifying above-average performers.

Advanced Data Transformation Using PIVOT in Oracle SQL

From Rows to Columns: Using Oracle SQL PIVOT for Smarter Reports

Overview: Explain the PIVOT clause with aggregation. Example Query:

SQL> ed
  1  SELECT *
  2  FROM (
  3      SELECT deptno, job, sal
  4      FROM emp
  5  )
  6  PIVOT (
  7      SUM(sal)
  8      FOR job IN ('SALESMAN' AS SALES_REP, 'MANAGER' AS MGR)
  9* );
SQL> /

   DEPTNO    SALES_REP     MGR
_________ ____________ _______
       30         4350    2850
       10                 2450
       20                 2975

Use Case: Creating executive summary tables.

Unlocking the Power of Oracle Window Functions

Title: Ranking, Running Totals, and More: A Deep Dive into Oracle Window Functions

Overview: Cover RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD. Example Query:

SQL> ed
  1  SELECT
  2      deptno,
  3      empno,
  4      sal,
  5      RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS salary_rank
  6  FROM
  7*     emp
SQL> /

   DEPTNO    EMPNO     SAL    SALARY_RANK
_________ ________ _______ ______________
       10     7839    5000              1
       10     7782    2450              2
       10     7934    1300              3
       20     7788    3000              1
       20     7566    2975              2
       20     7369     800              3
       30     7698    2850              1
       30     7499    1600              2
       30     7844    1500              3
       30     7521    1250              4

10 rows selected.

Use Case: Finding top earners in each department.

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.'

PL/SQL 23ai (Oracle 23c) – New Feature Boolean Data Type in SQL

Oracle 23c - BOOLEAN Column Support

'Finally! You can use BOOLEAN as a column type in tables and queries.'

SQLcl: Release 24.4 Production on Wed May 28 23:48:53 2025

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

Last Successful login time: Wed May 28 2025 23:48:54 +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> -- Create a table with a BOOLEAN column
SQL> CREATE TABLE employee_status (
  2    emp_id NUMBER PRIMARY KEY,
  3    is_active BOOLEAN
  4  );

Table EMPLOYEE_STATUS created.

SQL>
SQL> -- Insert into BOOLEAN column
SQL> INSERT INTO employee_status VALUES (1001, TRUE);

1 row inserted.

SQL> INSERT INTO employee_status VALUES (1002, FALSE);

1 row inserted.

SQL>
SQL> -- Query BOOLEAN column
SQL> SELECT * FROM employee_status WHERE is_active;

   EMP_ID IS_ACTIVE
_________ ____________
     1001 true

Use Case: Cleaner logic for status flags, without relying on 1/0 or Y/N.

Enhancing Data Security with Oracle 23ai Data Redaction

In today's data-driven landscape, safeguarding sensitive information is paramount. Oracle Database 23ai introduces advanced Data Redaction capabilities, empowering organizations to protect confidential data without altering the underlying database.

---

🔍 What is Oracle Data Redaction?

Oracle Data Redaction allows for the dynamic masking of sensitive data in query results, ensuring that unauthorized users cannot view confidential information. This feature operates transparently, modifying data output at runtime without changing the stored data.

---

🛡️ 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. 

---

🔧 Implementing Data Redaction Policies

Oracle provides the DBMS_REDACT PL/SQL package to manage redaction policies. Here's a basic example:

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'EMP_SALARY_REDACTION',
    column_name   => 'SALARY',
    function_type => DBMS_REDACT.FULL,
    expression    => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR_MANAGER'''
  );
END;
/

This policy fully redacts the SALARY column in the EMPLOYEES table for users other than HR_MANAGER. 

---

📌 Use Cases

• Read-Only Applications: Redact sensitive data displayed on dashboards or reports without affecting the underlying data.

• Data Management Tools: Prevent exposure of confidential information during data loading or management operations.

• Analytics and Reporting: Ensure that analysts and report viewers see only redacted data, maintaining compliance with data privacy regulations. 

---

⚠️ Considerations

• Licensing: Data Redaction is part of Oracle's Advanced Security Option, which may require additional licensing.

• Not a Substitute for Access Control: While Data Redaction obscures data in query results, it does not replace robust access control mechanisms. 

---

Oracle Database 23ai's enhanced Data Redaction capabilities provide a robust framework for protecting sensitive information, ensuring that organizations can meet compliance requirements and safeguard data privacy without compromising functionality or performance.

---

Redacting the data in 23ai Oracle Database

 In this post we will learn how to redact Salary column in employees table of 23ai Oracle database.

As Admin user configure the redact policy as -

PL/SQL Code

BEGIN
    DBMS_REDACT.ADD_POLICY(
        object_schema => 'ADMIN',
        object_name   => 'EMP',
        policy_name   => 'REDACT_SENSITIVE_DATA',
        expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'''
    );
END;
/
SET ECHO ON
BEGIN
    DBMS_REDACT.ALTER_POLICY(
        object_schema => 'ADMIN',
        object_name   => 'EMP',
        column_name   => 'SAL',
        policy_name   => 'REDACT_SENSITIVE_DATA',
        function_type => DBMS_REDACT.FULL
    );
END;
/

Original table Employee Records from ADMIN.EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-80	800		20
7499	ALLEN	SALESMAN	7698	20-02-81	1600	300	30
7521	WARD	SALESMAN	7698	22-02-81	1250	500	30
7566	JONES	MANAGER	7839	02-04-81	2975		20
7698	BLAKE	MANAGER	7839	01-05-81	2850		30
7782	CLARK	MANAGER	7839	09-06-81	2450		10
7788	SCOTT	ANALYST	7566	19-04-87	3000		20
7839	KING	PRESIDENT		17-11-81	5000		10
7844	TURNER	SALESMAN	7698	08-09-81	1500	0	30
7934	MILLER	CLERK	7782	23-01-82	1300		10

10 rows selected.

Employee Records from app user after policy is configured from ADMIN.EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-80	0		20
7499	ALLEN	SALESMAN	7698	20-02-81	0	300	30
7521	WARD	SALESMAN	7698	22-02-81	0	500	30
7566	JONES	MANAGER	7839	02-04-81	0		20
7698	BLAKE	MANAGER	7839	01-05-81	0		30
7782	CLARK	MANAGER	7839	09-06-81	0		10
7788	SCOTT	ANALYST	7566	19-04-87	0		20
7839	KING	PRESIDENT		17-11-81	0		10
7844	TURNER	SALESMAN	7698	08-09-81	0	0	30
7934	MILLER	CLERK	7782	23-01-82	0		10

10 rows selected.

So now you see the normal app user is seeing redacted output and salary as 0, however the original contents of table has salary, please note the ADMIN user will see the original data because he has EXEMPT REDACTION POLICY privilege. So if this privilege EXEMPT REDACTION POLICY is given to someone he or she will be able to see the original contents.

Error Message = ORA-12514: Cannot connect to database.

 I recently encountered a situation where one of my free Oracle autonomous cloud database was throwing Error Message = ORA-12514: Cannot connect to database, while connecting to the database, the reason was simple - The database was hibernated in cloud because i did not access from a long time.

Here is the full error details -

SQLcl: Release 24.4 Production on Thu May 15 23:44:45 2025

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

Connection failed

  USER          = admin

  URL           = jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=re2d3bgg7ywwtp7_pydb_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

  Error Message = ORA-12514: Cannot connect to database. Service re2d3bgg7ywwtp7_pydb_medium.adb.oraclecloud.com is not registered with the listener at host adb.ap-mumbai-1.oraclecloud.com port 1522. (CONNECTION_ID=qI3rAjuSRe+lAwcEvdtwcg==)

https://docs.oracle.com/error-help/db/ora-12514/

Solution :- Start the database from cloud console and try again after the database is started -

SQLcl: Release 24.4 Production on Thu May 15 23:48:44 2025

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

Last Successful login time: Thu May 15 2025 23:48:45 +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> show user

USER is "ADMIN"

# So there you go , this is how you fix the Error Message = ORA-12514: Cannot connect to database

SQLcl: The Modern Oracle Command-Line Tool You Need to Know About

 In the world of Oracle database management, command-line tools have always played a vital role. Traditionally, SQL*Plus was the go-to utility for interacting with Oracle databases from the terminal. But as technology evolved, Oracle introduced a more powerful, user-friendly, and feature-rich alternative: SQLcl (SQL Command Line).

If you're an Oracle developer, DBA, or even a casual user working with Oracle databases, SQLcl can significantly improve your productivity. In this blog post, we’ll explore what SQLcl is, why it’s worth using, and how it can streamline your database workflows.

---

🌟 What is SQLcl?

SQLcl is a modern command-line interface (CLI) provided by Oracle for working with Oracle databases. It offers everything SQL*Plus does but with more enhancements, better scripting support, and developer-friendly features.

Developed as part of the Oracle Database Utilities, SQLcl is lightweight, cross-platform, and supports powerful capabilities such as:

• Command History: Navigate through previous commands easily.
• Code Formatting: Automatically format your SQL code for readability.
• Scripting Support: Run complex scripts with ease.
• JSON and CSV Output: Export query results in modern data formats.
• DML and DDL Generation: Quickly generate table structures and data.
• REST Integration: Interact with Oracle REST Data Services (ORDS).

---

⚙️ Why Should You Use SQLcl?

Here are some compelling reasons why SQLcl stands out:

1. Enhanced Productivity: With features like command history, tab completion, and syntax highlighting, working in SQLcl feels more intuitive compared to SQL*Plus.

2. Data Export Made Simple: Export query results as CSV, JSON, or even SQL inserts with a single command.

3. Built-in Liquibase Support: You can easily manage schema changes using the popular open-source tool Liquibase, integrated natively into SQLcl.

4. Cross-Platform: Whether you're on Windows, macOS, or Linux, SQLcl works seamlessly.

5. Smarter Scripting: The ability to use JavaScript alongside traditional SQL scripting expands what's possible from the command line.

How to Install SQLcl?

Installing SQLcl is straightforward:

1. Download SQLcl:
   Get the latest version from Oracle's official website:
   👉 SQLcl Download Page

2. Extract the Files:
   Unzip the downloaded file into a directory of your choice.

3. Set Up Environment Variables (Optional but Recommended):

• On Linux/macOS:
• export PATH=$PATH:/path/to/sqlcl/bin
• 
  
• On Windows:
• • Add the sqlcl\bin directory to the system PATH.

• Verify Installation:
  Open a terminal and run:

• sql

Comparison: SQLcl vs. SQL*Plus

Feature	SQL*Plus	SQLcl
Command History	❌	✅
Code Formatting	❌	✅
Export as JSON/CSV	❌	✅
REST Integration	❌	✅
Liquibase Support	❌	✅
Scripting (JavaScript)	❌	✅

Clearly, SQLcl offers a significant upgrade for Oracle users.

Conclusion: Why SQLcl Matters

SQLcl is more than just a replacement for SQL*Plus—it's a productivity powerhouse. Whether you're running ad-hoc queries, automating database tasks, or managing schema changes, SQLcl simplifies your workflow while adding modern features.

💡 Pro Tip: Combine SQLcl with shell scripting or automation tools like Ansible for even greater efficiency.

Setting up sqlcl in your macbook

 In this article you will learn how to setup sqlcl in your macbook -

Step 1) Download the zip file from below url -

https://www.oracle.com/in/database/sqldeveloper/technologies/sqlcl/download/

Step 2) Go to the destination now -

base) karandodwal@Karans-MacBook-Air Downloads % pwd

/Users/karandodwal/Downloads

(base) karandodwal@Karans-MacBook-Air Downloads % ls -ltr sqlcl-24.4.1.042.1221.zip

-rw-r--r--@ 1 karandodwal  staff  92394919 22 Feb 23:05 sqlcl-24.4.1.042.1221.zip

Step 3 ) Unzip the software now

 unzip sqlcl-24.4.1.042.1221.zip

Archive:  sqlcl-24.4.1.042.1221.zip

   creating: sqlcl/

   creating: sqlcl/bin/

   creating: sqlcl/lib/

   creating: sqlcl/lib/ext/

  inflating: sqlcl/NOTICES.txt

  inflating: sqlcl/lib/hk2-api.jar

  inflating: sqlcl/LICENSE.txt

  inflating: sqlcl/lib/javassist.jar

  inflating: sqlcl/THIRD-PARTY-LICENSES.txt

  inflating: sqlcl/lib/jersey-apache-connector.jar

  inflating: sqlcl/lib/sshd-contrib.jar

  inflating: sqlcl/24.4.1.042.1221

  inflating: sqlcl/lib/httpclient.jar

  inflating: sqlcl/bin/version.txt

  inflating: sqlcl/lib/httpcore.jar

  inflating: sqlcl/bin/dependencies.txt

  inflating: sqlcl/lib/jackson-datatype-jsr310.jar

  inflating: sqlcl/bin/sql

  inflating: sqlcl/lib/jackson-databind.jar

  inflating: sqlcl/bin/sql.exe

  inflating: sqlcl/lib/vavr-match.jar

  inflating: sqlcl/lib/oci-java-sdk-databasetools.jar

  inflating: sqlcl/lib/pom.xml

  inflating: sqlcl/lib/oci-java-sdk-common.jar

  inflating: sqlcl/lib/ext/dbtools-cpat.jar

  inflating: sqlcl/lib/oci-java-sdk-identity.jar

  inflating: sqlcl/lib/ext/cpat.jar

  inflating: sqlcl/lib/ext/dbtools-data.jar

  inflating: sqlcl/lib/ext/poi.jar

  inflating: sqlcl/lib/ext/poi-ooxml.jar

  inflating: sqlcl/lib/ext/poi-ooxml-lite.jar

  inflating: sqlcl/lib/ext/xmlbeans.jar

  inflating: sqlcl/lib/ext/commons-collections4.jar

  inflating: sqlcl/lib/ext/log4j-api-2.20.0.jar

  inflating: sqlcl/lib/ext/log4j-core-2.20.0.jar

  inflating: sqlcl/lib/ext/dbtools-datapump.jar

  inflating: sqlcl/lib/ext/dbtools-apex.jar

  inflating: sqlcl/lib/vavr.jar

  inflating: sqlcl/lib/ext/commons-lang3.jar

  inflating: sqlcl/lib/ext/text-tree.jar

  inflating: sqlcl/lib/bcpkix-jdk15to18.jar

  inflating: sqlcl/lib/ext/commons-text.jar

  inflating: sqlcl/lib/sshd-sftp.jar

  inflating: sqlcl/lib/ext/json-flattener.jar

  inflating: sqlcl/lib/bcprov-jdk15to18.jar

  inflating: sqlcl/lib/ext/json-base.jar

  inflating: sqlcl/lib/resilience4j-circuitbreaker.jar

  inflating: sqlcl/lib/ext/commons-compress.jar

  inflating: sqlcl/lib/ext/commons-codec.jar

  inflating: sqlcl/lib/ext/opencsv.jar

  inflating: sqlcl/lib/ext/snakeyaml.jar

  inflating: sqlcl/lib/ext/dbtools-liquibase.jar

  inflating: sqlcl/lib/ext/liquibase-core.jar

  inflating: sqlcl/lib/ext/dbtools-dg.jar

  inflating: sqlcl/lib/resilience4j-core.jar

  inflating: sqlcl/lib/ext/dbtools-aq.jar

  inflating: sqlcl/lib/ext/dbtools-blockchain.jar

  inflating: sqlcl/lib/sshd-scp.jar

  inflating: sqlcl/lib/ext/dbtools-modeler-cli-ext.jar

  inflating: sqlcl/lib/oci-java-sdk-secrets.jar

  inflating: sqlcl/lib/ext/dbtools-modeler-common.jar

  inflating: sqlcl/lib/ext/dbtools-jobs.jar

  inflating: sqlcl/lib/oci-java-sdk-bastion.jar

  inflating: sqlcl/lib/ext/dbtools-copy.jar

  inflating: sqlcl/lib/sshd-osgi.jar

  inflating: sqlcl/lib/ext/dbtools-sqlcl-awr-extension.jar

  inflating: sqlcl/lib/oci-java-sdk-workrequests.jar

  inflating: sqlcl/lib/ext/pushd-popd-dirs.jar

  inflating: sqlcl/lib/sshd-putty.jar

  inflating: sqlcl/lib/ext/dbtools-project.jar

  inflating: sqlcl/lib/ext/org.eclipse.jgit.jar

  inflating: sqlcl/lib/ext/dbtools-mdb.jar

  inflating: sqlcl/lib/ext/ucanaccess.jar

  inflating: sqlcl/lib/ext/jackcess.jar

  inflating: sqlcl/lib/ext/hsqldb.jar

  inflating: sqlcl/lib/ext/dbtools-mle-command-extension.jar

  inflating: sqlcl/lib/dbtools-sqlcl.jar

  inflating: sqlcl/lib/xmlparserv2_sans_jaxp_services.jar

  inflating: sqlcl/lib/antlr-runtime.jar

  inflating: sqlcl/lib/antlr4-runtime.jar

  inflating: sqlcl/lib/jakarta.json-api.jar

  inflating: sqlcl/lib/parsson.jar

  inflating: sqlcl/lib/dbtools-core.jar

  inflating: sqlcl/lib/ojdbc11.jar

  inflating: sqlcl/lib/xdb.jar

  inflating: sqlcl/lib/dbtools-arbori.jar

  inflating: sqlcl/lib/sqlcl-jline.jar

  inflating: sqlcl/lib/jansi.jar

  inflating: sqlcl/lib/jline.jar

  inflating: sqlcl/lib/dbtools-net.jar

  inflating: sqlcl/lib/jackson-annotations.jar

  inflating: sqlcl/lib/jackson-core.jar

  inflating: sqlcl/lib/jackson-jr-objects.jar

  inflating: sqlcl/lib/jackson-jr-stree.jar

  inflating: sqlcl/lib/low-level-api.jar

  inflating: sqlcl/lib/dbtools-common.jar

  inflating: sqlcl/lib/ucp11.jar

  inflating: sqlcl/lib/guava.jar

  inflating: sqlcl/lib/orai18n.jar

  inflating: sqlcl/lib/orai18n-utility.jar

  inflating: sqlcl/lib/orai18n-mapping.jar

  inflating: sqlcl/lib/oraclepki.jar

  inflating: sqlcl/lib/ST4.jar

  inflating: sqlcl/lib/orajsoda.jar

  inflating: sqlcl/lib/commons-io.jar

  inflating: sqlcl/lib/dbtools-http.jar

  inflating: sqlcl/lib/slf4j-jdk14.jar

  inflating: sqlcl/lib/slf4j-api.jar

  inflating: sqlcl/lib/httpclient5.jar

  inflating: sqlcl/lib/httpcore5.jar

  inflating: sqlcl/lib/httpcore5-h2.jar

  inflating: sqlcl/lib/dbtools-oci.jar

  inflating: sqlcl/lib/oci-java-sdk-common-httpclient-jersey3.jar

  inflating: sqlcl/lib/oci-java-sdk-common-httpclient.jar

  inflating: sqlcl/lib/jakarta.ws.rs-api.jar

  inflating: sqlcl/lib/oci-java-sdk-database.jar

  inflating: sqlcl/lib/jakarta.annotation-api.jar

  inflating: sqlcl/lib/jersey-common.jar

  inflating: sqlcl/lib/jakarta.inject-api.jar

  inflating: sqlcl/lib/osgi-resource-locator.jar

  inflating: sqlcl/lib/jersey-client.jar

  inflating: sqlcl/lib/jersey-media-json-jackson.jar

  inflating: sqlcl/lib/hk2-utils.jar

  inflating: sqlcl/lib/jersey-entity-filtering.jar

  inflating: sqlcl/lib/oci-java-sdk-circuitbreaker.jar

  inflating: sqlcl/lib/jcl-over-slf4j.jar

  inflating: sqlcl/lib/jdbcrest.jar

  inflating: sqlcl/lib/jackson-module-jakarta-xmlbind-annotations.jar

  inflating: sqlcl/lib/jakarta.activation.jar

  inflating: sqlcl/lib/jakarta.xml.bind-api.jar

  inflating: sqlcl/lib/jersey-hk2.jar

  inflating: sqlcl/lib/hk2-locator.jar

  inflating: sqlcl/lib/aopalliance-repackaged.jar

  inflating: sqlcl/lib/eddsa.jar

Step 4) launch sqlcl now, make sure your path is correct i.e - /Users/karandodwal/git/sql/sqlcl/bin should be the value of the $PATH -

% echo $PATH

/Users/karandodwal/git/sql/sqlcl/bin

Step 5) Launch sqlcl now with command sql , the key thing to note us sql is command because in the bin folder you will see sql file as an executable as -

 cd /Users/karandodwal/git/sql/sqlcl/bin

(base) karandodwal@Karans-MacBook-Air bin % ls -ltr

total 416

-rwxr-xr-x@ 1 karandodwal  staff   29954 11 Feb 12:20 sql

-rw-r-----@ 1 karandodwal  staff     152 11 Feb 12:23 version.txt

-rw-r-----@ 1 karandodwal  staff    3704 11 Feb 12:23 dependencies.txt

-rwxr-xr-x@ 1 karandodwal  staff  169984 11 Feb 12:24 sql.exe

-rw-------@ 1 karandodwal  staff       0 22 Feb 23:10 history.log

Now fire the sql command with ./sql or sql as our path is already sorted above.

(base) karandodwal@Karans-MacBook-Air ~ % sql

SQLcl: Release 24.4 Production on Sun Feb 23 00:07:50 2025

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

Username? (''?)

# In the next article you will learn how to connect to autonomous database using sqlcl, stay tuned.