PL/SQL Interview Questions with Examples

Study the four sql statement categories—dml, transaction control, data definition language, and constraints—and compare primary key with unique, delete with truncate, and second maximum via subqueries.

Explore the difference between where and having clauses, aggregate limitations, and group by in PL/SQL with practical examples of summing bonuses by employee.

Learn PL/SQL basics and join types (left outer, full, self, cartesian) plus the block structure (declare, begin, end, optional exception) and removing duplicates with partition by and delete.

Explore essential PL/SQL concepts through practical Q&A on anonymous blocks, stored procedures versus functions, and autonomous transactions, including how independent commits affect outer transactions.

Explains mutating table errors when a trigger updates a column during a select on the same table and introduces a merge that updates on matches and inserts on not matched.

Learn how autonomous transactions in PL/SQL run independently, enabling isolated inserts via a stored procedure or function, with exception handling that keeps other transactions unaffected.

Explore materialized views and snapshots for cross-site data replication. Learn refresh options—fast, complete, on demand, and immediate or deferred—and the role of materialized view logs.

Explore cursor fundamentals in PL/SQL, including implicit and explicit cursors, open-fetch-close operations, and attributes like %rowcount and %found with practical examples.

Explains using explicit cursors in PL/SQL, declares and opens a cursor, fetches rows, handles the not found attribute and the for update clause, and closes the cursor while processing results.

Explore cursor usage in PL/SQL: handle open and fetch states, select into single values or bulk collect into collections, and loop through results with implicit and explicit cursors.

Explore ref cursors in PL/SQL, using typed cursor variables for flexible queries, with strong and weak typing, and pass cursor variables to procedures.

Demonstrates bulk operations in PL/SQL by building and populating a collection, then using a for all statement to insert 10,000 values in one go, instead of looping.

Explore PL/SQL triggers, including before and after triggers, row-level and statement-level, with examples of triggering events, when clauses, new and old values, and conditional actions.

This lecture explains statement-level triggers, including before and after triggers, and the distinction between for each row and for each statement. It also covers instead of triggers and trigger sequencing.

Explore instead of triggers on views, enabling insert, update, or delete through trigger bodies, and learn which view constructions become non updateable, with an example using a manager view.

Learn to use dynamic SQL in PL/SQL with execute immediate, bind variables, and placeholders to run select and DDL statements, including returning into and input-output bindings.

Learn dynamic sql with dbms_sql part 2 in pl/sql, comparing execute immediate, and master the execution flow: open, bind, define column, execute, fetch, and close.

explains using dbms_sql to copy data from a source table to a destination table, employing open, define, bind variables, execute, fetch, and loop constructs with error handling.

Learn explain plan concepts for performance tuning, view execution plans, compare cost versus performance, and see how index scans and joins affect SQL query optimization.

Learn how Oracle hints guide query optimization and performance tuning by influencing execution plans. Explore how hints affect index vs full table scans, join methods, and plan choices.

Explore the dbms_pipe package to create and use pipes for inter-session messaging, including implicit and explicit pipes, public and private types, with send and receive procedures.

Explore the dbms_utility package, including active instances, compiled schema, current connected instances, version reporting, dynamic sql execution, dependencies, and a time function for performance tuning in clustered databases.

Use the fearless DBMS profiler to analyze Oracle code execution, identify hot spots, and uncover bottlenecks with line-level statistics and start-stop profiling.

Explore Oracle lob data types, such as blob and clob, and learn to manipulate large objects with dbms_lob: creating tables, inserting, updating, and reading using functions and procedures.

Explore how to use the DBMS_JOB package to submit and schedule jobs, set next execution times, modify or disable jobs, and remove them when needed.

Uncover how to use the DBMS_RANDOM package in Oracle to generate pseudo random numbers and strings, control seeds, and apply value and string functions, with notes on initialization history.

Explore how to generate and format text output in pl/sql with the dbms_output package, including emitting multiple lines and handling newlines in messages. See practical examples of line-by-line output.

Learn how to use the UTL_FILE package to open files, read lines, and perform file operations in PL/SQL, with practical examples from the course.