DEV Community: SANDEEP KUMAR

Oracle SQL: JSON Functions

SANDEEP KUMAR — Wed, 23 Sep 2026 16:42:54 +0000

1. Overview of Oracle JSON Functions

Oracle Database provides powerful built-in SQL functions to construct JSON data directly from relational tables. These functions allow developers to transform rows and columns into standard JSON objects and arrays efficiently.

2. Core JSON Functions Reference

`JSON_OBJECT`

Description: Converts SQL data into a key-value pair formatted as a JSON object.
Syntax: JSON_OBJECT('key_name' VALUE column_name)
Example:

SELECT JSON_OBJECT('ENAME' VALUE ename)
FROM emp;

Output:

{"ENAME": "KING"}

`JSON_OBJECTAGG`

Description: An aggregate function that groups multiple rows and combines them into a single JSON object document containing multiple key-value pairs.
Notes: IMPORTANT — Commonly used when you want to aggregate child rows into a single JSON payload per group or query result set.
Syntax: JSON_OBJECTAGG(key_expression VALUE value_expression)
Example:

SELECT JSON_OBJECTAGG('ENAME' VALUE ename)
FROM emp;

Output:

{"ENAME": "KING", "ENAME": "BLAKE", "ENAME": "CLARK"}

`JSON_ARRAY`

Description: Evaluates each expression and returns a JSON array containing the values for each row.
Syntax: JSON_ARRAY(column_name)
Example:

SELECT JSON_ARRAY(ename)
FROM emp;

Output:

["KING"]
["BLAKE"]
["CLARK"]

`JSON_ARRAYAGG`

Description: An aggregate function that converts an entire set of rows/information into a single JSON array containing all values.
Notes: IMPORTANT — Ideal for turning a multi-row result set into a single JSON array column in reporting or API development.
Syntax: JSON_ARRAYAGG(column_name)
Example:

SELECT JSON_ARRAYAGG(ename)
FROM emp;

Output:

["KING", "BLAKE", "CLARK"]

3. Reference Table Structure (`EMP` Table)

To practice the queries above, use the standard EMP table structure:

Column Name	Data Type	Description
`EMPNO`	NUMBER(4)	Employee ID (Primary Key)
`ENAME`	VARCHAR2(10)	Employee Name
`JOB`	VARCHAR2(9)	Job Role
`SAL`	NUMBER(7,2)	Salary

4. Interview Preparation & Revision Corner

Q: What is the main difference between JSON_ARRAY and JSON_ARRAYAGG?
- Answer: JSON_ARRAY is a scalar function evaluated per row (producing a separate single-element JSON array for each row), whereas JSON_ARRAYAGG is an aggregate function that merges multiple rows into one comprehensive JSON array.
Q: When should you use JSON_OBJECTAGG instead of regular aggregation functions?
- Answer: Use JSON_OBJECTAGG when you need to serialize relational query results dynamically into standard JSON document formats for web services, REST APIs, or NoSQL migrations directly within Oracle SQL.

Oracle PL/SQL: Regular vs. Pipelined Table Functions

SANDEEP KUMAR — Wed, 23 Sep 2026 16:39:19 +0000

Table functions are specialized PL/SQL functions that return collections of rows and can be queried directly in the FROM clause of a SQL query as if they were physical database tables.

Key Comparison: Regular vs. Pipelined Table Functions

Feature	Regular Table Function	Pipelined Table Function
Memory Allocation	High (buffers entire collection in PGA memory)	Low (streams rows iteratively without holding the entire result set in memory)
Response Time	High Time to First Row (TTFR) — caller waits until processing completes	Low Time to First Row (TTFR) — caller gets rows immediately as they are generated
Syntax Keyword	Standard `RETURN <collection_type>`	Requires `PIPELINED` keyword in function header
Row Emission	Standard assignment into collection	Uses `PIPE ROW (...)` construct
Return Statement	`RETURN collection_variable;`	`RETURN;` (empty return statement)
Best Use Case	Small data sets, lookup operations	Large ETL operations, data transformations, real-time streaming

Database Prerequisites (Object & Table Types)

To query a function using SQL, you must first define named SQL object types at the database level.

-- 1. Create Row Type Object
CREATE OR REPLACE TYPE t_tf_row AS OBJECT (
    id          NUMBER,
    description VARCHAR2(50)
);
/

