Find us on Google+ Google+

October 20, 2018

Oracle PL/SQL Interview Questions / FAQs

Oracle PLSQL Interview Questions


1. What is the use of package/function/procedure?
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.
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 use in SQL query. 

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 pragma?
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 compiler directive in oracle.
Autonomous_Transaction is creating new or independent session in the same transaction.
Autonomous Transaction provides a 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.
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 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 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.

13. What are the different types of triggers?

14. Can triggers contain commit?
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 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 trigger?
Yes, you can fire a procedure/function from a Trigger.
But, keep in mind that trigger & procedure/function should not acess the same table.

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

14 comments:

  1. Hello,
    very impressive and appreciate for sharing with us.

    ReplyDelete
  2. nice blog thanks for sharing such a good article for more details pleas visit our site
    Oracle Online Training in Deira, Dubai

    ReplyDelete
  3. Love this!!! Please make more!!!:)

    ReplyDelete

  4. Thank you for sharing such great information very useful to us.
    .Net Training in Noida

    ReplyDelete
  5. This is very great thinks. It was very comprehensive post and powerful concept. Thanks for your sharing with us. Keep it up..
    Oracle Training in Chennai | Oracle Training Institutes in Chennai

    ReplyDelete