Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up

Top Pl Sql Interview Questions And Answers

oracle pl sql interview questions
This is a list of top PL/SQL interview questions that has been compiled by industry experts working in the PL/SQL domain. In these interview questions you will understand what is the importance of PL/SQL, database triggers, PL/SQL compilation process, PL/SQL Packages, tracing a PL/SQL code, PL/SQL functions and procedures, Joins & Views in PL/SQL. Learn PL/SQL from Intellipaat PL/SQL certification training to excel in your career.

Want to Learn PL/SQL? Click Here

Top Answers to PL-SQL Interview Questions

1. Compare SQL & PL/SQL
Criteria SQL PL/SQL
What it is Single query or command execution Full programming language
What it comprises Data source for reports, web pages Application language to build, format and display report, web pages
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, conditional branching.

Go through this tutorial to learn more about PL/SQL.

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

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

Master PL/SQL, in this PL/SQL certification training.

Become PL SQL Certified in 24 hrs.


4. Explain the uses of 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

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

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

Compilation process includes syntax check, bind and p-code generation processes.Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. 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.

Go through this tutorial to learn more about handling PL/SQL errors.

6. Mention what PL/SQL package consists 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 the insightful PL/SQL tutorial to learn more about Pl/SQL Packages.

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

It provides several benefits like

  • Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
  • Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
  • Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
  • Object oriented design: The package gives developers strong hold 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.
8. What are different methods to trace the PL/SQL code?

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

  • trcsess and tkproof utilities
9. What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?

Function: The main purpose of a PL/SQL function is generally 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 schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.

10. What is stored Procedure?

A stored procedure is a sequence of statement or a named PL/SQL block which 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 schema object. It can be nested, invoked and parameterized.

Learn more about PL/SQL Collections & Records.

11. What is cursor and why it is required?

A cursor is a temporary work area created in a system memory when an 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 and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.

12. Day-to –Day activities?
  1. creating Database objects—Tables,synonyms, sequences
  2. To implement Business Rules— creat procedures, functions
  3. To impose Business Rules— create constraints, Trigers.
  4. Data manipulation———– create cursors.
13. Display records having max salary?

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

14. Display Highest salary from table?

Select max(sal) from emp;

15. Display 2nd Highest salary from table?

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

16. What is a JOIN?

It is a Keyword, used to query data from multiple tables based on relationship between the fields of the tables. Keys plays major role in JOINs.

17. what is View?
  • It is a virtual table ,which consists of data contained in a table.
  • Views not need any memory space .
  • View can be created on multiple tables.
18. What is subquery? Types.
  • A subquery is a query within another query.
  • The outer query is known as Main query, & inner query is called subquery.
  • Subquery is executed first & result of subquery is passed to main query.
  • Types——— correlated & non-correlated
19. what is Trigger?

It 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.

"1 Responses on Top Pl Sql Interview Questions And Answers"

  1. Sulemaan says:

    Very useful…Thanks for sharing….

Leave a Message

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

Sales Offer

Sign Up or Login to view the Free Top Pl Sql Interview Questions And Answers.