Common PL/SQL Interview Questions and Answers

Basic PL/SQL Interview Questions

1. What is PL/SQL?

PL/SQL refers to Procedural Language/Structured Query Language. It is a programming language extension of SQL that allows you to write procedural code, such as loops, conditional statements, exception handling, and SQL statements. PL/SQL is mainly used for developing database applications and stored procedures within Oracle Database.

2. Compare SQL and PL/SQL.

SQL is a domain-specific language to manage and manipulate relational databases. It primarily deals with querying, inserting, updating, and deleting data in a database. On the other hand, PL/SQL is a procedural language that extends SQL by adding programming constructs like variables, loops, and exception handling. PL/SQL is used for writing stored procedures, functions, and triggers, allowing for more complex and reusable database logic.

3. Do you know the basic structure of PL/SQL?

Yes, the basic structure of a PL/SQL block includes:

• Declaration section: Where you define variables, constants, and cursors.
• Execution section: Where you write the actual PL/SQL code, including SQL statements and procedural logic.
• Exception handling section: Where you handle errors and exceptions that may occur during execution.

4. What Is a Trigger? How Do You Use It?

A trigger is a database object in PL/SQL that automatically executes actions in response to specific events, such as insertions, updates, or data deletions in a table. Triggers are typically used to enforce business rules, maintain data integrity, and automate tasks. You create and define triggers using PL/SQL code and attach them to database tables.

5. What data types does pl/SQL have?

PL/SQL supports various data types, including:

• Scalar data types like VARCHAR2, NUMBER, DATE, and BOOLEAN.
• Composite data types like RECORD and TABLE.
• Reference data types like REF CURSOR and PL/SQL TABLE.

6. Explain the PL/SQL compilation process.

The PL/SQL compilation process involves:

• Parsing: The PL/SQL code is checked for syntax errors.
• Compilation: The code is converted into a format the Oracle Database can execute.
• Storage: The compiled code is stored in the database.
• Execution: When the PL/SQL code is called, it is executed by the database engine.

7. Tell me what a package consists of.

A PL/SQL package contains related procedures, functions, variables, and other PL/SQL constructs. It consists of two main parts:

• Package Specification: This defines the public interface of the package, including procedures, functions, and global variables that other programs can access.
• Package Body: This contains the actual implementation of the functions and procedures defined in the specification.

8. What are the benefits of using PL/SQL packages?

Using PL/SQL packages offers several benefits, including:

• Encapsulation: You can encapsulate related code and data within a package, promoting modular and organized code.
• Reusability: Packages allow you to reuse code across multiple programs and reduce redundancy.
• Information Hiding: You can hide implementation details by exposing only the necessary interfaces in the package specification.
• Improved Performance: Packages are precompiled and stored in the database, which can enhance performance.

9. Do you understand the meaning of exception handling?

Yes, exception handling in PL/SQL is the process of handling errors or exceptional conditions that may occur during program execution. It allows you to gracefully handle errors by specifying actions to take when a particular exception occurs, such as logging the error, rolling back transactions, or raising custom exceptions.

10. Give me some examples of predefined exceptions.

Predefined exceptions in PL/SQL include:

• NO_DATA_FOUND: Raised when a SELECT statement returns no rows.
• TOO_MANY_ROWS: Raised when a SELECT INTO statement retrieves multiple rows.
• DUP_VAL_ON_INDEX: Raised when attempting to insert a duplicate value into a specific index.
• ZERO_DIVIDE: Raised when dividing by zero.

11. What do you understand by PL/SQL cursors?

PL/SQL cursors retrieve and manipulate data from a result set. They can be either explicit or implicit. The database automatically creates implicit cursors for DML statements (e.g., SELECT INTO), while the programmer defines and uses explicit cursors. Cursors help iterate through query results and process data row by row.

12. When do we use triggers?

Triggers are used when you want to automate actions or enforce rules based on changes in the database. Common use cases for triggers include auditing changes, maintaining data integrity, and implementing business rules.

13. What is a PL/SQL block?

A PL/SQL block is a self-contained unit of code that can include declarations, executable statements, and exception handlers. It is the fundamental structure for writing PL/SQL programs, procedures, functions, and anonymous blocks.

14. Name the differences between syntax and runtime errors.