-- 2. Create Nested Table Type based on the Row Object
CREATE OR REPLACE TYPE t_tf_tab AS TABLE OF t_tf_row;
/

Implementation 1: Regular Table Function

Regular table functions fully populate a collection in memory before returning the entire dataset to the caller.

CREATE OR REPLACE FUNCTION get_tab_tf (
    p_rows IN NUMBER
) RETURN t_tf_tab AS
    l_tab t_tf_tab := t_tf_tab();
BEGIN
    FOR i IN 1..p_rows LOOP
        l_tab.EXTEND;
        l_tab(l_tab.LAST) := t_tf_row(i, 'Description for ' || i);
    END LOOP;

    RETURN l_tab;
END get_tab_tf;
/

Testing the Regular Function:

SELECT * FROM TABLE(get_tab_tf(10));

Implementation 2: Pipelined Table Function

Pipelined table functions stream data back to the calling query immediately upon creation using PIPE ROW.

CREATE OR REPLACE FUNCTION get_tab_ptf (
    p_rows IN NUMBER
) RETURN t_tf_tab PIPELINED AS
BEGIN
    FOR i IN 1..p_rows LOOP
        -- Sends individual row directly to the caller
        PIPE ROW (t_tf_row(i, 'Description for ' || i));
    END LOOP;

    -- Empty RETURN transfers execution control back to the caller
    RETURN;
END get_tab_ptf;
/

Testing the Pipelined Function:

SELECT * FROM TABLE(get_tab_ptf(10));

Important Technical & Interview Notes

IMPORTANT: In a pipelined function, attempting to execute RETURN collection_name; results in a compilation error (PLS-00633: RETURN statement in a pipelined function cannot take an expression). The RETURN; statement must remain empty.
NOTE: Pipelining substantially reduces overall system memory usage and drastically decreases "Time to First Row" for large datasets.
NOTE: Parallel-enabled table functions allow workload splitting across multiple parallel slave processes (PARALLEL_ENABLE clause), accelerating execution during high-volume processing.
IMPORTANT: The TABLE() clause wrapper around a table function in SQL queries is mandatory in Oracle 11g and earlier, but became optional starting from Oracle Database 12c Release 2 (12.2).

-- Valid in Oracle 12c R2 and later without the TABLE() operator:
SELECT * FROM get_tab_ptf(10);

Interview Questions & Answers

Q1: What is the primary operational difference between a Regular and Pipelined Table Function?

Answer: A regular table function constructs the entire result set in Process Global Area (PGA) memory before returning control to the caller. A pipelined table function sends rows back incrementally as they are processed using PIPE ROW, saving PGA memory and significantly reducing the time required to display initial results.

Q2: What happens if you specify a variable inside the RETURN statement of a pipelined function?

Answer: Oracle generates a PL/SQL compilation error (PLS-00633). Pipelined table functions pass data back using PIPE ROW(...), so the final RETURN; statement must be blank to signal execution completion.

Oracle PL/SQL: CASE Expression vs. CASE Statement

SANDEEP KUMAR — Wed, 23 Sep 2026 16:37:37 +0000

Conditional Control: `CASE` Expression vs. `CASE` Statement

Preserved & Corrected Notes

CASE Expression: Evaluates conditional logic and returns a single scalar value.
CASE Statement: Evaluates conditional logic to execute procedural actions/statements.
Structure & Block Inclusion: A CASE statement can form a complete execution control path within a PL/SQL block.
Termination Syntax:
- CASE Statements end with END CASE;
- CASE Expressions end with END

Production Code Examples

1. CASE Expression (Used inside SQL or PL/SQL Assignment)

DECLARE
   v_job_id  VARCHAR2(10) := 'IT_PROG';
   v_bonus   NUMBER;
BEGIN
   -- CASE Expression returns a value assigned directly to v_bonus
   v_bonus := CASE v_job_id
                 WHEN 'IT_PROG'  THEN 1000
                 WHEN 'SA_REP'   THEN 1500
                 ELSE 500
              END;

   DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

2. CASE Statement (Procedural Execution Control)

DECLARE
   v_job_id VARCHAR2(10) := 'IT_PROG';
BEGIN
   -- CASE Statement executes executable statements based on conditions
   CASE v_job_id
      WHEN 'IT_PROG' THEN
         DBMS_OUTPUT.PUT_LINE('Department: Information Technology');
         -- Multiple PL/SQL statements allowed here
      WHEN 'SA_REP' THEN
         DBMS_OUTPUT.PUT_LINE('Department: Sales');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Department: General Support');
   END CASE; -- Terminates with END CASE;
