Oracle PL/SQL Interview questions - Basic to Advanced

Explore PL/SQL as a procedural extension of SQL, with blocks from declare to end, control structures, stored procedures and functions, data types, bind variables, and context switches.

Explore PL/SQL control structures, including conditional, iterative, and sequential forms; compare simple, while, and for loops, and review continue, goto, and stepping by five.

Understand how cursors in oracle track and manage result sets from select and dml statements, including implicit and explicit cursors, cursor attributes, and row counts.

Explore exception handling in Oracle PL/SQL, including causes and the three types—internally defined, predefined, and user defined—plus pragma exception_init, error code ranges, and handling bad records with nested blocks.

Explore procedure and function restrictions, definer versus invoker rights, no copy and deterministic options, invoker rights examples, and using user_tab_columns to compare schema table structures.

Examine when functions can be called in sql, including dml/ddl restrictions, check constraints, and parameter limits; compare definer and invoker rights, and cover no copy and deterministic function behavior.

Discover the advantages of packages: organized code, easy changes, information hiding, and library cache performance, plus forward declarations, scope, overloading, package-level autonomous transactions, and built-in packages.

Explore how Oracle PL/SQL triggers run implicitly on database events, including before, after, and instead-of types, with DML, DDL, and data dictionaries.

Explore advanced Oracle PL/SQL triggers, including firing order with follows, disabling during bulk loads, instead of triggers on views, and mutating errors with compound triggers.

Explore Oracle collections, a composite data type with elements of the same type. Identify the three collection types—associative arrays, nested tables, and varying arrays—and their storage and deletion rules.

Explore dynamic SQL as a runtime generator of SQL statements, covering DDL and DML operations, ACL session control, and bind variables to prevent SQL injection; understand execution flow.

Explore local and remote object dependencies, including direct and indirect links; learn to identify dependencies with data dictionary tools, compile invalid objects, and understand timestamp- and signature-based remote dependency tracking.

Explore context switching between SQL and PL/SQL engines, and master bulk collect and limit clause handling to fetch data into a collection, optimize performance, and manage bulk exceptions.

Discover how table functions convert a collection into tabular data and how pipeline functions speed up row delivery with parallel, partitioned execution and reduced staging.

Explore advanced PL/SQL features in Oracle 21c, including function results cache, extended iterators and SQL macro usage, with notes on suppression of warnings and non-persistable types.

Learn pl sql tuning and profiling to optimize procedures and functions using explain plan, oracle trace, bulk collect, bind variables, no copy, and pipelining; profile with dbms_profiler to identify bottlenecks.