• Syntax Error: These errors occur during compilation and are related to incorrect PL/SQL language syntax. They prevent the code from compiling successfully.
• Runtime Error: These errors occur during program execution and are caused by issues such as division by zero, data type mismatches, or other exceptional conditions. They can be handled with exception handling.

15. What are COMMIT, ROLLBACK, and SAVEPOINT?

• COMMIT: The SQL statement known as COMMIT is utilized to persistently store all modifications carried out within the ongoing transaction in the database, signifying the successful conclusion of said transaction.
• ROLLBACK: ROLLBACK in SQL is a command employed to reverse all modifications executed during the ongoing transaction and revert the database to its prior state.
• SAVEPOINT: SAVEPOINT sets a point within a transaction to which you can later roll back if needed. It allows you to undo parts of a transaction selectively.

16. What are the two types of exceptions?

In PL/SQL, exceptions are categorized into two types:

• Predefined Exceptions: These exceptions are predefined by Oracle and include errors like NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX.
• User-Defined Exceptions: These are exceptions defined by the PL/SQL programmer to handle specific application-related errors.

17. Do you know what a mutating table error is?

A mutating table error arises when a trigger attempts to update or reference a table that is currently undergoing modification by the same trigger. This situation can result in unpredictable outcomes and is commonly resolved by either reengineering the trigger logic or adopting alternative methods.

18. When is a declaration statement required?

Declaration statements are required when defining variables, constants, cursors, or other program constructs in PL/SQL. They are placed in the declaration section of a PL/SQL block before the executable code.

19. Explain three basic parts of a trigger in PL/SQL.

• Trigger Event: Specifies the event (e.g., INSERT, UPDATE, DELETE) that activates the trigger.
• Trigger Timing: Determines whether the trigger fires before or after the event.
• Trigger Body: Contains the PL/SQL code to be executed when the trigger is activated.

20. What are character functions?

Character functions in PL/SQL are functions that operate on character data. They include functions for string manipulation, conversion, formatting, and searching within strings. Examples include CONCAT, SUBSTR, UPPER, LOWER, and INSTR.

21. What is the difference between type and rowtype?

• Type: A "type" in PL/SQL refers to a user-defined data type, such as a record type or a table type, which can be used to define the structure of variables and parameters.
• Rowtype: A "rowtype" in PL/SQL is a distinct data type designed to represent an individual data row sourced from a database table. This data type is generated automatically for every table and serves as a means to declare variables capable of containing complete data rows extracted from the respective table.

22. Name some schema objects that are created in PL/SQL.

Schema objects created in PL/SQL include procedures, functions, triggers, packages, types (e.g., record types), and tables (if you create a new table using PL/SQL).

23. What is the purpose of SYSDATE and user keywords?

• SYSDATE: SYSDATE is a PL/SQL function that returns the current date and time from the system clock. It is commonly used to record timestamps in database records.
• USER: USER is a PL/SQL function that returns the username of the currently logged-in database user. It can be used for auditing and access control.

24. Are there any downsides to using PL/SQL?

While PL/SQL is a powerful language for database programming, it may have some downsides, including:

• Vendor Lock-In: PL/SQL is specific to Oracle Database, which can create vendor lock-in.
• Learning Curve: It may take time to learn PL/SQL, especially for developers without prior database programming experience.
• Performance: Poorly optimized PL/SQL code can impact database performance.

25. How can we use comments in PL/SQL code?

PL/SQL supports two types of comments:

• Single-Line Comments: These are indicated by two hyphens (--). Anything following the hyphens on the same line is treated as a comment.
• Multi-Line Comments: These are enclosed within /* and */ and can span multiple lines. Everything between the comment markers is considered a comment and is ignored by the compiler.

Intermediate PL/SQL Interview Questions

1. How do you access the fields of a record in PL/SQL?

In PL/SQL, you can access the fields of a record using dot notation. For example, if you have a record named `my_record` and a field named `field_name`, you can access it as `my_record.field_name`.

2. Can you label a PL/SQL loop?

Yes, you can label a PL/SQL loop using a label name followed by a colon. Labels are typically used with nested loops to specify which loop to exit or continue.

3. What are the different methods for tracing PL/SQL code?

There are several methods for tracing PL/SQL code, including using `DBMS_OUTPUT.PUT_LINE`, PL/SQL Profiler, and the built-in `DBMS_TRACE` package for fine-grained tracing and debugging.

