PL SQL Interview Questions

Intermediate Level PL SQL Interview Questions

11. How to write comments in PL/SQL?

Comments help us to understand the logic of the code. There are two ways to write comments in Pl/SQL:

1. Using (--): This is used to write single-line comments.
2. Using (/*....*/): This is used to write multi-line comments.

12. What is a Raise_application_error in PL/SQL?

It's a DBMS STANDARD procedure that lets you send user-defined error messages from a database trigger or stored sub-program.

13. How to assign a name to an unnamed PL/SQL exception block?

DECLARE 
  your_exception_name MINE_EXCEPTION; 
  PRAGMA EXCEPTION_INIT (your_exception_name, error_code); 
BEGIN 
-- Write your PL/SQL Logic
EXCEPTION
 WHEN your_exception_name THEN
    -- Steps to handle exception
END;

14. Differentiate between compile-time errors and run-time errors?

A PL/SQL compiler can readily discover a compile-time error. Take, for example, poor spelling.

An exception-handling section in a PL/SQL block is used to handle a run-time error. Consider the SELECT INTO query, which returns no rows.

15. What are COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?

Commit: Any changes made to the database during the current transaction are made permanent with the COMMIT command. The changes are also available to other users after a commit. See "Overview of Transaction Processing in PL/SQL" for additional information on PL/SQL transaction processing.

Rollback: The flipside of the COMMIT statement is the ROLLBACK statement. It reverses part or all of the modifications made to the database during the current transaction. See "Overview of Transaction Processing in PL/SQL" for further information. In PL/SQL, the SQL ROLLBACK statement can be incorporated as static SQL.

Savepoints: The SAVEPOINT statement identifies and marks the current point in a transaction's processing. Savepoints undo portions of a transaction rather than the entire transaction when using the ROLLBACK TO command. See "Overview of Transaction Processing in PL/SQL" for further information.

16. What is a mutating table error?

This error occurs when a trigger tries to update a row that it is currently utilizing. Views or temporary tables are used to fix it, and the database selects one and changes the other.

17. What is the use of SYSDATE and USER?

SYSDATE: The current time and date on the local database server are returned by this keyword. SYSDATE is the syntax. We utilize the TO_CHAR function on SYSDATE and specify the format we require to extract the portion of the date.

USER: This keyword is used to return the user id of the current session?

18. How can we manipulate the character data using the functions?

The functions that we use to manipulate the character data.

Function	Description
LEFT	It returns the specified number of characters from the left of the string.
RIGHT	It returns the specified number of characters from the right of the string.
SUBSTRING	This function would choose data from any section of the string starting at a specified start point and continuing for the specified amount of characters.
LTRIM	It removes all the whitespace from the left part of the string.
RTRIM	It removes all the whitespace from the right part of the string.
UPPER	It is used to convert all characters to the upper case of the string.
LOWER	It is used to convert all characters to the lower case of the string.

19. Why do we use SYS.ALL_DEPENDENCIES?

All dependencies between procedures, packages, triggers, and functions that are accessible to the current user are described in SYS.ALL DEPENDENCIES. Name, dependency type, type, referenced owner, and other columns are returned.

20. What is the difference between the cursor in the procedure and the cursor in the package specification?

Cursors defined in procedures will have a local scope, which means other procedures can't use them. Cursors stated in package specifications, however, are given the global scope and can thus be utilized and accessed by other methods.

Also see, Power Electronics Interview Questions

Advanced Level PL SQL Interview Questions

21. Explain the concept of bulk processing in PL/SQL.

Bulk processing in PL/SQL involves handling multiple rows of data in a single operation, unlike traditional row-by-row processing. It leverages collections such as PL/SQL tables or arrays to process data efficiently.

22. How %TYPE and %ROWTYPE are different?

%TYPE: The attribute that declares a variable with the same data type as a column in a table.

Example:

DECLARE
departmentId
departments.department_id%TYPE;

%ROWTYPE: This attribute declares a RECORD variable with the same structure as a table row. The RECORD is the row that contains fields with the same data types and names as the table or view's columns.

Example:

DECLARE
department_rec
departments.%ROWTYPE;

23. What does a typical PL/SQL package contain?

Packages are schema objects that condense functions, processes, variables, and other items into a single location. Data that should be included in all packages.

• Specifications for the package
• Body of the package

24. What are records in Pl/SQL?

Records are collections of data of various types that can be linked together as fields. Table-based records, programmer-based records, and cursor-based records are the three types of records provided by PL/SQL.

25. What is the procedure in PL/SQL?

A procedure is a collection of PL/SQL statements that their names can call. A call specification (also known as a call spec) specifies how to call a Java method or a third-generation language (3GL) procedure from SQL and PL/SQL. When a call is made, the call spec instructs Oracle Database, which Java method to call.

26. What is the function in Pl/SQL?

