Categories

Top Oracle PL/SQL Interview Questions and Answers

CTA

PL/SQL is an extension of SQL and can be referred to as Procedural Language/SQL that allows users to build complex database applications with the help of procedures, modules, functions, control structures, and more. In this blog on Oracle PL/SQL Interview Questions, we aim to discuss some of the most popular questions that recruiters of major companies generally ask during job interviews. Here is a list of these interview questions along with their answers:

Q1. Compare between SQL and PL/SQL.
Q2. What is PL/SQL?
Q3. What is the basic structure of PL/SQL?
Q4. Explain the uses of a database trigger.
Q5. What are the data types present in PL/SQL?
Q6. How is a process of PL/SQL compiled?
Q7. What does a PL/SQL package consist of?
Q8. What do you know about the commands COMMIT, ROLLBACK, and SAVEPOINT?
Q9. What are the benefits of PL/SQL packages?
Q10. What is Exception handling?

The three parts into which this PL/SQL Interview Questions blog is divided are as follows:
1. Basic

2. Intermediate

3. Advanced

Watch this video on PL/SQL Interview Questions and Answers on YouTube:

Top 60 PL/SQL Interview Questions and Answers

Youtube subscribe

Basic PL SQL Interview Questions for Freshers

1. Compare between SQL and PL/SQL.

Criteria SQL PL/SQL
What is it? A single query or command execution A full programming language
What does it comprise? The data source for reports, web pages, etc. An application language to build, format, and display reports, web pages, etc.
Characteristic Declarative in nature Procedural in nature
Used for Manipulating data Creating applications

2. What is PL/SQL?

Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.

Go through this PL/SQL Tutorial to learn ‘What is PL/SQL?’

3. What is the basic structure of PL/SQL?

PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

basic structure of PL/SQL

CTA

Become a Database Architect

4. Explain the uses of a database trigger.

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for:

  • Audit data modifications
  • Log events transparently
  • Enforce complex business rules
  • Maintain replica tables
  • Derive column values
  • Implement Complex security authorizations

database trigger

Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and the range of values and operations are determined.

5. What are the data types present in PL/SQL?

There are various kinds of data types are available in PL/SQL which are:

  1. Scalar data types: A scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, BOOLEAN are some examples of scalar data types.
  2. Composite data types: A composite data type is made up of different data types that are easy to update and have internal components that can be utilized and modified together. For instance, RECORD, TABLE, VARRAY, and so on.
  3. Reference data types: A reference data type stores pointers, which are values that relate to other programs or data elements. REF, CURSOR is an example of a reference data type.
  4. Large object data type: A large object data type stores locators, which define the location of large items stored out of line (such as video clips, graphic images, and so on). BLOB, BFILE, CLOB, NCLOB, and others are examples of large object data types.

Get 50% Hike!

Master Most in Demand Skills Now !

6. How is a process of PL/SQL compiled?

Syntax checking, binding, and P-code generation are all part of the compilation process. Syntax checking looks for compilation issues in PL/SQL code. After all mistakes have been fixed, the data holding variables are given a storage address. This process is referred to as binding. The PL/SQL engine’s P-code is a set of instructions. For named blocks, P-code is saved in the database and used the next time it is run.

Go through the Handling PL/SQL Errors tutorial page to know how error handling is done in PL/SQL!

Intermediate PL SQL Interview Questions

7. What does a PL/SQL package consist of?

A PL/SQL package consists of:

  • PL/SQL table and record TYPE statements
  • Procedures and functions
  • Cursors
  • Variables ( tables, scalars, records, etc.) and constants
  • Exception names and pragmas for relating an error number with an exception
  • Cursors

Check out the insightful PL/SQL tutorial to learn more about Pl/SQL Packages!

8. What do you know about the commands COMMIT, ROLLBACK, and SAVEPOINT?

COMMIT: The COMMIT command saves changes to a database permanently during the current transaction.