4. Why should you use an index in a table?

Tables with indexes enhance query performance by enabling the database to swiftly pinpoint particular rows according to the indexed column(s). Indexes expedite data retrieval and prove particularly valuable when dealing with extensive tables.

5. How do developers use database links?

Developers use database links to establish connections between different databases, allowing them to access and manipulate data across different instances. This is useful for distributed data processing and cross-database queries.

6. Tell me what expressions mean in PL/SQL.

In PL/SQL, expressions are combinations of variables, constants, operators, and function calls that evaluate a single value. Expressions can be used for calculations, comparisons, and generating results.

7. What is the overloading of a procedure?

Procedure overloading in PL/SQL allows you to create multiple procedures with the same name but different parameter lists. The appropriate execution method is determined at compile or runtime based on the number or data types of the arguments passed.

8. Define a subprogram in PL/SQL.

A subprogram in PL/SQL is a named block of code that can be called from other parts of a PL/SQL program. Subprograms include procedures and functions that encapsulate specific functionality and promote code reusability.

9. How would you implement a standalone procedure?

To implement a standalone procedure in PL/SQL, you declare it using the `PROCEDURE` keyword, define its parameters, and provide the necessary logic within the procedure's block. Standalone procedures are not associated with any specific database object like a package.

10. Tell me how to declare a constant in PL/SQL.

You can declare a constant in PL/SQL using the `CONSTANT` keyword within a PL/SQL block or package. Constants have fixed values that cannot be changed during program execution.

11. Explain what the OPEN cursor command function does.

The `OPEN` cursor command is used in PL/SQL to execute a SQL query and open a cursor for processing the query result set. After opening a cursor, you can fetch rows using `FETCH` statements.

12. How do you delete triggers?

To delete a trigger in PL/SQL, you can use the `DROP TRIGGER` statement followed by the trigger name. This removes the trigger from the associated table.

13. What is the difference between temporary and permanent tablespaces?

Permanent tablespaces in a database store persistent data, such as tables and indexes. Temporary tablespaces are stored during query processing, sorting, and intermediate result storage.

14. Which command do developers use to delete a procedure?

Developers can use the `DROP PROCEDURE` statement to delete a stored procedure in PL/SQL.

15. What are the cursor attributes used in PL/SQL?

Cursor attributes in PL/SQL include `%FOUND`, `%NOTFOUND`, `%ROWCOUNT`, and `%ISOPEN`. These attributes provide information about the status of a cursor and the result set it processes.

16. How do you declare a user-defined exception?

To declare a user-defined exception in PL/SQL, you use the `DECLARE` section and the `EXCEPTION` keyword. For example:

sql

    DECLARE

       custom_exception EXCEPTION;

    BEGIN

       -- Code that might raise the exception

       IF some_condition THEN

          RAISE custom_exception;

       END IF;

    END;

17. The case statement has no selector in PL/SQL. Is this true?

No, this statement is not true. In PL/SQL, the `CASE` statement does have a selector, which is used to specify the expression to evaluate and compare with various conditions to determine the branch of code to execute.

18. What is the difference between implicit and explicit cursors?

Implicit cursors are automatically created by PL/SQL for DML statements (e.g., `INSERT`, `UPDATE`, `DELETE`) and are used to process the result set of these statements. Developers explicitly declare and use explicit cursors to process query result sets using `OPEN`, `FETCH`, and `CLOSE` statements.

19. What are the benefits of triggers?

Triggers in PL/SQL can automatically execute code in response to specific database events (e.g., `INSERT`, `UPDATE`, `DELETE`). They are used for enforcing data integrity, auditing changes, and automating tasks.

20. What are the found and not found cursor attributes in PL/SQL?

• `%FOUND`: This cursor attribute returns `TRUE` if a `FETCH` statement successfully retrieves a row.
• `%NOTFOUND`: This cursor attribute returns `TRUE` if a `FETCH` statement does not retrieve any more rows (end of a result set).

21. Can you explain the PL/SQL execution architecture?

PL/SQL code is executed using the PL/SQL engine within the Oracle Database. When a PL/SQL block is called, the engine compiles it into bytecode, which is stored in memory. The bytecode is executed, and data is retrieved or modified using SQL statements.

