PL/SQL is a procedural language that incorporates SQL statements into its syntax. The Oracle Database server compiles and stores PL/SQL program units within the database. Both PL/SQL and SQL at runtime operate within the same server process, providing the highest functionality. Therefore, PL/SQL inherits the robustness, security, and portability of the Oracle Database.

PL/SQL interview coming up? If yes, then I have prepared for you a list of PL/SQL interview questions along with their reasonable answers.

PL/SQL Interview Questions For Beginners & Experts

1. What is PL/SQL?

Answer: PL/SQL refers to Procedural Language extensions to SQL (Structured Query Language). Developed by Oracle, it aims to overcome the disadvantages of SQL for easier development and handling of critical applications in a general manner.

2. Define cursor and its use.

Answer: By definition, a cursor is a pointer to a memory area allocated by Oracle to process SQL statements. The cursor holds records returned by the SQL query. Cursors are of two types - implicit and explicit.

3. What do you understand by PL/SQL table?

Answer: PL/SQL tables are nothing but objects of type tables modeled as database tables. For faster processing, they provide arrays that are nothing but temporary tables in memory. Using these tables simplifies the process of moving bulk data.

4. What is the basic structure of PL/SQL?

Answer: Here is the basic structure of PL/SQL:

[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements
END;

5. What are the features of PL/SQL?

Answer: PL/SQL offers the following features:

• Applications developed using PL/SQL are portable across computer hardware or operating systems with an Oracle engine.
• Applications can reuse PL/SQL code because it can be grouped and stored in databases as PL/SQL units such as functions, procedures, packages, triggers, and types.
• Besides exception handling, PL/SQL also supports error checking and data validation before data manipulation.
• By utilizing its procedural nature, PL/SQL allows decision making, looping, and branching.
• PL/SQL supports exception handling by making use of an exception handling block.
• You can process multiple queries in one block by using a single command using PL/SQL.

6. Explain the basic structure followed in PL/SQL?

Answer: The basic structure of PL/SQL follows the BLOCK structure. Each PL/SQL code contains SQL and PL/SQL statement that forms a PL/SQL block. Each PL/SQL Block consists of three sections:

1. The optional Declaration Section
2. The mandatory Execution Section
3. The optional Exception handling Section

7. What is the use of WHERE CURRENT OF in cursors?

Answer: We use this clause when referencing the current row from an explicit cursor. With this clause, we can update and delete the current row without explicitly specifying the row ID.

Syntax: UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name

8. Why do we use database triggers? Give the syntax of a trigger.

Answer: Database Trigger is a stored procedure that is automatically invoked when an event happens. The event may include: insert, update, delete, etc.

Syntax:

create trigger [trigger_name]
[before | after]

on [table_name]
[for each row]
[trigger_body]

9. Tell about a few data types in PL/SQL.

Answer: There are several data types in PL/SQL:

• Composite – made up of other data types that are updateable easily.
• Reference data types like CURSOR.
• Large object types – BLOB, CLOB, etc.
• Scalar types – primitive data types like CHAR, DATE, LONG, VARCHAR2, etc. Example, RECORD, TABLE, etc.

10. Explain exception handling in PL/SQL.

Answer: PL/SQL provides customized exception handling. An error-handling code is built into the program when a problem occurs. There are three types of exceptions:

1. Pre-defined exceptions – common errors that are already defined. Example – NO_DATA_FOUND
2. Undefined exceptions – errors without predefined names.
3. User-defined exceptions – handled by the code created by the user.

11. How do you compile PL/SQL code?

Answer: The first step is to check the syntax. If the developer corrects any syntax errors, Oracle binds all variables with data with a storage address. Finally, we generate the p-code

12. What is the importance of %TYPE and %ROWTYPE data types in PL/SQL?

Answer:

• %TYPE: This declaration provides data types for variables, columns, and constants, which are used for anchoring. When a variable has the same type as the column of a table, it is helpful to declare that data type when declaring the variable.

• Consider the example of declaring a variable named ib_employeeid, which has the same data type and size as the column employeeid in the table ib_employee.

The syntax would be ib_employeeid ib_employee.employeeid%TYPE;

• %ROWTYPE: It is used to declare a variable with the same data type and size as a row. Table rows are called records, and their fields would have the same data types and names as the columns defined in the table.

• For example, the syntax to declare a record named ib_emprecord for storing an entire row in a table called ib_employee is: ib_emprecord ib_employee%ROWTYPE;

13. List some schema objects that are created using PL/SQL.

Answer: Some schema objects that we can create using PL/SQL are Database links, triggers, stored procedures, functions and packages, external procedure libraries, views, synonyms, sequences, etc.

14. What constitutes a PL/SQL package?

Answer: Packages are schema objects that place variables, procedures, functions, etc., in one place. Packages should include –

• Package specifications
• Package body

15. Explain the difference between ROLLBACK and ROLLBACK TO statements?

Answer: ROLLBACK rolls back all the changes from the beginning of the transaction. ROLLBACK TO allows a transaction to be rolled back (or undone) only until a point known as the SAVEPOINT. There is no way to undo the transactions before the SAVEPOINT, and the transaction remains active even after the command is given.

16. What are the various predefined exceptions?

Answer: Predefined exceptions are built-in exceptions thrown during the execution of a program. For example, PL/SQL raises NO_DATA_FOUND when no rows are returned during a select operation, and if more than one row is returned, the TOO_MANY_ROWS error occurs. Here are some more examples:

• COLLECTION_IS_NULL: When a collection is null
• LOGIN_DENIED: Incorrect login or permission denied.
• CURSOR_ALREADY_OPEN: When a cursor is already open

17. What are the different types of constraints?

Answer: The different types of constraints are:

• Check
• Foreign key
• Not NULL
• Primary key
• Unique

18. Explain the difference between commit and savepoint.

Answer: COMMIT is used to make database changes permanent. The transaction ends with all the save points erased. A transaction cannot be rolled back once it has taken place.

During a transaction, SAVEPOINT is used to set a point to which a programmer can roll back. It is useful when several transactions can be divided into groups with a savepoint.

19. Explain PL/SQL Records.

Answer: A record is a collection of data of various data types linked together as fields. PL/SQL supports three types of records: table-based records, program-based records, and cursor-based records.

20. What are the different types of cursors in PL/SQL?

Answer: Cursors fall into two categories:

Implicit Cursor:

• Oracle creates a cursor when any of the commands SELECT INTO, INSERT, DELETE, or UPDATE are executed implicitly.
• Oracle's internal execution cycle handles these cursors and returns the information and status of the cursor using the cursor attributes ROWCOUNT, ISOPEN, FOUND, NOTFOUND.

Explicit Cursor:

• The cursor is a SELECT statement that was specified explicitly in the declaration block.
• Programmers must control the execution cycle of these cursors from OPEN through FETCH and close.
• Oracle defines the execution cycle while executing the SQL statement together with associating a cursor with it.

21. Explain the PL/SQL block with an example.

Answer: PL/SQL Block consists of three sections: declaration, executable and exception-handling sections. It is mandatory to include the executable section. Blocks come in two types: named and anonymous.

Named blocks are functions and procedures that are stored in the database server and can be reused. Anonymous blocks are intended for one-time use and are not stored on the server. Example:

DECLARE

 message VARCHAR2(255):= 'Welcome to PL/SQL';
 byzero NUMBER;

BEGIN

   DBMS_OUTPUT.put_line (message);
   byzero := 1/0;

   EXCEPTION

  WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

22. Explain the uses of Merge with Syntax in PL-SQL.

Answer: Merge reduces the number of table scans and performs parallel operations if necessary. MERGE inserts or updates data from one table to another conditionally. As an example,

MERGE INTO orders o
 USING customer c
 ON (o.cust_id = c.cust_id)
 WHEN MATCHED THEN
 UPDATE SET o.del_address = c.address
 WHEN NOT MATCHED THEN
 INSERT (cust_id, address)
VALUES (c.emp_id, c.address);

In the above example, if a record matching the condition is found, the address of the same record is updated or, a new row is inserted.

23. How do you write comments in a PL/SQL code?

Answer: Comments are those sentences that do not affect the functionality and are used to enhance the readability of the code. The two types of comments are:

1. Single Line Comment: We can create this by using the symbol -- and writing our comment next to it.
2. Multi-Line comment: These comments can be specified over multiple lines, and the syntax goes like /* comment information */

24. Why are SYSDATE and USER keywords used?

Answer: Here are their uses:

SYSDATE:

• This keyword returns the current date and time on the local database server.
• Syntax: SYSDATE
• To extract part of the date, we use the TO_CHAR function on SYSDATE and specify the format we need.

Usage:

• SELECT SYSDATE FROM dual;
• SELECT id, TO_CHAR(SYSDATE, 'yyyy/mm/dd) from CoursesityEmployeeTable where customer_id < 200;

USER:

• This keyword returns the current session's user id.

Usage: SELECT USER FROM dual;

25. What is the use of SYS.ALL_DEPENDENCIES?

Answer: SYS.ALL_DEPENDENCIES describes all the dependencies between procedures, packages, triggers, and functions accessible to the current user. Among the columns returned are name, dependency_type, type, and referenced_owner.

26. Differentiate between the cursors declared in procedures and the cursors declared in the package specifications.

Answer: These two key points will highlight the difference between both:

• Cursors declared in procedures will have a local scope, so they are not usable in other procedures.
• Cursors declared within package specifications have a global scope, so they can be used and accessed by other procedures, too.

27. What is the difference between syntax and runtime errors?

Answer:

28. What is the difference between a mutating table and a constraining table?

Answer: A table that is being modified through the use of a DML statement is called a mutating table. Additionally, it can also be a table that has triggers defined on it. Whereas a table used to read a referential integrity constraint is called a constraining table.

29. How can you debug your PL/SQL code?

Answer: For debugging our code, we can use DBMS_OUTPUT and DBMS_DEBUG statements:

• DBMS_OUTPUT displays the output to the standard console.
• DBMS_DEBUG prints the output to the log file.

30. What is the difference between SGA and PGA?

Answer:

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

Answer: PL/SQL provides three transaction specifications:

COMMIT: When DML operations are performed, the data is only manipulated in the database buffer, not the actual database. To save these DML transactions to the database, you must COMMIT them.

The COMMIT transaction action saves all undone changes since the last commit and the below steps take place:

• The release of affected rows.
• Transaction marked as complete.
• The details of the transaction are stored in the data dictionary.

Syntax: COMMIT;

ROLLBACK: To undo or erase the changes made in the current transaction, we must roll back the changes. The ROLLBACK statement erases all changes made since the last COMMIT.

Syntax: ROLLBACK;

SAVEPOINT:This statement identifies and defines a point in the transaction process where any changes occurring before that point are preserved while all changes occurring afterward are released.

Syntax: SAVEPOINT <savepoint_name>;

32. Explain the difference between procedure and trigger.

Answer:

33. Is it possible to declare a column with a number data type and its scale greater than the precision? For example defining columns like column name NUMBER (10,100), column name NUMBER (10,-84)

Answer: Yes, it is possible to make such declarations. In the example above, (9, 12) indicates the number has 12 digits after the decimal point. However, since the maximum precision is 9, the rest are 0 padded like 0.000999999999.

Number (9, -12) signifies there are 21 digits before the decimal point, and out of that, there are 9 possible digits, and the rest are 0 padded like 999999999000000000000.0

34. How many triggers can be applied to a table?

Answer: We can apply a maximum number of 12 triggers on a table.

35. Write a PL/SQL procedure for selecting some records from the database using some parameters as filters.

Answer: Assume that we are retrieving details of employees from the ib_employee table where salary is a parameter for the filter.

CREATE PROCEDURE get_employee_details @salary nvarchar(30)
AS
BEGIN
   SELECT * FROM ib_employee WHERE salary = @salary;
END;

36. What is IN OUT parameter?

Answer: IN OUT parameter is a mode that passes a value to a subprogram and returns an updated value. Moreover, we can assign a value to it and then read the value back. However, the parameter matching to an IN OUT formal parameter must be a variable instead of a constant or expression.

37. Give a simple way to run a query faster.

Answer: We can use ROWID to run a query fast. It is the logical address of a row, not a physical column. It includes the block number, file number, and row number, hence decreasing I/O time, therefore, making query execution faster.

38. What are DBMS_OUTPUT and DBMS_DEBUG?

Answer: As definition:

DBMS_OUTPUT: A built-in package that lets you display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers.

DBMS_DEBUG: A PL/SQL interface to the PL/SQL debugger layer, Probe, in the Oracle server. Debugging PL/SQL program units on the server-side are possible using this API. It is primarily intended for implementing server-side debuggers.

We can use both of them for debugging the code. DBMS_OUTPUT prints its output to the console, whereas DBMS_DEBUG prints it to a log file.

39. Can you use an IF statement inside a SELECT statement? How?

Answer: In versions 9 and above, we can do this using the DECODE keyword. Here's an example:

SELECT day_of_week,
DECODE (number, 0, 'Sunday',
1, 'Monday',
2, 'Tuesday',
3, 'Wednesday',
4, 'Thursday',
5, 'Friday',
6, 'Saturday',
'No match') result FROM weekdays;

40. List some cursor attributes in PL/SQL.

Answer: Here are some cursor attributes in PL/SQL:

• %ISOPEN: Check if the cursor is open.
• %FOUND: Checks if the cursor has fetched any row, returns Boolean.
• %NOT FOUND: Checks if the cursor has fetched any row. Returns Boolean.
• %ROWCOUNT: Get the number of rows that are updated, deleted, or fetched.

41. How to restrict string length in PL/SQL?

Answer: We can use CHAR (NUMBER) to get a fixed length for a variable. Example – CHAR (10). If the length of the string is less than the specified number, white spaces will be added.

42. Write PL/SQL program to find the sum of digits of a number.

Answer:

DECLARE
--Declare variables num, sum_of_digits and remainder of datatype Integer 
num  INTEGER; 
sum_of_digits INTEGER; 
remainder  INTEGER; 
BEGIN
num := 123456; 
sum_of_digits := 0;
-- Find the sum of digits until original number doesnt become null 
WHILE num <> 0 LOOP 
 remainder := MOD(num, 10); 
 sum_of_digits := sum_of_digits + remainder; 
 num := TRUNC(num / 10); 
END LOOP; 
dbms_output.PUT_LINE('Sum of digits is '|| sum_of_digits); 
END;

Input: 9874

Output: 28

43. How do you trace the PL/SQL code?

Answer: You can trace the PL/SQL code via DBMS_* methods like

• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR

44. Explain the error ORA-03113.

Answer: An end-of-file error on communication channel ORA-03113 indicates a broken connection between client and server. A timeout may have caused the connection to fail. Ping the server and check for connectivity to troubleshoot.

45. Write a simple procedure to select some records from the database using some parameters.

Answer:

CREATE PROCEDURE get_customer_details @age nvarchar(20), @city nvarchar(10)
AS

BEGIN
SELECT * FROM customers WHERE age = @age AND city = @city;
END;

46.Write a PL/SQL program using a WHILE loop for calculating the average of the numbers entered by the user. Stop the entry of numbers whenever the user enters the number 0.

Answer:

DECLARE
   n NUMBER;
   average NUMBER :=0 ;
   sum NUMBER :=0 ;
   count NUMBER :=0 ;
BEGIN
   -- Take input from user
   n := &input_number;
   WHILE(n<>0)
       LOOP
           -- Increment count to find total elements
           count := count+1;
           -- Sum of elements entered
           sum := sum+n;
           -- Take input from user
           n := &input_number;
       END LOOP;
   -- Average calculation
   average := sum/count;
   DBMS_OUTPUT.PUT_LINE(‘Average of entered numbers is ’||average);
END;

47. Tell us about SQLCODE and SQLERRM.

Answer: To trace exceptions that aren't explicitly handled by the program, SQLCODE and SQLERRM are used. These are globally defined variables. While SQLCODE returns the error code, SQLERRM returns the corresponding error message.

48. In what cursor attributes the outcomes of DML statement execution are saved?

Answer: There are four cursor attributes in which the outcomes of DML statement execution are saved:

1. SQL%FOUND: This function returns TRUE if at least one row has been processed.

2. SQL%NOTFOUND: This function returns TRUE if no rows were processed.

3. SQL%ISOPEN: This function determines whether the cursor is open or not and returns TRUE if it is.

4. SQL%ROWCOUNT: This function returns the number of rows processed by the DML statement.

49. Write a PL/SQL code to count the number of Sundays between the two inputted dates.

Answer:

--declare 2 dates of type Date
DECLARE
   start_date Date; 
   end_date Date; 
   sundays_count Number:=0; 
BEGIN
   -- input 2 dates
   start_date:='&input_start_date'; 
   end_date:='&input_end_date';
   /* 
   Returns the date of the first day after the mentioned date 
   and matching the day specified in second parameter.
   */
   start_date:=NEXT_DAY(start_date-1, 'SUNDAY');
   --check the condition of dates by using while loop. 
   while(start_date<=end_date) 
   LOOP 
       sundays_count:=sundays_count+1; 
       start_date:=start_date+7; 
   END LOOP; 

   -- print the count of sundays
   dbms_output.put_line('Total number of Sundays between the two dates:'||sundays_count); 
END; 
/

Input:

• start_date = ‘01-SEP-19’
• end_date = ‘29-SEP-19’

50. Write PL/SQL program to convert each digit of a given number into its corresponding word format.

Answer:

DECLARE
-- declare necessary variables 
   -- num represents the given number
   -- number_to_word represents the word format of the number
   -- str, len and digit are the intermediate variables used for program execution
num   INTEGER; 
number_to_word VARCHAR2(100); 
digit_str   VARCHAR2(100); 
len   INTEGER; 
digit   INTEGER; 
BEGIN
   num := 123456; 
   len := LENGTH(num); 
   dbms_output.PUT_LINE('Input: ' ||num);
   -- Iterate through the number one by one
   FOR i IN 1..len LOOP 
       digit := SUBSTR(num, i, 1);
       -- Using DECODE, get the str representation of the digit
       SELECT Decode(digit, 0, 'Zero ', 
                       1, 'One ', 
                       2, 'Two ', 
                       3, 'Three ', 
                       4, 'Four ', 
                       5, 'Five ', 
                       6, 'Six ', 
                       7, 'Seven ', 
                       8, 'Eight ', 
                       9, 'Nine ') 
       INTO digit_str 
       FROM dual;
       -- Append the str representation of digit to final result.
       number_to_word := number_to_word || digit_str; 
   END LOOP;
   dbms_output.PUT_LINE('Output: ' ||number_to_word); 
END;

Input: 12345

Output: One Two Three Four Five

---

If you have made it this far, then certainly you are willing to learn more about databases. Here are some more resources related to the database that we think will be useful to you.

• 10 Best MySQL Courses & Tutorials - Learn MySQL Online
• 9 Best PostgreSQL Tutorials and Courses - Learn PostgreSQL Online
• 12 Best SQL Courses & Tutorials - Learn SQL Online