PL-SQL Interview Questions

Looking for PL SQL Interview Questions with Answers? Here we have compiled set of questions and answers for professionals who are looking to crack the interview.

Q.1 What do you understand by PL/SQL ?

PL/SQL is defined as a procedural language that has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.

Report This Question

Q.2 Differentiate between % ROWTYPE and TYPE RECORD.

Lets discuss some of the points of difference -

% ROWTYPE is used when a query returns an entire row of a table or view, whereas TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE

Report This Question

Q.3 Explain the uses of cursor.

Cursor is primarily a named private area in SQL from which information can be accessed. Cursor are required to process each row individually for queries which then return multiple rows.

Report This Question

Q.4 What the uses of database trigger?

Database Trigger is a PL/SQL program unit associated with a particular database table. It is primarily used for -

1. Audit data modifications.
2. Log events transparently.
3. Enforce complex business rules.
4. Maintain replica tables
5. Derive column values
6. Implement Complex security authorizations

Report This Question

Q.5 What are the types of exceptions?

Exception refers to the Error handling part of PL/SQL block. They are of two types namely -
user_defined
predefined.

Report This Question

Q.6 What do you understand by Raise_application_error?

It refers to the procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.

Report This Question

Q.7 List the rules to be applied to NULLs while doing comparisons.

The rules to be applied to NULLs while doing comparisons are -
NULL is never TRUE or FALSE
NULL cannot be equal or unequal to other values
If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)

Report This Question

Q.8 How can you process of PL-SQL compiled?

The process of compiling includes syntax check, bind and p-code generation processes. The steps followed are -

1. Syntax checking checks the PL SQL codes for compilation errors.
2. When all errors are corrected, a storage address is assigned to the variables that hold data referred as Binding.
3. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.

Report This Question

Q.9 What is the difference between Syntax and runtime errors?

The difference between Syntax and runtime errors are -
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block.
For eg, SELECT INTO statement, which does not return any rows.

Report This Question

Q.10 What is PL/SQL?

PL/SQL stands for "Procedural Language/Structured Query Language" and is Oracle's extension of SQL.

Report This Question

Q.11 What do you understand by Implicit and Explicit Cursors?

Cursor is implicit by default such that the user cannot control or process the information in this cursor.

In case a query returns multiple rows of data, the program defines an explicit cursor. This permits the application to process each row sequentially as the cursor returns it.

Report This Question

Q.12 Explain the key components of PL/SQL.

PL/SQL consists of blocks, variables, control structures, and exception handlers.

Report This Question

Q.13 What do you understand by mutating table error?

Mutating Table Error occurs when a trigger tries to update a row that it is currently using. This error is fixed by using views or temporary tables, so database selects one and updates the other.

Report This Question

Q.14 What are the advantages of using PL/SQL?

PL/SQL provides better performance, modularity, and error handling compared to standard SQL.

Report This Question

Q.15 When do you require a declare statement?

Declare statement is used by PL SQL anonymous blocks like with stand alone, non-stored procedures. Such that if it is used, it must come first in a stand alone file.

Report This Question

Q.16 How do you declare variables in PL/SQL?

You can declare variables using the DECLARE section.

Report This Question

Q.17 List the importance of SQLCODE and SQLERRM.

SQLCODE gives the value of the number of error for the last encountered error where on the other hand SQLERRM returns the message for the last error.

Report This Question

Q.18 What is a PL/SQL block?

A PL/SQL block is a unit of code containing declarations, SQL statements, and procedural logic.

Report This Question

Q.19 Does SQL*Plus also have a PL/SQL Engine?

SQL*Plus does not have a PL/SQL Engine embedded in it. Therefore all PL/SQL code is sent directly to database engine. Such that it is way more efficient as each statement is not individually stripped off.

Report This Question

Q.20 Explain the difference between a scalar and composite data type in PL/SQL.

Scalar data types hold single values (e.g., number, varchar2), while composite data types hold multiple values (e.g., records, tables).

Report This Question

Q.21 What are the packages available to PL SQL developers?

