PLSQL Complex Interview Questions (Part 2)

1.What is mutating table error?

For a trigger, it is the table on which this trigger is defined. If a trigger tries to read or modify such a table, it causes a mutating table error.

2. What is compound trigger?

It is a solution for Mutating table error. Introduced in 11g

3. What will happen if set serveroutput off?

It will not write o/p to the screen

4. What are labels ?

Lables can be used in PL/SQL block to improve redability.

SET SERVEROUTPUT ON<< find_stu_num >>
BEGIN
DBMS_OUTPUT.PUT_LINE(‘The procedure
find_stu_num has been executed.’);
END find_stu_num;

5. What is nested if?

If within the If is called nested if.

6. Difference between NULLIF and COALESCE ?

NULLIF(exp1, exp2) => if both are equal it writes NULL. Else it writes first 1 & COLAESCE(exp1,exp2…) => It writes first non-null expression

7. ‘REVERSE’ in FOR loop?

It iterates from higher value to lower value

8. What is anonymous block and named block?

Anonymous block is not stored in the server. So we can not re-use it. Named block we can use it later.

9. FOR UPDATE and WHERE CURRENT OF ?

The FOR UPDATE and WHERE CURRENT OF syntax can be used with cursors that are performing a delete as well as an update.

10. How many pkgs oracle is having?

More than 130 pkgs.