PL/SQL Questions and Answers for Viva

Answer-1: PL/SQL is Oracle's procedural extension to SQL, allowing programming constructs like loops, conditions, and error handling.

Answer-2: SQL is a query language for interacting with databases, while PL/SQL adds procedural capabilities like loops and exception handling.

Answer-3: Benefits include block structure, error handling, improved performance, and reusability.

Answer-4: A PL/SQL block consists of three sections: Declaration, Execution, and Exception handling.

Answer-5: Cursors allow you to retrieve and manipulate query results row by row.

Answer-6: Implicit cursors are automatically created by Oracle, while explicit cursors are defined by the programmer for complex queries.

Answer-7: An exception is an error condition that interrupts normal program execution.

Answer-8: Exceptions are either predefined (e.g., NO_DATA_FOUND) or user-defined.

Answer-9: A trigger is a stored procedure that automatically executes in response to certain events on a table or view.

Answer-10: A function returns a value, while a procedure does not return a value but can have output parameters.

Answer-11: A package is a collection of related procedures, functions, variables, and other elements grouped together.

Answer-12: Variables are declared using the syntax: variable_name datatype [:= initial_value];.

Answer-13: %TYPE is used to declare a variable with the same data type as a column in a table.

Answer-14: %ROWTYPE is used to declare a variable that can hold an entire row of a table or query result.

Answer-15: Collections are data types like arrays, including associative arrays, nested tables, and VARRAYs.

Answer-16: Nested tables are unbounded and can be sparse, while VARRAYs are bounded and ordered collections.

Answer-17: Types include conditional (e.g., IF-THEN-ELSE), iterative (e.g., LOOP, FOR), and sequential control.

Answer-18: Dynamic SQL allows execution of SQL statements that are constructed at runtime using the EXECUTE IMMEDIATE or DBMS_SQL package.

Answer-19: The DBMS_OUTPUT package is used for debugging by displaying output messages.

Answer-20: It associates a user-defined exception with an Oracle error number.

Answer-21: Loops can be implemented using LOOP, FOR, or WHILE constructs.

Answer-22: PL/SQL records are composite data types that can hold multiple fields of different data types.

Answer-23: Errors are handled using the EXCEPTION block with predefined or user-defined exceptions.

Answer-24: A pragma is a compiler directive that provides instructions to the PL/SQL compiler.

Answer-25: RAISE re-throws an exception, while RAISE_APPLICATION_ERROR is used to define custom error messages.

Answer-26: The MERGE statement is used to perform insert, update, or delete operations in a single statement.

Answer-27: An autonomous transaction is a separate transaction that can be committed or rolled back independently of the main transaction.

Answer-28: Declare an explicit cursor using CURSOR cursor_name IS query;.

Answer-29: OPEN initializes the cursor, while FETCH retrieves rows from the result set.

Answer-30: A REF cursor is a cursor variable that can hold the result set of a query dynamically.

Answer-31: The FORALL statement allows bulk binding for executing multiple DML statements.

Answer-32: SAVEPOINT marks a point in a transaction to which you can later roll back.

Answer-33: Mutating tables are tables that are being modified by a DML operation and cannot be queried or modified further during the same transaction.

Answer-34: Use the CREATE TRIGGER statement, specifying the timing (BEFORE/AFTER) and event (e.g., INSERT).

Answer-35: BULK COLLECT retrieves multiple rows from a query into a collection in a single operation.

Answer-36: ROWNUM is assigned during query processing, while ROW_NUMBER() assigns numbers based on specified order.

Answer-37: LOCK TABLE is used to lock a table to prevent concurrent modifications.

Answer-38: The RETURNING clause retrieves values from DML operations into variables or collections.

Answer-39: A materialized view is a database object that stores the result of a query for performance improvement.

Answer-40: A context switch occurs when control moves between SQL and PL/SQL engines, affecting performance.

Answer-41: Use techniques like bulk binding, avoiding context switches, and using indexed collections.

Answer-42: A compound trigger allows defining multiple timing sections in a single trigger.

Answer-43: %FOUND returns TRUE if a DML operation affects rows, while %NOTFOUND returns TRUE if no rows are affected.

Answer-44: A dynamic PL/SQL block is a PL/SQL code block constructed and executed at runtime using EXECUTE IMMEDIATE.

Answer-45: CASE statements provide conditional logic within SQL or PL/SQL code.

Answer-46: INSTEAD OF triggers are used to perform DML operations on views that are not inherently updatable.

Answer-47: COMMIT saves changes to the database, while ROLLBACK undoes changes since the last commit.

Answer-48: Parameters like PLSQL_OPTIMIZE_LEVEL and PLSQL_WARNINGS control PL/SQL behavior and performance.

Answer-49: SQLERRM returns the error message, while SQLCODE returns the numeric error code.

Answer-50: Debugging methods include using DBMS_OUTPUT, tools like SQL Developer, and adding log statements.