ROLLBACK: The ROLLBACK command is used at the end of a transaction to undo any modifications made since the start of the transaction.

SAVEPOINT: During transaction processing, the SAVEPOINT command saves the current point with a unique name.

9. What are the benefits of PL/SQL packages?

PL/SQL packages provide several benefits as follows:

Benefits of PL/SQL packages

  • Enforced information hiding: It offers the liberty to choose whether to keep data private or public.
  • Top-down design: We can design the interface to the code hidden in the package before we actually implemented the modules.
  • Object persistence: Objects declared in a package specification behave like global data for all PL/SQL objects in the application. We can modify the package in one module and then reference those changes to another module.
  • Object-oriented design: The package gives developers stronghold over how the modules and data structures inside the package can be used.
  • Guaranteeing transaction integrity: It provides a level of transaction integrity.
  • Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.

Master PL/SQL from this top-rated PL/SQL Certification Training!

10. What is Exception handling?

Exception handling in PL/SQL can be adjusted. When an error occurs, the program’s error handling code is included. There are three different sorts of exceptions:

  • Pre-defined exceptions are frequent errors that have already been defined. NO DATA FOUND is an example.
  • Undefined exceptions are errors that don’t have a name assigned to them.
  • User-defined exceptions are handled by the user’s own code.

11. Mention a few predefined exceptions?

The following are some examples of predefined exceptions:

  • NO DATA FOUND: A single-row SELECT statement that returns no data.
  • TOO MANY ROWS: A single row SELECT statement that returns many rows.
  • INVALID CURSOR: An incorrect cursor operation was performed.
  • ZERO DIVIDE: Attempt at zero division.

Career Transition

12. What are the types of SQL statements?

The five types of SQL statements are:

  1. DDL: The Data Definition Language(DDL) helps in the creation of a database structure or schema. CREATE, DROP, ALTER, RENAME, and TRUNCATE are the five types of DDL commands in SQL.
  2. DML: The Data Manipulation Language (DML) allows you to insert, change, and delete data from a database instance. It’s incharge of making all kinds of changes to a database’s data. The database application and the user can insert data and information using three basic commands which are: INSERT, UPDATE, DELETE.
  3. DCL: GRANT and REVOKE are commands in the DCL (Data Control Language) that can be used to grant “rights and permissions.” The database system’s parameters are controlled by other permissions.
  4. TCL: TCL commands, or Transaction Control Language, deal with database transactions. COMMIT, ROLLBACK, SAVEPOINT are the commands of TCL.
  5. DQL: The data is retrieved from the database using Data Query Language (DQL). It just has one command, which is SELECT

13. What are different methods to trace the PL/SQL code?

Tracing the code is a crucial technique to measure its performance during the runtime. Different methods for tracing the code includes:

  • DBMS_APPLICATION_INFO
  • DBMS_TRACE
  • DBMS_SESSION and DBMS_MONITOR
  • trcsess and tkprof utilities

14. What are the types of parameters in PL/SQL?

There are three types of parameters in PL/SQL which are as follows:

  • IN: IN parameters allow you to send values to the procedure that is being called, and they can be set to default values. They behave as constants and cannot be changed.
  • OUT: OUT arguments must be mentioned because they return a value to the caller. These are uninitialized variables that can’t be used in expressions.
  • IN OUT: IN OUT parameters send starting values to a procedure and then return updated values to the caller. These parameters should be treated as initialized variables and given a value.

15. What are PL/SQL records?

A PL/SQL record is a collection of values, or to put it another way, it is a collection of many pieces of information, each of which is of a simpler type and can be associated with one another as fields.

In PL/SQL, three types of records are supported:

  • Records based on tables
  • Records created by programmers
  • Records that are based on a cursor

16. Why do we use Index in a table?

In a table, we use indexes to allow quick access to rows. For procedures that return a small percentage of a table’s rows, indexes allow faster access to data.