END;
/

Interview Notes & Senior Architect Additions

[!IMPORTANT]
Unhandled Cases Trap (CASE_NOT_FOUND):
In a PL/SQL CASE Statement, if no WHEN clause condition matches and no default ELSE clause is specified, Oracle raises a runtime exception: ORA-06592: CASE not found while executing CASE statement.
In a CASE Expression, if no condition matches and no ELSE is specified, it silently evaluates to NULL.

Oracle SQL: Pseudo-Columns

SANDEEP KUMAR — Wed, 23 Sep 2026 03:03:55 +0000

1. Overview & Core Concepts

Definition: A pseudo-column behaves like a table column, but it is not actually stored on disk in the table.
Capabilities & Restrictions: You can SELECT from pseudo-columns, but you cannot perform INSERT, UPDATE, or DELETE operations on their values.

2. Common Oracle Pseudo-Columns

Oracle provides several built-in pseudo-columns for administrative, navigational, and sequence-based queries:

ROWID: Returns the unique physical address of a row within a database table.
ROWNUM: Assigns a sequential integer (starting from 1) to each row returned by a query result set.
NEXTVAL: Retrieves the next available value from a sequence object.
CURRVAL: Retrieves the current value of a sequence in the current session.
COLUMN_VALUE: Used primarily when querying collection types or table functions like XMLTABLE.
- *Example:*SQL
```
SELECT column_value
FROM (XMLTABLE('<a>123</a>'));
```
ORA_ROWSCN: Returns the conservative upper bound System Change Number (SCN) of the most recent change made to the row. Useful for tracking data staleness or building flashback implementations.
UID & USER: Returns the unique integer user ID and the username of the current user session.
- *Example Output from Dual Table:*SQL
```
SELECT uid, user FROM dual;
```
  UID USER
  
  102 HR
LEVEL: Used with the SELECT ... CONNECT BY hierarchical query clause to organize flat rows into a tree structure. It returns the current depth level of a node within the hierarchy.

UID	USER
102	HR

3. Hierarchical Queries & Tree Pseudo-Columns (`CONNECT BY`)

When working with hierarchical data (such as an employee-manager reporting line), Oracle provides specialized pseudo-columns to inspect tree structures:

Example: Hierarchical Query Structure

SQL

SELECT
    ename,
    empno,
    CONNECT_BY_ISLEAF,
    CONNECT_BY_ISCYCLE,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, '->')
FROM
    emp
CONNECT BY NOCYCLE
    PRIOR empno = mgr;

Explanation of Hierarchical Pseudo-Columns used in Example:

CONNECT_BY_ISLEAF: Returns 1 if the current row is a leaf node (has no children in the tree structure), and 0 otherwise.
CONNECT_BY_ISCYCLE: Returns 1 if the current row has a child that is an ancestor of itself (a loop in the hierarchy), and 0 otherwise. (Requires the NOCYCLE keyword in the CONNECT BY clause to avoid infinite loops).
SYS_CONNECT_BY_PATH(column, char): Returns the path of column values from the root to the current node, separated by the specified character string.

4. Advanced Interview Insights & Frequently Asked Questions

NOTE: Interviewers heavily test pseudo-columns, especially regarding execution order and limitations.

What is the evaluation order of ROWNUM in a query?

ROWNUM is assigned after the WHERE clause is evaluated, but before ORDER BY and GROUP BY clauses. This is why filtering with ROWNUM > 1 directly will always return zero rows (since row 1 fails the condition, it is discarded, and the next row becomes row 1). To handle pagination safely with ROWNUM, you must use an inline view or subquery that encapsulates the ORDER BY.

Can you create your own custom pseudo-columns?

No, pseudo-columns are system-defined by Oracle. However, virtual columns (introduced in Oracle 11g) allow you to define custom expression-based columns that are stored logically or physically as metadata.

What is the difference between ROWID and ROWNUM?

ROWID is a permanent, unique physical address of a row on disk (stable across transactions until the row is deleted or moved). ROWNUM is temporary, dynamic, and assigned on-the-fly to rows as they are fetched into the result set during statement execution.

Can you create an index on a ROWNUM or ROWID pseudocolumn?