Some of the packages available to the developers are -
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

Report This Question

Q.22 What is a cursor in PL/SQL, and how is it used?

A cursor is a database object used to fetch and manipulate data row by row. Cursors are used in PL/SQL to process query results.

Report This Question

Q.23 What are the basic parts of a trigger?

Three basic parts of a trigger are -
1. A triggering statement or event.
2. A restriction
3. An action

Report This Question

Q.24 What is an implicit cursor in PL/SQL?

An implicit cursor is automatically created by Oracle to process SQL statements. It does not need to be explicitly declared.

Report This Question

Q.25 List some character functions.

Some of the most used character functions are -
INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.

Report This Question

Q.26 How do you declare and use an explicit cursor in PL/SQL?

You declare an explicit cursor using the DECLARE section and use it with OPEN, FETCH, and CLOSE statements.

Report This Question

Q.27 How can you reference column values BEFORE and AFTER you have inserted and deleted triggers?

We can reference column values BEFORE and AFTER you have inserted and deleted triggers by -
Using the keyword "new.column name", the triggers can reference column values by new collection.
Using the keyword "old.column name", they can reference column vaues by old collection.

Report This Question

Q.28 Explain the difference between a stored procedure and a function in PL/SQL.

A stored procedure doesn't return a value, whereas a function returns a value.

Report This Question

Q.29 State the uses of SYSDATE and USER keywords?

SYSDATE indicates the current server system date. It is a pseudo column. On the other hand USER is also a pseudo column but refers to current user logged onto the session. Primarily they are used to monitor changes happening in the table.

Report This Question

Q.30 What is a PL/SQL trigger?

A trigger is a PL/SQL block that is automatically executed in response to a specific database event, such as an insert, update, or delete operation.

Report This Question

Q.31 How will ROWID assist in running a query faster?

ROWID is the logical address of a row, such that it is not a physical column. ROWID composes of data block number, file number and row number in the data block. Therefore I/O time gets minimized retrieving the row, and results in a faster query.

Report This Question

Q.32 How do you create and execute a PL/SQL procedure?

Use the CREATE PROCEDURE statement to create a procedure, and execute it using the EXECUTE or CALL statement.

Report This Question

Q.33 How can you handle exceptions in PL/SQL?

You can handle exceptions using the EXCEPTION section and predefined exception handlers or user-defined exceptions.

Report This Question

Q.34 What is a cursor FOR loop in PL/SQL, and how is it used?

A cursor FOR loop simplifies iterating through the records returned by a cursor. It automatically opens, fetches, and closes the cursor.

Report This Question

Q.35 Explain the difference between a BEFORE trigger and an AFTER trigger in PL/SQL.

A BEFORE trigger fires before the database event, while an AFTER trigger fires after the event.

Report This Question

Q.36 How do you pass parameters to PL/SQL procedures and functions?

Parameters can be passed using the IN, OUT, and IN OUT modes in procedure and function declarations.

Report This Question

Q.37 What is a PL/SQL record, and how is it used?

A PL/SQL record is a composite data type that can hold related data items. It's often used to hold the result of a query.

Report This Question

Q.38 What is a package in PL/SQL?

A package is a database object that groups related procedures, functions, variables, and cursors into a single unit.

Report This Question

Q.39 Explain the difference between a package specification and a package body in PL/SQL.

The specification defines the package interface (public), while the body contains the implementation (private).

Report This Question

Q.40 How do you call a PL/SQL function in SQL statements?

You can call a PL/SQL function in SQL statements by using it in the SELECT statement or WHERE clause.

Report This Question

Q.41 What is dynamic SQL in PL/SQL?

Dynamic SQL allows you to construct and execute SQL statements at runtime, using the EXECUTE IMMEDIATE statement.

Report This Question

Q.42 How do you handle NULL values in PL/SQL?

You can use the IS NULL and IS NOT NULL conditions to check for NULL values in PL/SQL.

Report This Question

Q.43 Explain the purpose of the RAISE_APPLICATION_ERROR procedure.

