PL/SQL Questions and Answers - 3

 

Difference between BULK COLLECT and FORALL

BULK COLLECT:
- Retrieves multiple rows from a query into a PL/SQL collection in one go.
- Improves performance by reducing context switches between SQL and PL/SQL engines.
Example:
SELECT empno BULK COLLECT INTO v_ids FROM emp;

FORALL:
- Executes DML statements for all elements in a collection.
- Also reduces context switching and improves performance.
Example:
FORALL i IN 1..v_ids.COUNT
  DELETE FROM emp WHERE empno = v_ids(i);

 

PL/SQL Data Types in Oracle

1. Scalar Types: NUMBER, VARCHAR2, DATE, BOOLEAN, etc.
2. Composite Types: RECORD, TABLE, VARRAY, Nested Tables
3. Reference Types: REF CURSOR
4. LOB Types: BLOB, CLOB, NCLOB, BFILE
5. User-defined Types: OBJECT types, Custom RECORDs

 

What is a Collection in Oracle

A collection is a composite data type for storing multiple values like arrays.
Types:
- Associative Arrays (Index-by tables)
- Nested Tables
- VARRAYs

Uses: BULK COLLECT, FORALL, parameter passing, temporary storage.

 

Types of Memory in Oracle Database

1. System Global Area (SGA): Shared memory (Buffer Cache, Shared Pool, Redo Log Buffer, etc.)
2. Program Global Area (PGA): Private memory for each session (sorting, hash joins, etc.)
3. User Global Area (UGA): Session memory, stored in PGA or SGA based on connection mode.
4. Software Code Area: Stores Oracle server code in memory.

 

Types of Views in Oracle

1. Simple View - One table, no joins or groups
2. Complex View - Joins, aggregates, functions
3. Inline View - Subquery in FROM clause
4. Materialized View - Stores physical data
5. Read-Only View - Prevents DML
6. Updatable View - Allows DML under conditions
7. Object View - Based on object types

 

Types of Refresh in Materialized View

1. FAST - Uses materialized view log for incremental refresh
2. COMPLETE - Refreshes entire data
3. FORCE - Tries FAST, then COMPLETE if not possible
4. ON DEMAND - Manually refresh via DBMS_MVIEW.REFRESH
5. SCHEDULED - Refreshes periodically (e.g., hourly, daily)

 

Incremental Update Without MERGE

Step 1: Update existing rows
UPDATE target SET col = (SELECT col FROM source WHERE key = target.key)
WHERE EXISTS (SELECT 1 FROM source WHERE key = target.key);

Step 2: Insert new rows
INSERT INTO target SELECT * FROM source
WHERE NOT EXISTS (SELECT 1 FROM target WHERE key = source.key);

Optional Step 3: Delete missing rows
DELETE FROM target WHERE NOT EXISTS (SELECT 1 FROM source WHERE key = target.key);