17. What is the difference between functions, procedures, and packages in PL/SQL?

  • Function: The main purpose of a PL/SQL function is to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
  • Procedure: A procedure does not have a return type and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values; otherwise, it is generally similar to a function.
  • Package: A package is a schema object which groups logically related PL/SQL types, items, and subprograms. You can also say that it is a group of functions, procedures, variables, and record TYPE statement. It provides modularity, due to which it aids application development. It is used to hide information from unauthorized users.

CTA

Become a SQL Developer

18. Why we use database links?

Database links are used to establish communication across multiple databases or environments such as test, development, and production. Other information can be accessed using the database links, which are read-only.

19. What is a stored procedure?

A stored procedure is a sequence of statements or a named PL/SQL block that performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object. It can be nested, invoked, and parameterized.

20. What is the Overloading of a procedure?

When the name of the same procedure is repeated with parameters of different datatypes and parameters in different places then that is referred to as procedure overloading.

21. What is meant by expressions?

An expression is made up of a series of literals and variables separated by operators. Operators are used in PL/SQL to manipulate, compare, and calculate data. An expression is made up of two parts: operators and operands.

22. Which cursor attributes are the result of a DML statement saved when it is executed?

The statement’s result is saved in four cursor attributes. The four attributes are: 

  • SQL% FOUND 
  • SQL% NOTFOUND 
  • SQL% ROWCOUNT 
  • SQL% ISOPEN

23. What is a cursor? Why is it required?

A cursor is a temporary work area created in system memory when a SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data, retrieved from the database, to manipulate it. A cursor can hold more than one row but can process only one row at a time. A cursor is required to process rows individually for queries.

24. What are the types of cursors?

There are two types of cursors are there:

  1. Implicit cursor: When PL/SQL executes a SQL statement, it automatically constructs a cursor without specifying one; these cursors are known as implicit cursors.

PL/SQL uses implicit cursors for the following statements.

  • INSERT
  • UPDATE
  • DELETE
  • SELECT
  1. Explicit cursor: A programmer declares and names an explicit cursor for queries that return more than one row. An explicit cursor is a SELECT statement that is declared explicitly in the current block’s declaration section or in a package definition. The following are the commands we use for the explicit cursor in PL/SQL.
  • OPEN
  • FETCH
  • CLOSE

25. What is the Open cursor command function?

When the OPEN cursor command is used to open a cursor, it performs the following operations:

  • A processing memory region has been set aside.
  • The statement SELECT is parsed.
  • The memory addresses are used to assign values to the input variables.
  • The active set of rows that meet the criteria for selection are recognized.
  • The pointer is placed exactly before the active set’s first row.

26. How to delete a trigger?

To delete a trigger, we need to use the command DROP TRIGGER. 

27. Can you tell me the advantages of stored procedures?

Stored procedures have various advantages to help you design sophisticated database systems, as listed below: 

  • Better Performance
  • Higher Productivity
  • Ease of Use
  • Increased Scalability.
  • High Maintainability.
  • Interoperability
  • Advance Security
  • Replication

28. hat are the various types of schema objects that PL/SQL can create?

There are various types of schema objects. Some of them are mentioned below:

  • Stored procedures, packages, and functions
  • Object tables, object types, and object views
  • Database Triggers
  • Database links
  • Cursors
  • Table 
  • View and many more.

29. What is the advantage of Implicit records?

Implicit records are handy since they do not require a hard-coded description. Because they’re based on database table records, any changes to the database table record will be reflected in the record automatically.

30. What is a trigger?

A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to the database objects.

31. What are the basic parts of the trigger?

There are three basic parts of a trigger which are 

  • A triggering event or statement. 
  • A trigger restriction. 
  • A trigger action.

32. Mention the ways of commenting in PL/SQL?

In PL/SQL, Comments help readability by describing the purpose and function of code portions. In PL/SQL, two types of comments are available. Which are as follows: 

Single-line comments: Single-line comments start with a double hyphen (- -) at the beginning of a line and go all the way to the conclusion. 