A PL/SQL function is a reusable software unit saved as a schema object in the Oracle Database, similar to a procedure. The function name and a RETURN clause specify the datatype of the returned value in the function header. Each function argument can be in three modes: IN, OUT, or INOUT. The function body is similar to the procedure body, divided into three sections: declarative, executable, and exception-handling.

27. How are exceptions handled in PL SQL?

For handling exceptions in PL SQL there are EXCEPTION blocks. It consists of code that executes when there is an exception. We can define our own exceptions or use the pre-defined ones. The custom exceptions can be defined using the RAISE statement.

If you want to associate an exception with a named error code, then EXCEPTION INIT pragma is used.

28. What are triggers in PL/ SQL, and how can we use them to perform complex tasks?

The blocks of PL SQL that are executed automatically when a particular event occurs in the database are known as triggers. For example, when the user performs the insert, update, or delete operation, that triggers an event. They maintain data integrity. 

Therefore triggers are event-driven. You can use them for automating the database actions. With triggers, we can invoke stored procedures/ functions, this enables us to perform more complex tasks.

29. How you can debug a PL SQL code?

We can use DBMS_OUTPUT.PUT_LINE procedure for debugging. It shows us the debug information in runtime. Some IDEs provide the facility of in-built debugging tools for PL SQL code. We can also use logging frameworks or write down our own logging statements to identify the issues. The DBMS_DEBUG package can also be used if advanced debugging is needed.

30. What are autonomous transactions in PL SQL?

The independent transactions in a vast transaction are known as autonomous transactions. They are useful when there is a requirement for logging or changes in the track that needs to be made without the result of the main transition being affected. This helps to main data consistency.

You can use it for performing operations that are separated from the main transactions. They must be rolled back independently or committed. 

PL/SQL Programming Questions

31. How do you declare a variable in PL/SQL?

In PL/SQL, variables are declared using the syntax:

DECLARE
  variable_name datatype;

 

Example:

DECLARE
  emp_name VARCHAR2(50);

32. How do you create a trigger in PL/SQL?

To create a trigger:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic
END;

33. Explain the basic structure of a PL/SQL block.

A PL/SQL block consists of three sections:

1. DECLARE (optional): Where variables, cursors, and user-defined types are declared.
2. BEGIN (mandatory): Contains the executable statements.
3. EXCEPTION (optional): Handles exceptions that may occur during execution.

Example:

DECLARE
 v_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO v_count FROM employees;
 DBMS_OUTPUT.PUT_LINE('Number of employees: ' || v_count);
EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

34. What is a stored procedure in PL/SQL and how do you create one?

A stored procedure is a named PL/SQL block that performs a specific task. It's compiled and stored in the database for repeated execution.

To create a stored procedure:

CREATE OR REPLACE PROCEDURE update_salary(p_empno IN NUMBER, p_percent IN NUMBER) IS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + p_percent/100)
  WHERE employee_id = p_empno;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END update_salary;

35. Write a PL/SQL program to calculate the factorial of a number using a loop.

DECLARE
  num NUMBER := 5;
  fact NUMBER := 1;
BEGIN
  FOR i IN REVERSE 1..num LOOP
    fact := fact * i;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is: ' || fact);
END;

36. Create a stored procedure that takes an employee ID as input and gives them a 10% raise.

CREATE OR REPLACE PROCEDURE give_raise(p_emp_id IN NUMBER) IS
BEGIN
 UPDATE employees
 SET salary = salary * 1.1
 WHERE employee_id = p_emp_id;
 
 IF SQL%ROWCOUNT = 0 THEN
   RAISE_APPLICATION_ERROR(-20001, 'No employee found with ID ' || p_emp_id);
 ELSE
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Raise given successfully to employee ' || p_emp_id);
 END IF;
EXCEPTION
 WHEN OTHERS THEN
   ROLLBACK;
   DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END give_raise;

37. Write a PL/SQL program to reverse a string.

DECLARE
  str VARCHAR2(100) := 'PLSQL';
  rev_str VARCHAR2(100) := '';
BEGIN
  FOR i IN REVERSE 1..LENGTH(str) LOOP
    rev_str := rev_str || SUBSTR(str, i, 1);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Reversed String: ' || rev_str);
END;

38. Write a function that returns the nth number in the Fibonacci sequence.

CREATE OR REPLACE FUNCTION get_fibonacci(p_n IN NUMBER) RETURN NUMBER IS
  v_fib1 NUMBER := 0;
  v_fib2 NUMBER := 1;
  v_fib_n NUMBER;
BEGIN
  IF p_n <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Input must be a positive integer');
  ELSIF p_n = 1 THEN
    RETURN v_fib1;
  ELSIF p_n = 2 THEN
    RETURN v_fib2;
  ELSE
    FOR i IN 3..p_n LOOP
      v_fib_n := v_fib1 + v_fib2;
      v_fib1 := v_fib2;
      v_fib2 := v_fib_n;
    END LOOP;
    RETURN v_fib_n;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RETURN NULL;