22. What are the functions available for manipulating character data?

PL/SQL provides various functions for manipulating character data, including `CONCAT`, `SUBSTR`, `INSTR`, `UPPER`, `LOWER`, `TRIM`, `LENGTH`, and many others.

23. What virtual tables are available in PL/SQL?

PL/SQL provides virtual tables such as `DUAL` and `USER` that can be queried to retrieve information about the current user, session, and system settings.

24. What are PL/SQL records?

PL/SQL records are composite data types that allow you to group related variables of different data types into a single unit. They are similar to structures in other programming languages.

25. What are the three parameter modes in PL/SQL?

In PL/SQL, parameters can have three modes:

• `IN`: Parameters passed as input to the subprogram.
• `OUT`: Parameters used to return values from the subprogram.
• `IN OUT`: Parameters that act as both input and output, allowing data to be passed in and out of the subprogram.

Advanced PL/SQL Interview Questions

1. What are pseudocolumns, and how do they work?

Pseudocolumns are special columns in Oracle databases that provide metadata or row-specific information in SQL queries and PL/SQL code. They cannot be modified directly and are used for read-only purposes. Examples of pseudocolumns include `ROWID`, `ROWNUM`, `LEVEL` (used in hierarchical queries), and `SYSDATE` (provides the current date and time).

2. What is the difference between SQLCODE and SQLERRM?

• `SQLCODE` is a PL/SQL variable storing the numeric error code returned by the last SQL statement. A value of 0 indicates success, while non-zero values indicate various error conditions.
• `SQLERRM` is a PL/SQL function designed to retrieve the error message corresponding to the most recent SQL error code stored in the `SQLCODE` variable.

3. Explain the daily activities of a PL/SQL developer.

Daily activities of a PL/SQL developer typically include writing, debugging, and maintaining PL/SQL code, creating and optimizing database stored procedures and triggers, collaborating with database administrators and application developers, and ensuring the efficient and reliable performance of database operations.

4. How can you locate a PL/SQL block?

PL/SQL blocks can be located in database packages, procedures, functions, triggers, or anonymous blocks within SQL scripts. You can use database metadata views like `ALL_PROCEDURES` or IDE tools to search for specific PL/SQL blocks.

5. What is a Join?

In SQL, a join operation merges rows from two or more tables using a shared column as a reference.

6. How do you display the highest salary from an employee table?

You can use the `MAX` function to display the highest salary from an employee table like this:

sql

   SELECT MAX(salary) FROM employee;

7. Tell me how you would create a nested table.

To create a nested table in PL/SQL, you would first define a nested table type, and then you can use that type to declare a nested table variable. Here's an example:

sql

   CREATE OR REPLACE TYPE NestedTableType AS TABLE OF NUMBER;

   DECLARE

     myNestedTable NestedTableType;

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

• A mutating table is a table that is currently being modified (inserted, updated, or deleted) within a trigger's body. You cannot query a mutating table directly within the same trigger.
• A constraining table is utilized in a database's referential integrity constraint, specifically in the context of a foreign key constraint. This constraint serves to uphold data integrity by verifying that the values within a foreign key column align with those found in the primary key column of the referenced table.

9. How do you create a function in PL/SQL?

You can create a function in PL/SQL using the `CREATE OR REPLACE FUNCTION` statement. Here's a basic example:

sql

   CREATE OR REPLACE FUNCTION calculate_salary_bonus(emp_id NUMBER) RETURN NUMBER IS

     bonus NUMBER;

   BEGIN

     -- Calculate bonus based on emp_id and other criteria

     RETURN bonus;

   END calculate_salary_bonus;

10. What programming constructs does PL/SQL support?

PL/SQL supports various programming constructs, including variables, control structures (IF, CASE, LOOP, etc.), procedures, functions, triggers, packages, exception handling, cursors, etc.

11. Explain what a timestamp data type is in PL/SQL.

In PL/SQL, the `TIMESTAMP` data type represents a point in time with fractional seconds precision. It includes both date and time information. Timestamps are often used to timestamp events or data in the database precisely.

12. How do you include single quotes in a string literal in PL/SQL?

To include a single quote within a string literal in PL/SQL, you can escape it by doubling it up. For example:

sql

    my_string := 'This is an example of a single ''quote'' in PL/SQL';

