PL/SQL Interview Questions and Answers

PL/SQL is Oracle's procedural extension to SQL that combines SQL with programming features such as variables, loops, conditions and exception handling. It is widely used to develop database applications, automate tasks and implement business logic directly within the Oracle database. PL/SQL enables users to:

• Create and manage stored procedures, functions, packages and triggers
• Use variables, loops, conditional statements and cursors for procedural programming
• Handle exceptions and perform secure transaction management
• Build efficient, reusable and high-performance database applications within Oracle Database

Beginner Interview Questions

1. What are the features of PL/SQL?

• PL/SQL is a procedural language that supports decision-making, loops and exception handling.
• It executes multiple SQL statements in a single block, improving performance.
• It allows creating reusable program units such as procedures, functions, packages, triggers and types.
• PL/SQL applications are portable across systems that support Oracle Database.

2. What do you understand by PL/SQL Collection?

A PL/SQL collection is an ordered collection of elements of the same type, where the position of each element is determined by its index. A user-defined type must be declared first before declaring the PL/SQL table as a variable.

Example:

DECLARE
    TYPE Vehicle_SSN_tabtype IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
    Vehicle_SSN_table Vehicle_SSN_tabtype;
BEGIN
    NULL;
END;

3. Explain the basic structure followed in PL/SQL.

PL/SQL is built around blocks, which are its basic building units. Each block has three sections:

• Declaration: Declares variables and constants.
• Execution: Contains executable SQL and PL/SQL statements.
• Exception Handling: Handles errors that occur during execution.

4. What is a PL/SQL cursor?

PL/SQL cursor controls the context area. A cursor holds one or more than one row returned by an SQL statement. There are set of rows which is held by the cursor is known as an active set.

Two types of cursors exist in PL/SQL.

• Implicit Cursor
• Explicit cursor

5. What is the use of WHERE CURRENT OF in cursors?

WHERE CURRENT OF is used to update or delete the row most recently fetched by a cursor. It identifies the current row being processed by the cursor.

Syntax:

UPDATE table_name
SET column = value
WHERE CURRENT OF cursor_name;

6. How can a name be assigned to an unnamed PL/SQL Exception Block?

An unnamed Oracle exception can be assigned a user-defined name using PRAGMA EXCEPTION_INIT, making specific error codes easier to handle.

Syntax:

DECLARE
    exception_name EXCEPTION;
    PRAGMA EXCEPTION_INIT(exception_name, error_code);
BEGIN
    -- Code
EXCEPTION
    WHEN exception_name THEN
        -- Exception handling steps
END;

7. What is a PL/SQL Trigger? Give some examples of when "Triggers" might be useful.

A PL/SQL trigger is a stored program that automatically executes when a specified event (such as INSERT, UPDATE, DELETE or certain DDL events) occurs on a table, view or database.

Syntax:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE | AFTER INSERT OR UPDATE OR DELETE
ON table_name
[FOR EACH ROW]
BEGIN
    trigger_action;
END;

• They are stored in the database and do not need to be called manually.
• They can be enabled or disabled but cannot be executed explicitly.

8. When does a DECLARE block have to be present?

A DECLARE block must be present only when you need to declare local variables, constants, cursors, records or other objects that will be used within the block. If no declarations are required, the DECLARE section can be omitted.

For example: PL/pgSQL DO block or function, the DECLARE section is placed before the BEGIN block and is optional unless declarations are needed.

9. How should comments be written in PL/SQL code?

DECLARE
    -- This is a single-line comment
BEGIN
    /* This is a
       multi-line comment */
END;

10. What is the purpose of the WHEN condition in the trigger?

WHEN condition is used in row-level triggers to specify the condition under which the trigger is fired. The trigger will only execute if the condition is met

11. What are the Differences between SQL and PL/SQL?

SQL and PL/SQL are both database languages, but they differ in their functionality.

12. Why are SYSDATE and USER keywords used?

SYSDATE:

The local database server's current time and date are returned by the SYSDATE keyword.

Example:

SELECT SYSDATE FROM dual;

USER:

The user id of the current session will be returned by using the USER keyword.

Example:

SELECT USER FROM dual;

13. What is the Difference between implicit cursor and explicit cursor?

Implicit and explicit cursors are both used to handle query results, but they differ in their control and usage.

14. Tell the importance of %TYPE and %ROWTYPE data types in PL/SQL.

%Type:

This datatype is used for specified tables to declares a variable with the same datatype as a table column.

Syntax:  

variable_name table_name.column_name%TYPE;

In the above syntax, the datatype of Attribute_Name is assigned to the variable named vAttributeName.

%ROWTYPE:

This datatype is used for specified tables to declares a record with the same structure as an entire table row.

Syntax:

record_name table_name%ROWTYPE;

%ROWTYPE assigns the data type of the Student table to the Rt_var_Student variable.