END get_fibonacci;

39. Write a PL/SQL program to find the sum of even and odd numbers from a given table.

DECLARE
  sum_even NUMBER := 0;
  sum_odd NUMBER := 0;
BEGIN
  FOR rec IN (SELECT num_value FROM numbers_tbl) LOOP
    IF MOD(rec.num_value, 2) = 0 THEN
      sum_even := sum_even + rec.num_value;
    ELSE
      sum_odd := sum_odd + rec.num_value;
    END IF;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Sum of Even Numbers: ' || sum_even);
  DBMS_OUTPUT.PUT_LINE('Sum of Odd Numbers: ' || sum_odd);
END;

40. Write a PL/SQL block that uses a cursor to calculate and print the average salary for each department.

DECLARE
  CURSOR dept_cursor IS
    SELECT department_id, department_name
    FROM departments;
    
  v_avg_salary NUMBER;
BEGIN
  FOR dept_rec IN dept_cursor LOOP
    SELECT AVG(salary)
    INTO v_avg_salary
    FROM employees
    WHERE department_id = dept_rec.department_id;
    
    DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name || 
                         ', Average Salary: ' || TO_CHAR(v_avg_salary, '$999,999.99'));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

PL/SQL Interview MCQs

1. Which of the following is a correct PL/SQL block structure?

A) DECLARE, BEGIN, END

B) BEGIN, EXCEPTION, END

C) BEGIN, DECLARE, END

D) DECLARE, EXCEPTION, END

Answer: A) DECLARE, BEGIN, END

2. What is the default mode of parameters in PL/SQL?

A) IN

B) OUT

C) IN OUT

D) NONE

Answer: A) IN

3. Which of the following is used to store the entire row of a table?

A) %TYPE

B) %ROWTYPE

C) ROWID

D) TABLE

Answer: B) %ROWTYPE

4. What happens when you issue a COMMIT in PL/SQL?

A) Saves the transaction and ends the session

B) Saves the transaction permanently

C) Rolls back the transaction

D) Deletes the transaction

Answer: B) Saves the transaction permanently

5. Which of the following is used to declare a cursor in PL/SQL?

A) CURSOR … IS

B) DECLARE CURSOR

C) OPEN CURSOR

D) SET CURSOR

Answer: A) CURSOR … IS

6. What is the purpose of EXCEPTION_INIT in PL/SQL?

A) To handle predefined exceptions

B) To associate an exception with an error code

C) To declare user-defined exceptions

D) To exit the exception block

Answer: B) To associate an exception with an error code

7. Which PL/SQL collection type stores key-value pairs?

A) Nested Table

B) VARRAY

C) Associative Array

D) RECORD

Answer: C) Associative Array

8. How do you raise a user-defined exception in PL/SQL?

A) RAISE_APPLICATION_ERROR

B) EXCEPTION RAISE

C) RAISE

D) THROW

Answer: C) RAISE

9. What is the maximum size of VARCHAR2 in PL/SQL?

A) 1000

B) 2000

C) 4000

D) 8000

Answer: C) 4000

10. What is the purpose of SAVEPOINT in PL/SQL?

A) To commit a transaction

B) To rollback to a point in a transaction

C) To restart the transaction

D) To define the end of a block

Answer: B) To rollback to a point in a transaction

Frequently Asked Questions

How to prepare for a PL SQL interview?

If one wants to prepare for a PL SQL interview, then one must clear all the basic concepts.  Further, you must refer to the questions which are mostly asked in the interview and prepare accordingly. Reading interview experiences also helps a lot.

What is PL SQL basic concept? 

Features of PL SQL and its working, basic syntax, commands, identifiers,  data types, variables, constraints, literals, arrays, functions, cursors, records, and triggers, are some of the basic topics of PL SQL that one must complete thoroughly before moving onto advanced topics.

What are commands in PL SQ?

Using commands in PL SQL, we can execute certain actions in a program. Some of the common commands are DECLARE,  LOOP, BEGIN, END, IF_THEN_ELSE, CASE, COMMIT, EXIT, etc. One must have a complete understanding of all the basic commands in PL SQL.

What are the basic elements of PL SQL? 

The basic elements of PL/SQL include variables, loops and conditional statements, exception handling, cursors for database interaction, and procedures/functions forming its fundamental components for organized and reusable code.

Conclusion

In this article, we have extensively discussed the PL SQL Interview Questions. PL/SQL is a powerful procedural extension of SQL used for writing complex queries, performing database manipulation, and controlling data processing flow. Mastering PL/SQL requires a solid understanding of various concepts, such as cursors, triggers, packages, and exception handling.

You can also check out our other content on the Coding Ninjas Website- 

• DBMS Interview Questions
• DB2 interview questions
• Selenium Interview Questions
• Javascript interview questions
•  SAP FICO Interview Questions 
   

Do upvote our blog to help other ninjas grow. Happy Coding!