RAISE_APPLICATION_ERROR allows you to raise custom exceptions with user-defined error codes and messages.

Report This Question

Q.44 What is an autonomous transaction in PL/SQL?

An autonomous transaction is a separate transaction nested within the main transaction, allowing you to commit or roll back independently.

Report This Question

Q.45 What is bulk processing in PL/SQL?

Bulk processing involves processing multiple rows of data at once, typically using bulk operations like FORALL and BULK COLLECT.

Report This Question

Q.46 How do you declare and use PL/SQL tables?

PL/SQL tables are declared in the declaration section and used to hold sets of data. They are similar to arrays.

Report This Question

Q.47 What is a pragma in PL/SQL, and how is it used?

A pragma is a compiler directive that provides additional information to the PL/SQL compiler. For example, AUTONOMOUS_TRANSACTION is used for autonomous transactions.

Report This Question

Q.48 How can you improve the performance of PL/SQL code?

Performance can be improved by using proper indexing, bulk processing, and optimizing SQL queries.

Report This Question

Q.49 Explain the purpose of the DBMS_OUTPUT package in PL/SQL.

DBMS_OUTPUT is used for displaying debug information and messages from PL/SQL code.

Report This Question

Q.50 What is the purpose of the %TYPE and %ROWTYPE attributes in PL/SQL?

%TYPE is used to declare variables that have the same data type as a database column, and %ROWTYPE is used to declare variables that have the same structure as a table row.

Report This Question

Q.51 How do you handle deadlock situations in PL/SQL?

Deadlocks can be resolved using deadlock detection algorithms or by implementing retry mechanisms in PL/SQL code.

Report This Question

Q.52 Explain the use of the FOR UPDATE clause in PL/SQL cursors.

The FOR UPDATE clause is used to lock rows returned by a cursor, allowing them to be updated without conflicts.

Report This Question

Q.53 What is the purpose of the UTL_FILE package in PL/SQL?

UTL_FILE is used to read from and write to operating system files from PL/SQL code.

Report This Question

Q.54 How do you handle transactions in PL/SQL?

Transactions can be controlled using COMMIT and ROLLBACK statements to ensure data consistency.

Report This Question

Q.55 Explain the use of the NOCOPY hint in PL/SQL parameters.

The NOCOPY hint can improve performance by avoiding unnecessary copying of large variables in parameter passing.

Report This Question

Q.56 How do you create an anonymous PL/SQL block?

An anonymous PL/SQL block is created without a name and can be executed directly from a SQL client or tool.

Report This Question

Q.57 What is a savepoint, and how is it used in PL/SQL?

A savepoint is a point within a transaction to which you can later roll back if needed, using the ROLLBACK TO statement.

Report This Question

Q.58 Explain the use of the CONTINUE statement in PL/SQL loops.

The CONTINUE statement is used to skip the current iteration of a loop and proceed to the next iteration.

Report This Question

Q.59 How do you handle date and time values in PL/SQL?

PL/SQL provides data types like DATE, TIMESTAMP, and functions like SYSTIMESTAMP to work with date and time values.

Report This Question

Q.60 What is an index in PL/SQL, and why is it important?

An index is a database object that improves the speed of data retrieval by providing fast access to rows in a table.

Report This Question

Q.61 How do you create and drop PL/SQL triggers?

Triggers can be created using the CREATE TRIGGER statement and dropped using DROP TRIGGER.

Report This Question

Q.62 Explain the use of the RAISE statement in PL/SQL.

The RAISE statement is used to raise a user-defined exception or re-raise a caught exception.

Report This Question

Q.63 What is the purpose of the PRAGMA EXCEPTION_INIT directive?

PRAGMA EXCEPTION_INIT associates a user-defined exception with an Oracle error code.

Report This Question

Q.64 How do you handle multi-row queries in PL/SQL?

Multi-row queries can be handled using cursors, BULK COLLECT, and FORALL constructs.

Report This Question

Q.65 What is the purpose of the DBMS_SCHEDULER package in PL/SQL?

DBMS_SCHEDULER is used to create, manage, and schedule jobs and tasks in the database.