Answer: You cannot index ROWNUM, but ROWID acts as the implicit physical primary address of every row and is inherently indexed via the rowid access path. You can, however, create indexes on Virtual Columns.

What is the purpose of CONNECT_BY_ISCYCLE?

Answer: When performing hierarchical queries with loops in the data, CONNECT_BY_ISCYCLE returns 1 if the current row has a child that is also its ancestor (a loop), preventing infinite loops when paired with the NOCYCLE clause.

IMPORTANT NOTES FOR INTERVIEW:

Virtual columns do not consume storage space for data (unlike regular columns), but metadata is stored in the dictionary, and they can be indexed to improve query performance.

ROWNUM is assigned before any sorting or aggregation happens in a query. If you use ORDER BY with ROWNUM, rows are numbered arbitrarily unless wrapped in a subquery.

💡 Practical Code Example: Hierarchical Queries with Pseudocolumns

SQL

SELECT
    ename,
    empno,
    CONNECT_BY_ISLEAF,
    CONNECT_BY_ISCYCLE,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, '->') AS hierarchy_path
FROM emp
CONNECT BY NOCYCLE PRIOR empno = mgr;

⚖️ Difference: Pseudocolumn vs. Virtual Column

Feature	Pseudocolumn	Virtual Column
Table Storage	Not part of the table definition/storage.	Physically part of the table metadata structure, though values are derived on-the-fly.
Value Derivation	Generated dynamically by the query engine (e.g., `ROWNUM`, `LEVEL`).	Derived from expressions/other columns of the same table.
Indexing	Cannot be indexed directly.	Can be indexed (including B-tree and function-based indexes).
DML Operations	Selection only (No INSERT/UPDATE/DELETE).	Selection only; values are managed automatically by the database via expressions (No direct DML on the virtual column).

🏗️ Table Structure Example: Virtual Columns

CREATE TABLE Test (
    a   NUMBER,
    b   NUMBER,
    SUM AS (a + b),  -- Virtual Column
    Sub AS (a - b),  -- Virtual Column
    Mul AS (a * b),  -- Virtual Column
    Div AS (a / b)   -- Virtual Column
);

Oracle SQL: Read-Only Tables

SANDEEP KUMAR — Wed, 23 Sep 2026 02:52:55 +0000

1. Overview & Core Concepts

Definition: Oracle allows you to restrict tables to read-only mode, blocking all Data Modification Language (DML) operations (such as INSERT, UPDATE, DELETE) and certain Data Definition Language (DDL) modifications while permitting SELECT queries.
Primary Use Cases: Protecting reference data, historical archives, compliance data retention, and locking master tables during maintenance or migration windows.

2. Methods to Make a Table Read-Only or Restrict Data Modifications

There are multiple approaches in Oracle to enforce read-only behavior on table data, ranging from native table states to permissions, views, and triggers.

Method 1: Using Native Table Read-Only State (`ALTER TABLE ... READ ONLY`)

This is the cleanest, built-in feature introduced in Oracle to directly set a table's state to read-only.

Syntax:SQL
```
ALTER TABLE emp READ ONLY;
```
Reverting to Read-Write:SQL
```
ALTER TABLE emp READ WRITE;
```
Behavior & Error Handling:
- If you attempt to insert or update records on a read-only table, Oracle throws a direct error:
  - SQL Error: ORA-12081: update operation not allowed on table "EMP"

Method 2: Granting Restricted Privileges Across Schemas

You can isolate the master table in a dedicated schema (Schema1 / User-A) and grant only SELECT privileges to other users (Schema2 / User-B).

Setup & Granting Privilege:SQL

-- Connected as Schema1 (User-A)
GRANT SELECT ON emp TO user_b;

Behavior from User-B's Schema:SQL
```
-- Connected as Schema2 (User-B)
INSERT INTO user_a.emp VALUES (..., ...);
```
- Error Output: ORA-01031: insufficient privileges (or general insufficient privileges error).

Method 3 & 4: Creating Views (Standard or Read-Only Views)

You can revoke direct DML access to the base table and expose data strictly via a view.
Starting in Oracle, you can explicitly create a read-only view to block DML directly through the view
```
CREATE OR REPLACE VIEW emp_ro_view AS
SELECT * FROM emp
WITH READ ONLY;
```

Method 5: Using Statement-Level Triggers

You can implement a database trigger to intercept and block any DML transactions at the statement level.

Syntax & Example:SQL

