PL/SQL Interview Questions and Answers

PL/SQL is a widely used database programming language known for its performance, flexibility and integration with Oracle databases. It is commonly used in enterprise applications for managing database operations.

• It is widely used in companies like Oracle, IBM, Accenture, TCS and Cognizant.
• It helps in managing database logic using procedures, functions and triggers.
• It covers core concepts like cursors, exception handling and packages.
• It supports performance tuning and efficient data processing.

PL/SQL Basic Interview Questions

1. What are the features of PL/SQL?

• PL/SQL is a procedural language, which provides the functionality of decision making, iteration and numerous further features of procedural programming languages.
• Using a single command, PL/SQL executes several queries in one block.
• PL/SQL can handle the exception generated in the PL/SQL block. That block is called an exception handling block.
• One can create a PL/SQL unit such as procedures, packages, triggers, functions and types, which are stored in the database for reuse by applications.
• Applications that are written in PL/SQL are portable to computer hardware or operating systems where Oracle is functional.

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

A PL/SQL table 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.

By including elements from procedural languages, PL/SQL expands SQL and creates a structural language that is more potent than SQL. A block is PL/SQL's fundamental building block. Every PL/SQL program is built around these blocks and a block consists of three parts:

• Declaration: Used to declare variables and constants.
• Execution: Contains executable commands (SQL statements).
• Exception handling: Deals with errors during the execution phase

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?

LAST FETCHED ROW identify by using WHERE CURRENT OF in cursor. We can use the WHERE CURRENT OF statement for updating or deleting records without using the SELECT FOR UPDATE statement. The record that was last retrieved by the cursor can be updated or deleted using the WHERE CURRENT OF statement.

Syntax:

UPDATE table_name
SET column = value
WHERE CURRENT OF cursor_name;

OR

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?

In PL/SQL, an unnamed (predefined) exception can be given a user-defined name using the PRAGMA EXCEPTION_INIT directive. This allows you to handle specific Oracle error codes with meaningful names.

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.

When a specific event takes place, the Oracle engine immediately starts. The trigger is already stored in the database If a certain condition is met, the trigger is frequently called from a database. Trigger mode can be activated or deactivated, but running explicitly is not possible.

Syntax:

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

• Triggers are automatically executed when events like INSERT, UPDATE or DELETE occur.
• 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?

In PL/SQL anonymous blocks, such as stand-alone and non-stored procedures, a DECLARE statement is used. The statement in the stand-alone file should come first when they are used.

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?

The 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?

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?

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

%Type:

This datatype is used for specified tables to define the variable as its column name datatype.

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:

Use %ROWTYPE if the programmer doesn't know the datatype of the specified column but still needs to assign it to the variable. It is nothing more than an assignment in an array in which we can specify a variable and define the entire row datatype.

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?

• OLD and NEW two are virtual tables that exist during the execution of the database trigger.
• OLD and NEW both are accessible by UPDATE statement.
• INSERT statement can access only NEW value.

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. How can we debug our PL/SQL code?

PL/SQL code can be debugged using built-in tools and packages that help track execution and identify errors.

Intermediate PL/SQL Interview Questions

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

A table that can be changed using a DML statement or one with triggers defined is said to be a mutating table. The table that is read for a referential integrity constraint is referred to as a constraining table.

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

There are two types of data types present in PL/SQL.

• Scalar data types: NUMBER, DATE, CHAR, VARCHAR2, BOOLEAN and LONG are scalar data types.
• Composite data type: TABLE and RECORD are composite data types.

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?

PL/SQL does not support data definition commands like CREATE, ALTER etc.

25. Name some PL/SQL exceptions.

Below are some PL/SQL exceptions.

• INVALID_NUMBER
• TOO_MANY_ROWS
• ACCESS_INTO_NULL
• CASE_NOT_FOUND
• ZERO_ERROR
• 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 will have 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?

we 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 error that happened in the code in the error log database.

33. What are PL/SQL records? tell types of records.

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 Explain the BTITLE and TTITLE statements.

TTITLE statement is used to define the top title similarly for defining the bottom title we will use BTITLE statement.

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 used to indicate arithmetic operations like division, addition etc.

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-3113. these are some following scenarios when ORA-3113 can occur:

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

40. What is a Join?

The join keyword is used to query data from multiple tables. Join is based on the relationship between the fields of tables. Table keys play an important role in Joins.

41. How can we write or create multiple tables in PL/SQL?

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 PL/SQL 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?

We will trace the code to measure its performance during run time. below are some methods to trace the PL/SQL code.

• DBMS_TRACE
• DBMS_APPLICATION_INFO
• DBMS_SESSION
• DBMS_MONITOR

50. How do you Create Nested Tables in PL/SQL?

A nested table is a dynamic collection in PL/SQL that stores multiple values of the same data type and can be extended or reduced in size.

DECLARE
    TYPE deptname IS TABLE OF VARCHAR2(10);
    TYPE budget IS TABLE OF INTEGER;

    names deptname := deptname('Marketing', 'Development', 'Sales');
    deptbudget budget := budget(12567, 4567, 1234);

BEGIN
    FOR i IN 1 .. names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Department = ' || names(i) || 
            ', Budget = ' || deptbudget(i)
        );
    END LOOP;
END;