13. What does the error ORA-03113 mean?

ORA-03113 is a common Oracle error code that indicates a connection has been lost to the database. This can happen due to network issues, the database instance being shut down, or problems on the database server.

14. What is an autonomous transaction?

An autonomous transaction in PL/SQL is a separate, independent transaction that is started within the context of another transaction. It allows you to commit or rollback changes independently of the calling transaction, ensuring that changes made in the autonomous transaction are not affected by the outcome of the calling transaction.

15. How can you debug your PL/SQL code?

PL/SQL code can be debugged using various tools and techniques, such as DBMS_OUTPUT for printing messages, using logging tables, using a debugging IDE or debugger, and using exception handling to capture and log errors.

16. How will you restrict the string length in PL/SQL?

You can restrict the string length in PL/SQL using the `VARCHAR2` or `CHAR` data types with a specified length constraint. For example:

sql

    my_varchar2 VARCHAR2(50);

17. Tell me about the role of a hierarchical profiler.

A hierarchical profiler in PL/SQL is a tool that helps developers analyze and optimize the performance of hierarchical queries, such as those involving recursive SQL statements. It provides insights into the execution plan and performance bottlenecks of hierarchical queries.

18. What are the roles of PLVrb and PLVcmt in PL/SQL?

• PLVrb (PL/Vision Row Builder) is a PL/SQL utility package that helps build complex rows for use in dynamic SQL or as return values from functions.
• PLVcmt (PL/Vision Commenter) is a PL/SQL utility package that helps developers document their code by generating comments for procedures, functions, and triggers.

19. How do you get returns on more than one row?

To retrieve returns on more than one row in PL/SQL, you can use a cursor to fetch multiple rows of data from a table or use a collection data type like a nested table or associative array to store and manipulate multiple rows of data.

20. Describe a problem developers might face while writing log information in a database table in PL/SQL.

One common problem is performance degradation due to excessive log writes. Writing log information to a database table can cause contention for resources and slow down the main application. Developers must carefully design and implement efficient logging mechanisms, consider asynchronous logging, and use appropriate indexes to mitigate these issues.

21. How can you verify whether a CPU has executed an update statement?

You can't directly verify whether a specific CPU has executed an update statement in PL/SQL. PL/SQL code runs within the database server, and Oracle manages the distribution of CPU resources. However, you can monitor overall database performance and query execution using tools like Oracle Enterprise Manager or database performance monitoring tools.

22. What do you know about PRAGMA EXCEPTION_INIT in PL/SQL?

PRAGMA EXCEPTION_INIT is a PL/SQL directive associating a user-defined exception with an Oracle error code. It allows you to handle specific database errors in a more controlled manner by associating them with a custom exception, making error handling more precise and informative.

23. Which command deletes a package in PL/SQL?

To delete a package in PL/SQL, you can use the `DROP PACKAGE` command followed by the package name. For example:

sql

    DROP PACKAGE my_package;

24. What is the purpose of the DBMS_OUTPUT package?

The DBMS_OUTPUT package in PL/SQL displays debugging information and messages from PL/SQL code. It provides procedures like `PUT_LINE` to print messages to the console. It is commonly used for debugging and monitoring code during development.

25. What is a subquery? name some types of subqueries in PL/SQL.

In PL/SQL, a subquery refers to a query enclosed within another query. Its purpose is to fetch data that is intended to be utilized either within the primary query's conditions or as part of its resulting dataset. Common types of subqueries include:

• Scalar Subquery: Returns a single value.
• Correlated Subquery: References columns from the outer query.
• Nested Subquery: A subquery within another subquery.
• Multi-Row Subquery: Returns multiple rows as a result.
• EXISTS Subquery: Checks for the existence of rows.
• IN Subquery: Tests if a value matches any value returned by a subquery.

Master front-end and back-end technologies and advanced aspects in our Full Stack Developer - MERN Stack. Unleash your career as an expert full stack developer. Get in touch with us NOW!

Conclusion

The PL/SQL interview questions discussed in this response cover many topics that a PL/SQL developer may encounter in their role. These questions touch upon the fundamental concepts of PL/SQL, error handling, database interactions, and best practices for developing efficient and maintainable code. If you would like to strengthen your software development skills, check out the Full Stack Developer - MERN Stack, which will help you establish yourself as an expert in software development.