Multi-line comments: Multi-line comments start with a slash-asterisk (/*) and terminate with an asterisk-slash (*/), and they can span several lines.

33. What is %TYPE?

The % TYPE property is used to declare the column in a table that includes the value of that column. The variable’s data type is the same as the table’s column.

34. What is %ROWTYPE?

The % ROW TYPE property is used to declare a variable that contains the structure of the records in a table. The variable’s data type is the same as the table’s columns.

35. Differentiate Between A Temporary And Permanent Tablespace.

Temporary tablespaces are used to store temporary items like sort structures, whereas permanent tablespaces are used to store things that will be used as the database’s genuine objects.

36. How many triggers can be applied to a table?

We can add a maximum of 12 triggers to a table. 

Wish to learn more? Visit the PL/SQL Collections and Records tutorial page!

Advanced PL SQL Interview Questions and Answers for Experienced Professionals

37. What are Pseudo-columns and How Do They Work? How Can Pseudo Columns Be Used In Procedure Statements?

Pseudo-columns aren’t genuine table columns, but they behave like them. They are used to retrieve specific information in SQL statements. Although pseudo-columns are recognized by PL/SQL as part of SQL statements, they cannot be used directly in a procedural language. In S, the pseudo-columns listed below are used.

  • CURRVAL and NEXTVAL
  • LEVEL
  • ROWID
  • ROWNUM

38. What is the use of the || operator?

The strings are concatenated using the || operator. Select statements and DBMS OUPUT.put line both employ the || operator.

39. What Is The Difference Between Sqlcode And Sqlerrm, And Why Are They Important For Pl/SQL Developers?

The value of the error number for the most recent error detected is returned by SQLCODE. The SQLERRM function returns the actual error message for the most recent issue. They can be used in exception handling to report or save the error that happened in the code in an error log database. These are especially important for the exception WHEN OTHERS.

40. What is raise_application_error?

This procedure can be used to send user-defined error messages from stored subprograms. You can prevent returning unhandled exceptions by reporting failures to your application. It appears in two places: the executable section and the exceptional section.

41. In PL/SQL, How can we verify whether an update statement is executed or not?

The SQL % NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records. If the last SQL statement run had no effect on any rows, this variable returns TRUE.

42. Explain the Day-to-day activities in PL/SQL.

  1. Create database objects—tables, synonyms, sequences, etc.
  2. To implement business rules, create procedures, functions, etc.
  3. To impose business rules, create constraints, triggers, etc.
  4. For data manipulation, create cursors

43. How can we locate a PL/SQL block when a cursor is open?

We can use the %ISOPEN variable cursor status to find the PL/SQL block.

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

The pragma_exception_init command in PL/SQL instructs the compiler to associate an exception name with an Oracle error number. This enables you to refer to any internal exception by name and create a custom handler for it.

45. In PL/SQL, What is the difference between Stored Procedure and Stored Function?

The key differences between the stored procedure and stored function are:

  • Returning the value in a stored procedure is optional, whereas returning the value in a stored function is required.
  • A stored procedure can have both input and output parameters, but a stored function only has one.
  • Exception handling is possible in Stored procedures, but it is not feasible in Stored functions.

46. How to display records having the maximum salary from an employee table?

Select * from emp where sal= (select max(sal) from emp)

If you have any doubts or queries related to PL/SQL, get them clarified from our PL/SQL experts on our SQL Community!

47. What is the syntax to disable the trigger?

ALTER TRIGGER TRIGGER_NAME DISABLE;

48. How to display the highest salary from an employee table?

Use the following code for displaying the highest salary from an employee table:

Select max(sal) from emp;

49. What is the command used for deleting a package?

The command used for deleting a package is DROP PACKAGE

50. How to display the second highest salary from an employee table?

