Oracle PL/SQL Interview Questions 2022

This is the 2nd set of the Oracle PL/SQL interview questions series.

Oracle PL/SQL is an advanced version of SQL. There are given a top list of PL/SQL interview questions with answers. You can also visit our PL/SQL Tutorial.

1. What is a Package?

A PL/SQL package is a named collection of procedures, functions, variables, cursors, user-defined types, and records that are referenced using a common qualifier, the package name.

2. What are the advantages of the Package?

• Modularity
• Easy Application Design
• Information Hiding
• Better Performance
• Overloading and other Added Functionality

3. What is an Exception in PL/SQL?

An exception is an error that occurs during program run time. PL/SQL supports programmers to catch such conditions using the EXCEPTION block in the program and suitable action is taken against the error condition. There are 2 types of exceptions −

• Pre-defined exceptions: PL/SQL introduces many pre-defined exceptions, that are performed when any database rule is violated by any program. For instance, the predefined exception NO_DATA_FOUND is found when a SELECT INTO statement returns no rows. 
• User-defined exceptions: PL/SQL allows us to define our own exceptions according to the need of the program. A user-defined exception must be declared within the program and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

Syntax: 
DECLARE 
   my-exception EXCEPTION; 

4. What is Cursor and its Syntax?

A cursor is a pointer that points to the result set of an SQL query against database tables in the PL/SQL Program.

• It is used to process the individual rows returned by the database.
• It holds the rows returned by the SQL statement.
• The cursor is defined as a work area where a SQL statements is executed.

Cursor cursor_name is sql_select_statements;

There are two types of Cursor-

1. Implicit Cursor: It is a predefined cursor associated with a DML statement.
2. Explicit Cursor: It is defined by the user and it is associated with a SELECT statement.

5. How can you use Explicit Cursor in a PL/SQL Program?

To use an Explicit cursor we have to follow 4 steps:

• DECLARE the cursor in the Declaration block.
• OPEN it in the Execution block.
• FETCH the data from the cursor into variables or records in the Execution block.
• CLOSE the cursor in the Execution block before you end the PL/SQL Block.

Syntax:

DECLARE  
   v_id customers.id%type;  
   v_name customers.name%type;  
   v_addr customers.address%type;  
   CURSOR v_customers is  
      SELECT id, name, address into custid, custname, custadd FROM customers;  
BEGIN  
   OPEN v_customers;  
   LOOP  
      FETCH v_customers into v_id, v_name, v_addr;  
      EXIT WHEN v_customers%notfound;  
      dbms_output.put_line(v_id || ' ' || v_name || ' ' || v_addr);  
   END LOOP;  
   CLOSE v_customers;  
END;  

6. What is the difference between Delete and Truncate in SQL?

Delete is DML Command and is used when we specify the rows that we want to remove or delete from the table. The DELETE command can contain a WHERE condition. If the WHERE condition is used with the DELETE statement then it will remove only those rows which satisfy the condition else by default it will remove all the rows from the table. Syntax: Delete from Table_name where condition;

 On the other hand, Truncate is DDL Command and is used to delete all the rows from a table. Unlike the DELETE command, the TRUNCATE command does not hold a WHERE condition. Syntax: TRUNCATE TABLE Table_Name;

7. What is the use of Merge in SQL explain?

The MERGE statement provides a suitable way to perform all three INSERT, UPDATE, and DELETE operations together which can be very helpful when it comes to handling the big running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statements are required a source table to perform these operations on the target table.

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE_statement;

8. What is the use of commit, rollback and savepoint, and SET TRANSACTION in SQL?

• COMMIT − It is used to save the changes after doing any insert, delete, update, or alter statement.
• ROLLBACK − to Rollback the changes after doing any insert, delete, update, or alter state.
• SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
• SET TRANSACTION − used to place a name on a transaction.

9. What is NVL explain their use?

In SQL, NVL() converts a null value to an actual value. Data types that can be used are date, character and number.

Syntax:

NVL (expr1, expr2)

10. How do Union and Union all work in SQL?
UNION operator is used to combine the result-set of two or more SELECT statements. but it has some limitations:

• Within UNION every SELECT the statement must have the same number of columns
• The columns must also have similar data types and must also be in the same order
•  UNION selects the only distinct values by default.

UNION ALL is used to allow duplicate values. The only difference is that it does not remove any identical rows and columns from the output of the SELECT statement.