15. What are the differences between ROLLBACK and ROLLBACK TO statements in PL/SQL?

16. What are the uses of SYS.ALL_DEPENDENCIES?

The dependencies between all the procedures, packages, triggers and functions that the current user can access are described by SYS.ALL_DEPENDENCIES.

17. What the virtual tables exist during the execution of the database trigger?

During the execution of a database trigger, two virtual tables (or records) are available: OLD and NEW.

• OLD contains the values of the row before the triggering event.
• NEW contains the values of the row after the triggering event.

Their availability depends on the type of DML operation:

• INSERT: Only the NEW values are available because there is no existing row.
• UPDATE: Both OLD and NEW values are available.
• DELETE: Only the OLD values are available because the row is being removed.

18. What is the Difference between the cursors declared in procedures and in the package specifications?

The cursor declared in a package specification is global and can be accessed by other procedures or procedures in the package. A cursor declared in a procedure is local that can not be accessed by other procedures.

19. What is purposes of COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?

COMMIT:

• Saves all changes made during the transaction permanently.
• Once committed, changes cannot be undone.

Syntax:

BEGIN
    -- commands
    COMMIT;
END;

ROLLBACK:

• Undoes all changes made during the transaction.
• Restores the database to its previous state.

Syntax:

BEGIN
    -- commands
    ROLLBACK;
END;

SAVEPOINT:

• Creates a point within a transaction.
• Allows partial rollback to a specific point.

BEGIN
    SAVEPOINT sp;
    -- commands
    ROLLBACK TO sp;
END;

20. What is the difference between a Function and a Procedure in PL/SQL?

Intermediate Interview Questions

21. What is the main difference between a mutating table and a constraining table?

22. Describe the data types present in PL/SQL.

PL/SQL data types are classified into the following categories:

• Scalar data types: Store a single value. Examples include NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN, LONG, TIMESTAMP and RAW.
• Composite data types: Store multiple values in a single variable. Examples include RECORD, associative arrays (index-by tables), nested tables and varrays.
• Reference data types: Store references to other program items or database objects. Examples include %TYPE, %ROWTYPE and REF CURSOR.
• Large Object (LOB) data types: Store large amounts of character or binary data. Examples include BLOB, CLOB, NCLOB and BFILE.

23. List the types of exceptions in PL/SQL.

Two types of exceptions are present in PL/SQL.

• Pre-defined exceptions: These are exceptions that are automatically handled by Oracle (e.g., NO_DATA_FOUND, TOO_MANY_ROWS).
• User-defined exceptions: These are exceptions defined by the user to handle specific errors in the program.

24. What types of commands PL/SQL does not support?

DDL statements cannot be executed directly in a PL/SQL block. They can be executed using EXECUTE IMMEDIATE.

25. Name some PL/SQL exceptions.

Below are some PL/SQL exceptions.

• INVALID_NUMBER
• TOO_MANY_ROWS
• ACCESS_INTO_NULL
• CASE_NOT_FOUND
• ZERO_DIVIDE
• NO_DATA_FOUND

26. What is a PL/SQL package?

Packages are schema objects that group PL/SQL types, variables and subprograms that are logically related.

A package consists of two parts.

• Package specification
• Package body or definition

Syntax:

package_name.attribute_name;

27. Write a PL/SQL program to find a given string is palindrome.

DECLARE
    str VARCHAR2(50) := 'abababa';
    rev_str VARCHAR2(50) := '';
BEGIN
    FOR i IN REVERSE 1..LENGTH(str) LOOP
        rev_str := rev_str || SUBSTR(str, i, 1);
    END LOOP;

    IF rev_str = str THEN
        DBMS_OUTPUT.PUT_LINE(str || ' is a palindrome');
    ELSE
        DBMS_OUTPUT.PUT_LINE(str || ' is not a palindrome');
    END IF;
END;
/

28. What command will you use to delete a package?

Use the DROP PACKAGE statement to delete a package.

Syntax:

DROP PACKAGE [BODY] Attribute_Name.Package_Name;

29. How will you execute a stored procedure?

EXECUTE or EXEC keyword can be used to execute stored procedures.

Syntax:

EXECUTE procedure_name;
or
EXEC procedure_name;

30. Explain the IN, OUT and IN OUT parameters.

• IN: We can transmit values to the procedure that is being called using the IN parameter. You can use the default settings for the IN parameter. IN parameter behaves as a constant.
• OUT: The caller receives a value from the OUT parameter. It is an uninitialized variable.
• IN OUT: The IN OUT parameter gives starting values to a procedure and sends the updated values to the caller. IN OUT parameter should be like an initialized variable.

31. Differentiate between %ROWTYPE and %TYPE.

• %ROWTYPE: It is used to declare a variable that has the structure of the records in a table.
• %TYPE: To declare a column in a table that contains the value of that column, use the %TYPE property. The variable's data type and the table's column are the same.

