Frequently asked Oracle PL/SQL Interview Questions

Oracle PLSQL Interview Questions/answers

1. What is the use of package/function/procedure?

Answer:

A Procedure that performs an action and a function that computes a value.

Package is a collection of related variables, cursor, procedure and functions. A package is a schema object which groups logically related PL/SQL types, items and subprograms.
Functions return a single value, procedures are used to return multiple values, otherwise, it is generally similar to a function. 
Functions can be used in SQL query but procedure we can't use in SQL query. 

2. What is the use of FORALL, BULK COLLECT in PL/SQL?
Answer:

3. What is PL/SQL table (now PL/SQL tables are called Associative Arrays)?

Answer:
PL/SQL table or associative array is a one-dimensional array.
An associative array is an unbounded set of key-value pairs. Each key is unique, and serves as the subscript of the element that holds the corresponding value.

4. How to compile procedure/package?

Answer:
alter procedure upd_int compile;
alter package SCOTT.LOCA_SER compile;

5. What are the differences between if and nullif?
Answer:

6. What is the maximum number of triggers, can apply to a single table?

Answer:
12 triggers

7. What are the differences between decode and case?
Answer:

8. What is pragma?

Answer:
Triggers can run under PRAGMA AUTONOMOUS_TRANSACTION, in which case they must have a COMMIT. But this is an edge case, as there are few meaningful uses of nested transactions in Oracle.
Pragma is a compiler directive in oracle.
Autonomous_Transaction is creating a new or independent session in the same transaction.
Autonomous Transaction provides functionality to the developer in which it allows to do changes in a separate transaction and to save/discard that particular transaction without affecting the main session transaction.

9. Want output in below format, salary in descending order and with serial numbers.

Answer:
Empno empname   Sal S.No
123     Satya      12000 1
345     Rajesh     10000 2
796     Prasad      9000 3
986     Bhanu       8000 4

select t1.*, rownum from (select empno, empname, sal from emp order by sal desc)t1 order by rownum;

10. What is the difference between function and procedure?

Answer:

Functions return a single value, procedures are used to return multiple values, otherwise, it is generally similar to a function. 
Functions can be used in SQL query but procedure we can't use in SQL query. 
A Procedure that performs an action and a function that computes a value.

Package is a collection of related variables, cursor, procedure and functions. A package is a schema object which groups logically related PL/SQL types, items and subprograms.

11. What is mutating of a trigger?

Answer:
A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired.

12. What are cursor attributes?

Answer:
Cursor attributes are used to get information about the current status of the cursor.

Both explicit and implicit cursors have four attributes:
• FOUND – It Returns TRUE if the record was fetched successfully, FALSE otherwise
• NOTFOUND - Returns TRUE if the record was not fetched successfully, FALSE otherwise.
• ROWCOUNT - Returns the number of records fetched from the cursor at that point in time.
• ISOPEN - Returns TRUE if the cursor is open, FALSE otherwise.

13. What are the different types of triggers?
Answer:

14. Can trigger contain commit?

Answer:
No, regular triggers can not contain commit. 
Any change that a trigger does is committed with the transaction that fired the trigger.
You will get the below error.
ORA-04092: cannot COMMIT in a trigger

Autonomous triggers (PRAGMA AUTONOMOUS_TRANSACTION) can contain COMMIT and ROLLBACK.

15. Can you call a procedure/function inside a trigger?

Answer:
Yes, you can fire a procedure/function from a Trigger.
But, keep in mind that trigger & procedure/function should not access the same table.

Related Oracle Articles: Oracle Exadata Interview Questions   Oracle Data Guard Interview Questions/FAQs  Oracle Export/Import - Data Pump Interview Questions/FAQs