Phani Adivi—OracleOps AI

PL/SQL Topics

What do you want to learn? Pick a topic — each one gets a focused answer with a code example, not a wall of everything at once.

Don't see your topic? Ask Oracle directly.

Language Basics

Variables and Data Types Constants and %TYPE / %ROWTYPE IF and CASE Loops (Basic, WHILE, FOR)Nested Blocks and Labels GOTO and CONTINUE Block Structure and Comments BOOLEAN and Three-Valued Logic PLS_INTEGER, BINARY_INTEGER and SIMPLE_INTEGER User-Defined SUBTYPEs The NULL Statement Identifier Scope and Qualification PL/SQL Records Expressions and Operator Precedence

String & Data Functions

SUBSTR and INSTR REPLACE and TRANSLATE TRIM, LPAD, RPAD Regular Expressions TO_CHAR, TO_DATE, TO_NUMBER NVL, NVL2, COALESCE, NULLIF TO_CHAR, TO_DATE and TO_NUMBER Format Models Date Arithmetic Functions SYSDATE vs SYSTIMESTAMP TIMESTAMP and INTERVAL Types EXTRACT and Date Parts ROUND and TRUNC LISTAGG CASE Expressions and DECODE

Cursors & Bulk Operations

Explicit Cursors Cursor FOR Loops Cursor Attributes (%FOUND, %ROWCOUNT)Parameterized Cursors REF Cursors (Strong vs. Weak)BULK COLLECT FORALL BULK COLLECT with LIMIT SAVE EXCEPTIONS in FORALL Implicit vs Explicit Cursors FOR UPDATE and WHERE CURRENT OF Returning Result Sets with SYS_REFCURSOR FORALL with INDICES OF and VALUES OF BULK COLLECT INTO a Collection of Records Cursor FOR Loop Record Variable

Packages & Modularity

Package Spec vs. Body Procedures vs. Functions IN, OUT, IN OUT Parameters Overloading Package-Level (Global) Variables Package Initialization Blocks Forward Declarations PRAGMA SERIALLY_REUSABLE Local (Nested) Subprograms Default Parameters and Named Notation Shared Constants, Types and Cursors in a Package Spec Recursive Subprograms

Triggers

BEFORE vs. AFTER Triggers Row-Level vs. Statement-Level INSTEAD OF Triggers Compound Triggers Mutating Table Errors DML, DDL and Database Event Triggers :NEW and :OLD Pseudorecords The Trigger WHEN Clause Trigger Ordering with FOLLOWS and PRECEDES Enabling and Disabling Triggers Trigger Firing Order for One Statement

Exception Handling

Predefined Exceptions User-Defined Exceptions RAISE_APPLICATION_ERROR SQLCODE and SQLERRM PRAGMA EXCEPTION_INIT RAISE_APPLICATION_ERROR Exception Propagation FORMAT_ERROR_BACKTRACE and the Error Stack WHEN OTHERS and Re-Raising NO_DATA_FOUND vs TOO_MANY_ROWS Handling an Exception Inside a Loop

Collections

Associative Arrays (Index-By Tables)Nested Tables VARRAYs Collection Methods (COUNT, EXISTS, DELETE, EXTEND)Choosing Between the Three Collection Types MULTISET Operators Navigating a Sparse Collection Collections of Records Associative Arrays Indexed by VARCHAR2

SQL Integration

CTEs (WITH Clause)Scalar & Correlated Subqueries UNION, INTERSECT, MINUS MERGE Statement Analytic (Window) Functions Dynamic SQL (EXECUTE IMMEDIATE)SELECT ... INTO RETURNING INTO The DBMS_SQL Package Bind Variables and SQL Injection COMMIT, ROLLBACK and SAVEPOINT The WITH FUNCTION Clause

Advanced & Performance

Autonomous Transactions Pipelined Table Functions Object Types DBMS_OUTPUT and Debugging Invoker Rights vs. Definer Rights The NOCOPY Parameter Hint The PL/SQL Function Result Cache The DETERMINISTIC Clause Conditional Compilation PLSQL_OPTIMIZE_LEVEL and Inlining PRAGMA UDF DBMS_APPLICATION_INFO