Select max(sal) from emp where sal not in ( select max(sal) from emp

51. How can we view the user-defined functions and procedures in PL/SQL?

The table USER SOURCE will be used to store user-defined functions and procedures. To examine them, the function and procedure names should be specified in uppercase (in select command). The following command is used to inspect the source code of a user-defined function or method.

Select text from user_source where name=’PROCEDURE_NAME’;

52. What is a Join?

Join is a keyword used to query data from multiple tables based on the relationship between the fields of tables. Keys play a major role in Joins.

53. In PL/SQL, What is the purpose of the DBMS_OUTPUT package?

The PL/SQL output is shown on the screen using the DMS_OUTPUT package. get_line, put_Line, new_line, and many more are found in DBMS_OUTPUT. The put_line procedure, which is a part of the DBMS_OUPUT package, is used to display the information in the line.

54. what is a View?

  • A View is a virtual table consisting of data contained in a table.
  • Views do not need any memory space.
  • Views can be created on multiple tables.

What is a View in PL/SQL

55. How can we execute a stored procedure?

There are two steps to execute a stored procedure.  

  • Step 1: We need to use EXECUTE keyword. We can also use the EXEC keyword. 
  • Step 2: We need to call the name of the procedure from a PL/SQL block.

Syntax 

EXECUTE procedure_name;

Or

Exec  procedure_name;

56. What is the difference between ANY and ALL operators?

ALL operator: Value is compared to every value returned by the subquery using the ALL Operator.

  • > ALL denotes greater than the maximum
  • <ALL denotes less than the minimum
  • <>ALL is the same as NOT IN condition.

ANY operator: Value is compared to each value returned by the subquery using the ANY Operator. SOME is a synonym for ANY operator.

  • > ANY denotes something more than the bare minimum.
  • <ANY denotes a value lower than the maximum.
  • =ANY is the same as the IN operator.

57. How to create a Function?

The syntax to create a CREATE function is below:

CREATE function_name
RETURN return_datatype
{IS | AS}
DECLARE
VARIABLE DATATYPE;
BEGIN
function_body
END function_name;

58. How can we Switch From An Init.ora File to Spfile?

We can switch from Init.ora file to Spfile, if we create spfile from pfile command.

59. What is a subquery? What are its types?

A subquery is a query within another query. The outer query is known as the main query and the inner query is called the subquery. A subquery is executed first, and the result of the subquery is passed to the main query.

There are two types of subqueries: 

  • Correlated
  • Non-correlated

60. How can we read/write files in PL/SQL?

We can read/write operating system text files by the UTL_FILE package. It provides a restricted version of the operating system stream file I/O. And, It is available for both client-side and server-side PL/SQL.

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp','myoutput','z');
UTL_FILE.PUTF(file, 'Value of func1 is %sn',func1(2));
UTL_FILE.FCLOSE(file;
END;

61. How do we create Nested tables in PL/SQL?

In PL/SQL, one of the collection types is nested tables. They can be made in a PL/SQL block or at the schema level. These are similar to a 1D array, except their size can be dynamically extended.

The syntax to create a nested table

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

An example for creating nested tables in PL/SQL

DECLARE

TYPE deptname IS TABLE OF VARCHAR2(10);
TYPE budget IS TABLE OF INTEGER;
  names deptname;
  deptbudget budget;
 BEGIN
  names := deptname ('Finance', 'Sales', 'Marketing');
  deptbudget := budget (89899, 67879, 98999);
  FOR i IN 1 .. names.count LOOP
     dbms_output.put_line('Department = '||names(i)||', Budget = ' || deptbudget(i));
  end loop;
END;
/

Course Schedule

Name Date
SQL Training 2021-10-23 2021-10-24
(Sat-Sun) Weekend batch
View Details
SQL Training 2021-10-30 2021-10-31
(Sat-Sun) Weekend batch
View Details
SQL Training 2021-11-06 2021-11-07
(Sat-Sun) Weekend batch
View Details

1 thought on “Top 60 PL/SQL Interview Questions and Answers”

Leave a Reply

Your email address will not be published. Required fields are marked *