32. Discuss SQLERRM and SQLCODE. What is the importance of PL/SQL?

• SQLCODE returns the error number for the most recent error found.
• SQLERRM returns the error message for the most recent error.

SQLCODE and SQLERRM can be used in exception handling in PL/SQL to report the errors during exception handling.

33. What are PL/SQL records? List their types.

A record is a type of data structure that may store several types of data elements. Like a row in a database table, a record is made up of various fields.

There are three types of records in PL/SQL.

• Table-based records
• Cursor-based records
• User-defined records are created by programmers.

34. Which collection methods are commonly used with nested tables?

Common methods include:

• COUNT
• EXTEND
• DELETE
• FIRST
• LAST
• NEXT
• PRIOR
• EXISTS
• TRIM

35. What are the valid DateTime values for seconds in PL/SQL?

The following are valid second values:

• 00 to 59.9(n), where 9(n) is the accuracy in fractional seconds of time.
• For DATE, the 9(n) section does not apply.

36. Explain PL/SQL Delimiters.

In PL/SQL, a delimiter is a compound symbol having a unique meaning. Delimiters are special symbols with predefined meanings in PL/SQL.

37. What is the use of a UTL_FILE package in PL/SQL?

The UTL_FILE package is used to read from and write to text files on the server’s file system using PL/SQL.

DECLARE
    file_handle UTL_FILE.FILE_TYPE;
BEGIN
    file_handle := UTL_FILE.FOPEN('MY_DIR', 'test.txt', 'W');
    UTL_FILE.PUT_LINE(file_handle, 'Hello World');
    UTL_FILE.FCLOSE(file_handle);
END;

38. What is the use of index in PL/SQL?

A table's data blocks can be accessed more quickly and effectively with the help of an index.

39. What does the error ORA-03113 mean?

An ORA-3113 means "end of file on communication channel". A client process connected to an Oracle database will typically report an ORA-03113. these are some following scenarios when ORA-03113 can occur:

• When a server machine crashed
• At the operating system level, our server process was killed.
• Network communication problems.
• The client is not handling multiple connections

40. What is BULK COLLECT in PL/SQL?

BULK COLLECT is used to fetch multiple rows from a query into a collection in a single operation. It improves performance by reducing the number of context switches between the SQL and PL/SQL engines.

41. What are Nested Tables in PL/SQL? How do you create them?

Nested tables are collection types in PL/SQL. Nested tables are created either in the PL/SQL block or at the schema level. These are like a 1D array, but their size can be increased or decreased dynamically.

Syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];
collection_name type_name;

Advanced Interview Questions

42. Discuss the concept of RAISE_APPLICATION_ERROR .

RAISE_APPLICATION_ERROR is a built-in procedure in PL/SQL used to raise user-defined error messages from stored procedures, functions or triggers.

Syntax:

raise_application_error(error_number, message[, {TRUE | FALSE}]);

43. What do you know about pragma_exception_init in PL/SQL?

PRAGMA EXCEPTION_INIT is used in PL/SQL to associate a user-defined exception with a specific Oracle error number.

44. How can you verify whether an Update Statement is Executed or not, In PL/SQL?

In PL/SQL, you can verify whether an UPDATE statement affected any rows using implicit cursor attributes like SQL%FOUND and SQL%NOTFOUND.

• SQL%FOUND: Returns TRUE if the UPDATE affected at least one row
• SQL%NOTFOUND: Returns TRUE if no rows were updated

45. What is the use of the || Operator?

The || operator is used for string concatenation in SQL and PL/SQL.

46. Is a definition command like the CREATE command supported in PL/SQL?

DDL commands like CREATE are not directly allowed in PL/SQL blocks, but they can be executed using dynamic SQL (EXECUTE IMMEDIATE).

47. Write the syntax to create a view.

CREATE VIEW view_name AS SELECT columns FROM tables;

48. What are the basic parts of triggers?

The following three are basic parts of a trigger.

• Trigger statement
• Trigger restriction
• Trigger action

49. What are the Methods to Trace the PL/SQL Code?

PL/SQL code can be traced using the following tools:

• DBMS_TRACE
• DBMS_APPLICATION_INFO
• DBMS_SESSION
• DBMS_MONITOR

50. How do you insert records into a nested table in PL/SQL?

We can insert records into a nested table by first extending the collection using the EXTEND method and then assigning values to the new element.

Example:

DECLARE
    TYPE student_list IS TABLE OF VARCHAR2(50);
    students student_list := student_list();
BEGIN
    students.EXTEND;
    students(1) := 'Alice';

    students.EXTEND;
    students(2) := 'Bob';

    students.EXTEND;
    students(3) := 'Charlie';

    DBMS_OUTPUT.PUT_LINE('Total Students: ' || students.COUNT);
END;
/