WorksheetsPL/SQL Concepts Quiz
Total questions: 20
Worksheet time: 10mins
What is the benefit of using a FOR loop with a cursor in PL/SQL?
Requires manual fetch and close
Better for DDL statements
Automatically opens, fetches, and closes the cursor
Supports dynamic SQL
Why use a REF cursor in PL/SQL?
To return static result sets only
To return dynamic result sets from queries
For use with DDL only
To optimize exception handling
Which statement is true about stored procedures?
They must return a value
They are only executed from SQL*Plus
They can accept IN, OUT, IN OUT parameters
They cannot contain exception handling
Which of the following is true about PL/SQL functions?
They return multiple values
They cannot use parameters
They must return a single value
They cannot include SQL queries
What should be ensured when performing DML operations inside overloaded procedures or functions?
Avoid transactions
Only SELECT statements are allowed
Proper logic separation and use of autonomous transactions if needed
DML is not supported in overloaded functions
What is one key feature of PL/SQL packages?
They store binary data
They compile on every call
They encapsulate procedures, functions, and variables
They cannot have global variables
What is the difference between a package specification and a body?
The spec is not visible to users
The body defines the implementation
The spec includes logic
There is no difference
What does PRAGMA AUTONOMOUS_TRANSACTION do?
Makes transaction dependent on the caller
Rolls back after completion
Allows the block to commit or rollback independently
Prevents DML operations
Which trigger type allows data validation before insert?
AFTER INSERT
BEFORE INSERT
INSTEAD OF INSERT
DDL Trigger
What is a typical use case of an INSTEAD OF trigger?
Preventing SQL injection
Allowing DML on views
Logging DDL changes
Handling compilation errors
How can the mutating table error be resolved in row-level triggers?
Using INSTEAD OF triggers
By disabling the trigger
Writing all logic in SQL
Committing inside the trigger
What is a PL/SQL record?
A database table
A single variable
A user-defined composite data type
A system-defined array
Which collection type allows string-based indexing?
VARRAY
Nested Table
Associative Array
CLOB
What is the use of BULK COLLECT in PL/SQL?
To slow down data fetches
To fetch multiple rows into collections in a single context switch
To validate data types
To group multiple exceptions
FORALL is used with BULK COLLECT for:
Reading CLOB data
DDL execution
Improving performance in DML operations
Declaring cursors
What is the purpose of WHEN OTHERS THEN in PL/SQL?
To catch no exceptions
To catch user-defined exceptions only
To catch all raised exceptions
To stop function execution
What package is commonly used to execute dynamic SQL in PL/SQL?
DBMS_SQL
UTL_FILE
DBMS_RANDOM
DBMS_ALERT
What's the main purpose of using bind variables in dynamic SQL?
For styling output
For logging SQL
For performance and avoiding SQL injection
For enabling row locks
What differentiates a PL/SQL function from a procedure?
Procedures are only for triggers
Functions can't be reused
Functions return a value; procedures don't
Functions are always slower
Which of the following is true about global temporary tables in Oracle?
Data persists after the session ends
Structure and data are both temporary
Data is session or transaction-specific
They can't be used in PL/SQL blocks