Report This Question

Q.66 How do you use the CASE statement in PL/SQL?

The CASE statement is used for conditional branching in PL/SQL, similar to IF-THEN-ELSE constructs.

Report This Question

Q.67 Explain the concept of implicit type conversion in PL/SQL.

Implicit type conversion is the automatic conversion of data types in expressions, depending on the context.

Report This Question

Q.68 What is an in-line view in PL/SQL, and how is it used?

An in-line view is a subquery placed in the FROM clause of a SQL statement, often used for complex queries.

Report This Question

Q.69 How do you handle arrays in PL/SQL?

Arrays in PL/SQL can be implemented using PL/SQL tables or VARRAYs (variable arrays).

Report This Question

Q.70 Explain the use of the DBMS_ALERT package in PL/SQL.

DBMS_ALERT is used for communication between PL/SQL sessions, allowing one session to signal another.

Report This Question

Q.71 What is a mutating table error in PL/SQL, and how can it be resolved?

A mutating table error occurs when a trigger tries to modify the table that fired it. It can be resolved using a compound trigger or rewriting the logic.

Report This Question

Q.72 How do you use the DECODE function in PL/SQL?

The DECODE function is used for conditional data transformations in SQL statements.

Report This Question

Q.73 What is the purpose of the DBMS_APPLICATION_INFO package in PL/SQL?

DBMS_APPLICATION_INFO is used to set client application information in the database, aiding in performance monitoring and tuning.

Report This Question

Q.74 How can you retrieve the current user's session information in PL/SQL?

Session information can be retrieved using the SYS_CONTEXT function.

Report This Question

Q.75 What is the purpose of the DBMS_LOB package in PL/SQL?

DBMS_LOB is used to work with large objects (LOBs) like BLOBs, CLOBs, and NCLOBs in the database.

Report This Question

Q.76 How do you create and use triggers for auditing in PL/SQL?

Auditing triggers are created to log changes to specific tables for security and compliance purposes.

Report This Question

Q.77 Explain the difference between a row-level trigger and a statement-level trigger.

A row-level trigger fires once for each affected row, while a statement-level trigger fires once for the entire statement.

Report This Question

Q.78 How can you handle SQL injection in PL/SQL code?

SQL injection can be prevented by using bind variables and not concatenating user input directly into SQL statements.

Report This Question

Q.79 What is the purpose of the DBMS_UTILITY package in PL/SQL?

DBMS_UTILITY provides various utility functions for database operations, such as name resolution and data type conversions.

Report This Question

Q.80 How do you use the ROLLBACK statement in PL/SQL?

The ROLLBACK statement is used to undo all changes made during the current transaction.

Report This Question

Q.81 Explain the concept of row-level security in PL/SQL.

Row-level security restricts access to specific rows in a table based on user privileges or policies.

Report This Question

Q.82 How do you use the RETURNING clause in DML statements in PL/SQL?

The RETURNING clause allows you to return values from INSERT, UPDATE, and DELETE statements.

Report This Question

Q.83 What is dynamic SQL and how is it used in PL/SQL?

Dynamic SQL allows you to construct and execute SQL statements at runtime, often used for dynamic queries.

Report This Question

Q.84 How do you use the PIVOT and UNPIVOT operations in PL/SQL?

PIVOT transforms rows into columns, while UNPIVOT does the reverse. They are used for data transformation.

Report This Question

Q.85 Explain the concept of bulk binding in PL/SQL.

Bulk binding optimizes SQL operations by processing multiple rows at once, typically using collections.

Report This Question

Q.86 What is a PL/SQL function result cache, and how is it used?

A function result cache stores the results of a function for repeated calls with the same inputs, improving performance.

Report This Question

Q.87 How do you use the WITH clause (Common Table Expressions) in PL/SQL?

The WITH clause is used to create temporary result sets within a query for readability and reusability.

Report This Question

Q.88 What is a PL/SQL trigger compound trigger, and how is it different from regular triggers?

A compound trigger is a single trigger that includes various timing points, making it more versatile than regular triggers.

