PL SQL Interview Questions with Answers

Friends,

This is my first technical blog and so I thought of publishing my personal compilation of PL/SQL Interview qs  and answers, during those days of me interviewing as an Experienced Oracle PL/SQL developer. 
Though most of these would be already available, I hope this is useful to anyone who needs that last minute brush up.

Feel free to post your comments on - what you liked and any suggestions to improve. This will encourage me to keep going  :) 

All the best !

1. What is the difference between a procedure and a function? When to use a function vs procedure? ( I was surprised to find this qs being repeated in so many interviews for experienced ) 

        Procedure : 

1.           is a named PL/SQL block for performing 1 or more specific task.
2.          may or may not return value. 
3.          can have multiple OUT parameters for returning values.
4.          can be used only in PL/SQL
5.          DML can be used inside procedures

            Function :

1. is a named PL/SQL subprogram. A function's main purpose is to perform a computation based on a given set of logical conditions.
2. A function must always return a value to it's calling program
3. A function can have only a single OUT parameter. But it can return more than 1 value using a return parameter and OUT parameter.
4. Functions can be used in SQL and PL/SQL.
5. DML can be used, but such a function cannot be called from within a SQL query.
6. Finally, a function can be called from a procedure but not vice versa. 

2. Do functions have any restrictions? If yes, explain. 

     Yes. 

1. A function must always have a return type, whether or not it returns a value.
2. DML can be used, but such a function cannot be called from within a SQL query.
3. No commits are allowed within a function ,for this reason DDL statements are not allowed within functions;  since DDLs are auto-commit.
4. A function cannot be used with Pl/sql datatypes such as BOOLEAN, BINARY_INTEGER,associative arrays, PL/SQL records etc.

3. Explain SET operators in Oracle. What's the difference between Union and Union All ? 

 You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS.

UNION : combines the results of queries and eliminates duplicate selected rows.

The following statement combines This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

LOCATION_ID Department            Warehouse
----------- --------------------- --------------------------
       1400 IT
       1400                       Southlake, Texas
       1500 Shipping
       1500                       San Francisco
       1600                       New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction

 UNION returns only distinct rows that appear in either result, while UNION ALL returns all rows.

INTERSECT : combines the results of queries and returns only the rows returned by both queries:

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;

MINUS : returns only unique rows returned by the first query but not by the second:

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

4. Have you used Bulk collections? Explain it's use. how are exceptions handled ?

To reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.
Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.

 can be used with all three types of collections: associative arrays, nested tables, and VARRAYs.

 Limitation : bulk bind cannot use table of composite types. It works fine with a table of native types, but not with a table of objects or records

 Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values.
 Whenever you execute a DML statement inside of a loop, you should convert that code to use FORALL.

     Each FORALL statement may contain just a single DML statement. If your loop contains two updates and a delete, then you will need to write three FORALL statements.

     PL/SQL declares the FORALL iterator indx as an integer, just as it does with a FOR loop.
    
     FORALL does not require a LOOP command.

     In at least one place in the DML statement, you need to reference a collection and use the FORALL iterator as the index value in that collection

     When using the IN low_value . . . high_value syntax in the FORALL header, the collections referenced inside the FORALL statement must be densely filled. That is, every index value between the low_value and high_value must be defined.

     If your collection is not densely filled, you should use the INDICES OF or VALUES OF syntax in your FORALL header.
    
     EXCEPTIONS IN FORALL :
    
     Suppose that I’ve written a program that is supposed to insert 10,000 rows into a table. After inserting 9,000 of those rows, the 9,001st insert fails with a DUP_VAL_ON_INDEX error (a unique index violation).
     The SQL engine passes that error back to the PL/SQL engine, and if the FORALL statement is written like the one in Listing 4, PL/SQL will terminate the FORALL statement. The remaining 999 rows will not be inserted.
    
If you want the PL/SQL engine to execute as many of the DML statements as possible, even if errors are raised along the way, add the SAVE EXCEPTIONS clause to the FORALL header.
Then, if the SQL engine raises an error, the PL/SQL engine will save that information in a pseudocollection named SQL%BULK_EXCEPTIONS, and continue executing statements.
When all statements have been attempted, PL/SQL then raises the ORA-24381 error.

BEGIN
   FORALL indx IN 1 .. l_eligible_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees emp
         SET emp.salary =
                emp.salary + emp.salary * increase_pct_in
       WHERE emp.employee_id = l_eligible_ids (indx);
EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE = -24381
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
               || ‘: ‘
               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
         END LOOP;
      ELSE
         RAISE;
      END IF;
END increase_salary;

5. How to do Bulk Insert? 

---- Using BULK Collect or INSERT ALL. 

Bulk Collect : 

DECLARE
 CURSOR s_cur IS
 SELECT *
 FROM servers;

 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;

    FORALL i IN 1..s_array.COUNT
    INSERT INTO servers2 VALUES s_array(i);

    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
/

6. Can you insert into Multiple Tables ? 

Using INSERT ALL. 

-- Unconditional insert into ALL tables
INSERT ALL
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sysdate) 
SELECT employee_id AS empid, 
       hire_date AS hiredate,
       salary AS sal,
       manager_id AS mgr
FROM   employees
WHERE  employee_id > 200;

INSERT FIRST : 

-- Insert into the FIRST table with a matching condition
INSERT FIRST
  WHEN sal > 25000  THEN
    INTO special_sal VALUES(deptid,sal)
  WHEN hiredate LIKE ('%') THEN
    INTO hiredate_history_00 VALUES(deptid,hiredate)
  WHEN hiredate LIKE ('%99%') THEN 
    INTO hiredate_history_99 VALUES(deptid,hiredate)
  ELSE
    INTO hiredate_history_not_99 VALUES(deptid, hiredate)
SELECT department_id AS deptid,
       SUM(salary) AS sal,
       MAX(hire_date) AS hiredate
FROM   employees
GROUP BY department_id;

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.

The restrictions on multitable insertss are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.( remote)
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.

7. Have you used LIMIT in BULK COLLECT ? Why?

If you are fetching lots of rows, consume too much session memory and raise an error. To help you avoid such errors, Oracle Database offers a LIMIT clause for BULK COLLECT

Fetching up to the number of rows specified

DECLARE
   c_limit PLS_INTEGER := 100;

   CURSOR employees_cur
   IS
      SELECT employee_id
        FROM employees
       WHERE department_id = department_id_in;

   TYPE employee_ids_t IS TABLE OF
      employees.employee_id%TYPE;

   l_employee_ids   employee_ids_t;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH employees_cur
      BULK COLLECT INTO l_employee_ids
      LIMIT c_limit;

      EXIT WHEN l_employee_ids.COUNT = 0;
   END LOOP;
END;


 inside a loop, I use FETCH-BULK COLLECT-INTO to <collection>
------- fetch up to the number of rows specified by the LIMIT <limit>
 Now, no matter how many rows I need to fetch, my session will never consume more memory than that required for those 100 rows.