Find us on Google+ Google+

October 20, 2012

Oracle PL/SQL Interview Questions/FAQs

Oracle PL/SQL Interview Questions

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

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

3. What is PL/SQL table (now PL/SQL tables are called Associative Arrays)?
PL/SQL table or associative array is an 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?
alter procedure upd_int compile;
alter package SCOTT.LOCA_SER compile;

5. What are differences between if and nullif?

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

7. What are differences between decode and case?

8. What is fragma?

9. Want output in below format, salary in descending order and with serial numbers.
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?
Functions return 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 used 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 schema object which groups logically related PL/SQL types, items and subprograms.

11. What is mutating of a trigger?
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?
Cursor attributes are used to get information about the current status of cursor.

Both explicit and implicit cursors have four attributes:

• FOUND – It Returns TRUE if record was fetched successfully, FALSE otherwise
• NOTFOUND - Returns TRUE if record was not fetched successfully, FALSE otherwise.
• ROWCOUNT - Returns number of records fetched from cursor at that point in time.
• ISOPEN - Returns TRUE if cursor is open, FALSE otherwise.

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