Most commonly asked 25 oracle plsql interview questions

Last updated on July 26th, 2018 at 04:57 pm

Here  is the compilation of Most commonly asked oracle plsql interview questions. These will for sure help you in the interview. The questions are from basic to advanced level and covers most of the aspects of the PLSQL programming. I hope you find this useful

oracle plsql interview questions

Question 1) What is Oracle plsql?

PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language
Question 2) What are the components of a PL/SQL Block?
Declarative part, Executable part and Exception part.

Question 3) What are the datatypes a available in PL/SQL?


Some scalar data types such as
Some composite data types such as RECORD & TABLE.

Question 4) What is a cursor? Why Cursor is required?

Cursor is a named private SQL area from where information can be accessed.
Cursors are required to process rows individually for queries returning multiple rows.

Question 5) What is the difference between Procedure and Function ?


Function Procedure
Function must return a value Procedure need not

FUNCTION name (argument list …..) Return datatype is
local variable declarations
executable statements
execution handlers




PROCEDURE name (parameter list…..)
local variable declarations

Executable statements.
exception handlers


Function can be used in SQL with some restriction Procedure cannot be called directly from SQL.

Question 6) What is the difference between DELETE and TRUNCATE ?


Delete Truncate
DELETE is a DML command TRUNCATE is a DDL command.

Delete * from table




Truncate table <table name>;

It does not change the high-water mark TRUNCATE re-set the memory blocks after execution i.e. it sets the high-water mark of the object
It can be rollback It cannot be rollback
Delete trigger are fired No triggers will be fired
Rollback segments are used Less rollback segments are used and it is much faster

Question 7) What is Implicit Cursor and Explicit Cursor ?


Implicit Cursor Explicit Cursor
Implicit Cursor is defined and controlled by Oracle Internally. Explicit Cursor is defined and controlled programatically.


v_dept varchar2(50);


select dept into v_dept from dept where location = ‘XYZ’;




v_dept varchar2(50);

Cursor Cur_dept is select dept into v_dept from dept where location = ‘XYZ’;


Open Cur_dept;

Fetch Cur_dept into v_dept;

Close Cur_dept;


All select/update query used in PL/SQL block is an implicit cursor Explicit cursor are open, fetch and closed


Question 8) What is rowid and rownum?


Rowid is a hexa decimal unique number for each row of a table which is used for searching.

Rownum is a unique integer number for each row of a table which can be used for sorting or analysis.

Question 9) Explain the difference between a FUNCTION, PROCEDURE and PACKAGE


Function -A function has a return type in its specification and must return a value specified in that type.
-They are are stored in compiled form in database
– Functions can be called anywhere in an valid expression

Select fnd_profile.value(‘xyz)  from dual; 

Procedure -A procedure does not have a return type in its specification and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller
-They are are stored in compiled form in database
-Procedures are called as stand-alone executable statements:my_proc(parameter1,parameter2…);
Packages -Packages contain function, procedures and other data structures.
– The advantages of packages are Modularity, Easier Application Design, and Information.
Hiding, Reusability and Better Performance.
-There are a number of differences between packaged and non-packaged PL/SQL programs.
Package The data in package is persistent for the duration of the user’s session. The data in package thus exists across commits in the session.
-If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package.-You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.

Question 10)  Define Commit, Rollback and Save-point.


Commit Commit is used to make the transaction permanent
Following things happens when commit is executed
a)All work done by the transaction becomes permanent.
b)Other users can see changes in data made by the transaction.
c)Any locks acquired by the transaction are released.
Rollback Rollback is used to undo the transaction.
Following things happens when rollback is executed
a)All work done by the transaction is undone, as if it hadn’t been issued.
b) Any locks acquired by the transaction are released.
Save point A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.


Question 11) What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?


SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

Question 12) How can you find within a PL/SQL block, if a cursor is open?


Use the %ISOPEN cursor status variable.

Question 13) How can you generate debugging output from PL/SQL?


Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
Question 14) What are PL/SQL exceptions?

Some of them are

4.Zero_error etc.
Question15) What is the maximum number of triggers, you can apply on a single table?


12 triggers.

Question 16) What are two parts of package ?


The two parts are package specification and package body

They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package body contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.
Question 17) What is Overloading of procedures ?


Repeating of same procedure name with different parameter list
Question 18) What are the modes of parameters that can be passed to a procedure ?


IN, OUT, IN-OUT parameters.

Question 19)What command would you use to encrypt a PL/SQL application?


Question 20) How many types of triggers exist in PL/SQL?


There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.

Question 21) Name the tables where characteristics of Package, procedure and functions are stored?

dba_objects, dba_Source and dba_error.


user_objects,user_Source and user_error.
Question 22) 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.
Question 23) How to execute a stored procedure?


There are two way to execute a stored procedure.

From the SQL prompt, write EXECUTE or EXEC followed by procedure_name

Question 24) What is Raise_application_error?


Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

Question 25)  What is Pragma EXECPTION_INIT? Explain the usage?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Hope you like this compilation of Oracle PlSQL interview questions. I will be posting more such question in the future

Hope you like the compilation of oracle plsql interview questions. Please do provide the feedback

Here is the nice course on Udemy for PLSQL, You may want to take it  up

Leave a Reply