CREATE OR REPLACE TRIGGER tr_block_dml
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Table is read-only. DML operations are not allowed.');
END;
/

3. Summary Comparison of Read-Only Approaches

Method	Pros	Cons / Limitations
`ALTER TABLE READ ONLY`	Native, high-performance, dictionary-enforced, prevents accidental DML by table owners/admins without switching mode.	Requires exclusive table access lock to change state.
Schema Privileges	Clean separation of duties; non-owners cannot write.	Owner of the schema can still perform DML.
Read-Only Views (`WITH READ ONLY`)	Granular column/row filtering while keeping base table secured.	Users with direct table access can still modify the base table.
Triggers	Highly customizable; can include conditional logic (e.g., allow specific users).	Overhead on every DML statement; can be disabled by users with `ALTER TRIGGER` privileges.

4. Advanced Interview Insights & Frequently Asked Questions

NOTE: Interviewers frequently probe into the metadata behavior and edge cases of read-only tables.

Can you drop or truncate a read-only table?

No. DDL operations that modify the table structure or contents (like DROP TABLE or TRUNCATE TABLE) are blocked when a table is in READ ONLY mode. You must first alter the table back to READ WRITE (ALTER TABLE tab_name READ WRITE;) before dropping or truncating.

Can indexes be created or rebuilt on a read-only table?

Dropping or creating indexes is generally restricted while the table is read-only because it modifies the table's segment metadata.

How can you verify if a table is currently in read-only mode?

You can query the READ_ONLY column from data dictionary views like USER_TABLES, ALL_TABLES, or DBA_TABLES

    ```
    SELECT table_name, read_only
    FROM user_tables
    WHERE table_name = 'EMP';
    ```

Oracle SQL: Unused Columns & Virtual Columns

SANDEEP KUMAR — Wed, 23 Sep 2026 02:50:36 +0000

1. Unused Columns (`SET UNUSED`)

Overview & Concept

Definition: Marking a column as UNUSED is a form of logical deletion. The column metadata is hidden, but the physical data remains untouched in the table blocks.
Performance Benefit: Unlike a traditional DROP COLUMN (which locks the table and rewrites every data block to reclaim space immediately), SET UNUSED is an instant metadata-only operation. This prevents long locks and heavy I/O overhead on large production tables.

Key Characteristics

Metadata Hiding: Once a column is marked as unused, it cannot be viewed via the DESC command or standard SELECT * queries.
Reusing Column Names: Because Oracle internally renames unused columns in the data dictionary with system-generated names (e.g., SYS_00001_...), you can immediately create a new column with the same name on the table.
Reversibility: You cannot directly "un-set" an unused column using a simple undo command. To reclaim space permanently, the column must eventually be dropped.

Core SQL Operations & Examples

1. Mark a Column as Unused:SQL

