PL/SQL Interview Questions

PL/SQL developers are in huge demand these days, as the platform has nothing but great benefits to offer. For enhanced security, portability and robustness of databases, the Oracle developers prefer PL/SQL than SQL. Here are a few PL/SQL questions that can help to crack an interview:

Read Top 20+ PL/SQL Interview Questions and Answers

#1 Differentiate between syntax and runtime errors?

The errors such as spelling mistakes come under syntax errors, which can easily be found using a PL/SQL compiler. As far as Runtime error is concerned, they are found in PL/SQL block. You need to add an exception handling section to handle those errors. Such errors come under SELECT INTO statements that return no rows.

#2 What is PL/SQL?

PL/SQL stands for Procedural Language extension of Structured Query Language (SQL). It is a block-structured language having logical blocks which are made up of three sub-blocks i.e. a declarative part, an executable part and an exception building part. PL/SQL is integrated with Oracle and the functionalities of PL/SQL extend after each release of Oracle database.

It includes procedural language elements like conditions and loops and allows declaration of constants and variables, procedures and functions. It also helps the users to develop complex database applications using control structures, procedures, modules, etc. PL/SQL is not limited by any case-sensitive letters so you are free to use lower case letters or upper case letters.

#3 Explain the purpose of %TYPE AND %ROWTYPE data types?

%TYPE- The %TYPE lets you declare a constant, variable, collection element, etc as previously declared variable or column. It is an attribute which is used for anchoring. Example- the variable m_empno has same data type as the column empno in table emp.

%ROWTYPE- the %Rowtype attribute lets you declare a record that represents a row in the table.  The fields of the row have same name and data types as column in the view. Example- dept_rec dept%ROWTYPE . This declares a record that can store an entire row for DEPT table.

#4  What are some predefined exceptions in PL/SQL?

Here is a list of some predefined exceptions that can be used in PL/SQL:


#5 What is the difference between execution of triggers and stored procedures?

Stored procedure takes place explicitly when call statements are issued from another block. The trigger is implicitly executed whenever any activating events like the occurrence of DML statements happen.

#6 What are PL/SQL packages?

PL/SQL packages are schema objects that groups logically related functions stored procedures, cursors and variables at one place. The package is compiled and stored in a database and its contents can be shared. Packages have two parts: a specification and a body.

#7 What is the basic structure of PL/SQL?

The basic structure of PL/SQL is the BLOCK structure. Every program of PL/SQL includes both the SQL statement and the PL/SQL statement, which eventually forms the PL/SQL block. Every PL/SQL block has three defined sections out of which two sections are optional i.e. the declaration section and the exception handling section and one section is mandatory i.e. the execution section.

#8 What is PL/SQL table? Why is it used?

PL/SQL tables are those that are type tables of modeled database.  They are capable of providing arrays or so-called temporary tables. These arrays are located in memory that is instantly processed. The PL/SQL tables are capable of moving big data by simplification of moving data collection.

#9 What is the difference between ROLLBACK and ROLLBACK TO statements?

There is a considerable difference between the ROLLBACK and ROLLBACK TO statement. When you apply the ROLLBACK command a transaction is completely undone where all the blocks are released. As far as ROLLBACK TO command is concerned, the transaction is undone but till a SAVEPOINT. Thus, the transaction remains live and active even after the command is implemented.

#10 What is a mutating table and a constraining table?

A mutating table is the one, which is modified using a DML statement or a table with defined triggers. A constraining table is the one, which is being read for a referential integrity constraint.

#11 What is the difference between function, procedure and package in PL/SQL?

There is a considerable difference between function, procedure and package.

Function: The PL/SQL function consists of return type and is responsible for computing so that it can return a single value. Due to a specification in return type, the value returned must be in that same type.

Procedure: Procedure is just responsible for a return statement that can stop an execution and return back to the caller. Unlike a function, the procedure does not have any specific return type and doesn’t return single but multiple values.

Package: A package, which is a schema object, is responsible for grouping PL/SQL types, subprograms and items, which are logically related. Package consists of all i.e. procedure, record type statement, function, and procedure. Being able to provide modularity, it is cable of developing an application. Using a package, you can hide information coming from an unauthorized user.

#12 Differentiate between PL/SQL and SQL?

PL/SQL is Oracle’s Procedural Language SQL which allows you to write full programs to execute options like insert/ select/ updates/ delete. SQL is a query language that allows you to execute a single insert/ delete/ update. The following are the notable differences between SQL and PL/SQL:

SQL is a data-oriented language which is very useful for interactive processing.PL/SQL is a procedural language to create applications. It is an extension of Oracle.
SQL doesn’t have any procedural capabilities like condition testing, looping. It tells the database what to do and not how to do.On the contrary, PL/SQL offers procedural capabilities like condition testing, looping, etc. it also offers high language features.
SQL is executed by the database one statement at a time. It is a time-consuming process.PL/SQL is less time consuming as compared with SQL. It sends the block of codes to the database server at the same time.
There are very less chances of error handling in SQL. 

#13 What are PL/SQL cursors?

PL/SQL controls the context area through cursors. PL/SQL requires cursors to retrieve and process more than one row. A cursor is a pointer to the area of the memory containing SQL statements and information for processing the statements. PL/SQL is a mechanism under which multiple rows are selected from the database. Then, each row selected is individually processed inside PL/SQL programs.

#14 Enlist the data types that can be used in PL/SQL?

Each constant, value and parameter has a data type that tells us the type of data used and their associated operations in the program. The following are four data types available in PL/SQL:

  1. Scalar data type-these are single-valued data types with no internal components. They are further classified into four categories-
  2. CHAR– these data types hold the fixed-length character strings. Its value ranges from 1 to 32,767 characters.
  3. VARCHAR2– these data types store variable-length character strings ranging from 1 to 32,767 characters.
  4. BOOLEAN– it is a data type with two values, true or false, intended to represent the truth values of logic and Boolean algebra.
  5. DATE– it is a data type for storing date and time information.
  6. LONG– it is an Oracle data type for storing character data of variable length.
  1. Composite data type- it is a combination of other data types and internal components that can be easily altered and manipulated. These include record, table and array.
  2. Reference data type- it is a data type that holds pointers to other data items. Ref Cursor is an example.
  3. Large object data type- These data types hold locators that defines the location of large objects stored out of the line. These are stored separately from other data items.

#15 How many types of triggers exist in PL/SQL?

A total of 12 types of triggers can be found in PL/SQL that have to be used in combinations. Some of the triggers are AFTER, DELETE, ROW, BEFORE, TABLE, INSERT, UPDATE etc. The few possible combinations are:
Ask a Question