Oracle SQL and PL/SQL Interview and Concept Notes - 02 July 2025 - 7

What is a Cursor and Its Types

In PL/SQL, a cursor is a pointer to the result set of a query. It allows row-by-row processing.

Types of Cursors:
1. Implicit Cursor – Automatically created for single-row queries.
2. Explicit Cursor – Developer-defined for multi-row queries.
3. Cursor FOR Loop – Simplified looping over query results.
4. Parameterized Cursor – Accepts input parameters.
5. REF Cursor – Dynamic pointer to query results.

Example of Explicit Cursor:
DECLARE
  CURSOR c_emp IS SELECT empno, ename FROM emp;
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_empno, v_ename;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
  END LOOP;
  CLOSE c_emp;
END;

Cursor Attributes

1. %FOUND – TRUE if last fetch/DML affected rows.
2. %NOTFOUND – TRUE if no rows fetched/affected.
3. %ROWCOUNT – Number of rows fetched/affected.
4. %ISOPEN – TRUE if explicit cursor is currently open.

Example:
UPDATE emp SET sal = sal + 100 WHERE deptno = 10;
IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
ELSE
  DBMS_OUTPUT.PUT_LINE('No rows updated.');
END IF;

Mutating Table Error

Occurs when a row-level trigger tries to read/write from the same table it's modifying.

Error: ORA-04091

Solutions:
1. Use statement-level triggers.
2. Use compound triggers.
3. Use :NEW and :OLD values.
4. Use autonomous transaction procedures.

Example (Avoiding Mutating Error):
Use compound trigger with memory table to collect and process after statement.

Query for Employees Earning More Than Department Average

SELECT empno, ename, deptno, sal
FROM emp e
WHERE sal > (
  SELECT AVG(sal) FROM emp WHERE deptno = e.deptno
);

Types of Analytical Functions

1. Ranking: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
2. Aggregate: SUM(), AVG(), COUNT(), MIN(), MAX()
3. Value: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()
4. Distribution: PERCENT_RANK(), CUME_DIST()
5. Statistical: STDDEV(), VARIANCE(), CORR(), COVAR_POP(), COVAR_SAMP()

DENSE_RANK() Function

DENSE_RANK() assigns rank to rows without skipping ranks for ties.

Example:
SELECT sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rnk FROM emp;

3rd Maximum Salary Query

Method using DENSE_RANK():
SELECT sal FROM (
  SELECT sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
) WHERE rnk = 3;

Maximum Salary Query

SELECT MAX(sal) AS max_salary FROM emp;

To fetch employee(s) with max salary:
SELECT empno, ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);

Constraints and Types

1. PRIMARY KEY – Unique and NOT NULL.
2. UNIQUE – No duplicate values.
3. NOT NULL – Must contain value.
4. CHECK – Validates condition.
5. FOREIGN KEY – Enforces relationship with another table.

Yellow Interface

"Yellow interface" refers to ODI 11g's old data integration interface, which had a yellow UI. In ODI 12c, it's replaced by Mappings.

Bulk Collect

Used to fetch multiple rows into collections in one go to improve performance.

Example:
SELECT empno BULK COLLECT INTO v_empnos FROM emp;

Types of Errors in PL/SQL

1. Syntax Errors – Compile-time issues.
2. Compilation Errors – Incorrect declarations.
3. Runtime Errors – At execution time.
4. Logical Errors – Wrong result, no crash.
5. Server Errors – ORA-xxxxx codes.
6. User-Defined Errors – Raised using RAISE or RAISE_APPLICATION_ERROR.

Click for full details in ChatGPT