Report This Question

Q.89 How do you handle errors in PL/SQL functions?

Errors in functions can be handled by raising exceptions or returning error codes or NULL values.

Report This Question

Q.90 What is the use of the PRAGMA RESTRICT_REFERENCES directive in PL/SQL?

PRAGMA RESTRICT_REFERENCES is used to specify restrictions on a PL/SQL function's behavior, particularly when it's used in SQL statements.

Report This Question

Q.91 How do you create and use PL/SQL packages with global variables?

Global variables in packages are declared in the package specification and can be accessed by any procedure or function in the package.

Report This Question

Q.92 Explain the use of the FORALL statement in PL/SQL.

FORALL is used for bulk DML operations, allowing multiple rows to be inserted, updated, or deleted in a single statement.

Report This Question

Q.93 What is a PRAGMA AUTONOMOUS_TRANSACTION in PL/SQL, and why is it used?

PRAGMA AUTONOMOUS_TRANSACTION allows a procedure or function to execute SQL statements independently of the main transaction.

Report This Question

Q.94 How do you implement error logging in PL/SQL?

Error logging can be implemented using custom error tables or the built-in DBMS_ERRLOG package.

Report This Question

Q.95 What is the purpose of the DBMS_PIPE package in PL/SQL?

DBMS_PIPE is used for interprocess communication between PL/SQL sessions in the same database.

Report This Question

Q.96 How do you use the SAVE EXCEPTIONS clause in PL/SQL?

The SAVE EXCEPTIONS clause in a FORALL statement allows you to capture and handle exceptions while processing multiple rows.

Report This Question

Q.97 Explain the use of the UTL_TCP package in PL/SQL.

UTL_TCP is used for TCP/IP socket-based communication in PL/SQL programs.

Report This Question

Q.98 What is the purpose of the DBMS_PARALLEL_EXECUTE package in PL/SQL?

DBMS_PARALLEL_EXECUTE is used for managing and executing tasks in parallel for data manipulation.

Report This Question

Q.99 How do you use the PRAGMA INLINE directive in PL/SQL?

PRAGMA INLINE hints the compiler to inline a particular function or procedure for better performance.

Report This Question

Q.100 What is the use of the DBMS_SESSION package in PL/SQL?

DBMS_SESSION is used to manage and query session-specific information and settings.

Report This Question

Q.101 How do you create and use PL/SQL triggers for auditing changes to specific columns?

Triggers can be created to capture and log changes to specific columns in a table.

Report This Question

Q.102 Explain the use of the DBMS_ALERT.WAITONE procedure in PL/SQL.

DBMS_ALERT.WAITONE is used to wait for a specific alert message in a PL/SQL session.

Report This Question

Q.103 How do you use the NO_DATA_FOUND exception in PL/SQL?

NO_DATA_FOUND is raised when a SELECT statement returns no rows. It can be caught and handled in PL/SQL code.

Report This Question

Q.104 What is a PL/SQL cursor variable, and how is it used?

A cursor variable is a pointer to a cursor that allows dynamic SQL execution. It's used in scenarios where the cursor is not known at compile time.

Report This Question

Q.105 What does dirty read refers to?

Dirty read refers to inconsistent data reading during multiple transaction processing

Report This Question

Q.106 How do you use the EXECUTE IMMEDIATE statement with dynamic SQL in PL/SQL?

EXECUTE IMMEDIATE is used to execute dynamic SQL statements constructed at runtime.

Report This Question

Q.107 Why does the following statement in the declaration section fail : PRODUCT_IN_STOCK BOOLEAN := 'TRUE';

A Boolean variable cannot be assigned a character string value.

Report This Question

Q.108 What is the use of using RAISE keyword in exception blocks ?

To raise same exception again

Report This Question

Q.109 Why does the following declaration fail : MY_NUMBER POSITIVEN;

A variable declared as POSITIVEN should always be initialized to a non-NULL value.

Report This Question

Q.110 What will the result of executing REPLACE('BLACK AND BLUE','BL','J')

JACK AND JUE

Report This Question