ALTER TABLE cust_details
SET UNUSED (cust_account_number

2. View Unused Columns:SQL

To inspect unused columns in a table, query data dictionary views such as DBA_UNUSED_COL_TABS, USER_UNUSED_COLS, or use session settings:
```
SET COLINVISIBLE ON;
DESC cust_details;
*(Alternatively, query `USER_UNUSED_COLS` to see table names and counts of unused columns).*
```
3. Drop All Unused Columns Permanently:SQL

This command physically removes the data and reclaims disk space. It can be resource-intensive on large tables:
```
ALTER TABLE cust_details
DROP UNUSED COLUMNS;
```

2. Virtual Columns

Overview & Concept

Definition: A virtual column is a derived column defined in a table with an underlying expression that computes values from other physical columns in the same row.
Storage Behavior: Virtual columns do not store data physically on disk by default. Instead, the database evaluates the expression dynamically at runtime. (Note: Oracle also supports STORED virtual columns if persistence is required for indexing or performance).

Rules, Limitations, and Restrictions

DML Restrictions: You cannot perform direct DML operations (INSERT, UPDATE, DELETE) on virtual columns. Their values are managed entirely by the underlying expression.
Table Type Support: Virtual columns can only be defined on Heap-organized tables. They are not supported for external tables, Index-Organized Tables (IOT), clustered tables, or temporary tables.
Dependencies: A virtual column cannot reference another virtual column; it must reference physical columns within the same table.
Data Type: The expression for a virtual column must evaluate to a scalar data type only.
Partitioning: Virtual columns can be used as partition keys (e.g., partitioning a table by a year extracted from a date column).

Indexing Virtual Columns

You can define indexes on virtual columns.
These indexes behave identically to Function-Based Indexes, allowing the optimizer to pre-calculate and index the results of the virtual column expression for faster retrieval.

SQL Syntax & Example

SQL

CREATE TABLE t1 (
    id   NUMBER,
    col1 NUMBER,
    col2 NUMBER,
    col3 NUMBER GENERATED ALWAYS AS (col1 * col2) VIRTUAL
);

3. Senior Architect & Interview Masterclass

What is the difference between DROP COLUMN and SET UNUSED?
- DROP COLUMN immediately removes the column definition and physically purges the data from all blocks, which can cause severe locking and performance degradation on large tables.
- SET UNUSED instantly updates the data dictionary metadata, marking the column as dead without touching the physical blocks. Space reclamation is deferred until ALTER TABLE ... DROP UNUSED COLUMNS is executed manually (often scheduled during maintenance windows)[cite: 5].
Can you add a primary key constraint or unique constraint on a Virtual Column?
- Yes! If the underlying expression guarantees uniqueness or non-null behavior, you can add constraints (like NOT NULL or UNIQUE) and create indexes on virtual columns, making them extremely powerful for enforcing business rules without duplicating storage.
What happens to indexes when you mark a column as UNUSED?
- If the column was part of any indexes, those indexes become unusable/invalid and must be dropped or rebuilt after dropping the unused columns permanently.

DEV Community: SANDEEP KUMAR

Oracle SQL: JSON Functions

1. Overview of Oracle JSON Functions

2. Core JSON Functions Reference

JSON_OBJECT

JSON_OBJECTAGG

JSON_ARRAY

JSON_ARRAYAGG

3. Reference Table Structure (EMP Table)

4. Interview Preparation & Revision Corner

Oracle PL/SQL: Regular vs. Pipelined Table Functions

Key Comparison: Regular vs. Pipelined Table Functions

Database Prerequisites (Object & Table Types)

Implementation 1: Regular Table Function

Implementation 2: Pipelined Table Function

Important Technical & Interview Notes

Interview Questions & Answers

Oracle PL/SQL: CASE Expression vs. CASE Statement

Conditional Control: CASE Expression vs. CASE Statement

Preserved & Corrected Notes

Production Code Examples

1. CASE Expression (Used inside SQL or PL/SQL Assignment)

2. CASE Statement (Procedural Execution Control)

Interview Notes & Senior Architect Additions

Oracle SQL: Pseudo-Columns

1. Overview & Core Concepts

2. Common Oracle Pseudo-Columns

3. Hierarchical Queries & Tree Pseudo-Columns (CONNECT BY)

Example: Hierarchical Query Structure

Explanation of Hierarchical Pseudo-Columns used in Example:

4. Advanced Interview Insights & Frequently Asked Questions

💡 Practical Code Example: Hierarchical Queries with Pseudocolumns

⚖️ Difference: Pseudocolumn vs. Virtual Column

🏗️ Table Structure Example: Virtual Columns

Oracle SQL: Read-Only Tables

1. Overview & Core Concepts

2. Methods to Make a Table Read-Only or Restrict Data Modifications

Method 1: Using Native Table Read-Only State (ALTER TABLE ... READ ONLY)

Method 2: Granting Restricted Privileges Across Schemas

Method 3 & 4: Creating Views (Standard or Read-Only Views)

Method 5: Using Statement-Level Triggers

3. Summary Comparison of Read-Only Approaches

4. Advanced Interview Insights & Frequently Asked Questions

Oracle SQL: Unused Columns & Virtual Columns

1. Unused Columns (SET UNUSED)

Overview & Concept

Key Characteristics

Core SQL Operations & Examples

2. Virtual Columns

Overview & Concept

Rules, Limitations, and Restrictions

Indexing Virtual Columns

SQL Syntax & Example

3. Senior Architect & Interview Masterclass

`JSON_OBJECT`

`JSON_OBJECTAGG`

`JSON_ARRAY`

`JSON_ARRAYAGG`

3. Reference Table Structure (`EMP` Table)

Conditional Control: `CASE` Expression vs. `CASE` Statement

3. Hierarchical Queries & Tree Pseudo-Columns (`CONNECT BY`)

Method 1: Using Native Table Read-Only State (`ALTER TABLE ... READ ONLY`)

1. Unused Columns (`SET UNUSED`)