The PL/SQL complex Questions for interviews

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.

First operating system

  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.
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.