PLSQL complex interview Questions – part 2

PL/SQL Comments Off on PLSQL complex interview Questions – part 2

The PL/SQL also called procedural language SQL interview questions. These are very useful to prepare for interviews and also you can use to conduct interviews.

  1. What is mutating table error?
    1. 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?
    1. It is a solution for Mutating table error. Introduced in 11g
  3. What will happen if set serveroutput off?
    1. It will not write o/p to the screen
  4. What are labels ?
    1. Lables can be used in PL/SQL block to improve redability.
    2. 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?
    1. If within the If is called nested if.
  6. Difference between NULLIF and COALESCE ?
    1. 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?
    1. It iterates from higher value to lower value
  8. What is anonymous block and named block?
    1. 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 ?
    1. 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?
    1. More than 130 pkgs.

Related articles