Christmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
Christmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
PL SQL interview questions are a big part of acing any PL SQl interview. Being one of the most popular databases in the programming language, PL/SQL (Procedural Language for SQL) is fast, works seamlessly, and is surprisingly flexible with the Oracle databases. One of the main reasons behind top leading companies choosing PL/SQL is its effectiveness in handling database tasks and powerful features. Some of these leading companies include TCS, Infosys, Accenture, Oracle, IBM, Cognizant, and more.
If you are looking for top PL SQL interview questions and answers then this guide is for you. Today we’ll be discussing 45 PL SQL interview questions and answers that are perfect for beginners, intermediate, and advanced professionals. What's more? You’ll get a great understanding of topics like procedures and functions, PL/SQL concepts, cursors, exception handling, performance tuning, triggers, and more. So what are you waiting for? Read on to discover top PL SQL interview questions and basic LWC interview questions that can make you succeed in your interview.
A: PL/SQL stands for Procedural Language/Structured Query Language. It’s basically an extension of SQL that lets you do more complex programming, like adding loops, conditionals, and error handling to your SQL statements. PL/SQL and SQL data types are mainly used for building database applications and writing stored procedures in Oracle Database.
A: A trigger is made up of three key components:
A: A trigger is like a set of instructions in the database that automatically runs when certain events happen, such as when data is inserted, updated, or deleted from a table. Triggers are super handy for things like enforcing business rules, ensuring data consistency, and automating repetitive tasks. You write triggers in PL/SQL and link them to specific database tables.
A: In PL/SQL, if you’re working with anonymous blocks (like standalone or non-stored procedures), you’ll need a DECLARE block to define any variables. This block should be placed right at the beginning of your code when used in a standalone file.
A:
A: You need a DECLARE block in anonymous PL/SQL blocks, like when you’re writing standalone or non-stored procedures. It should be the first thing in your code if you’re using it in a standalone file.
A: Exception handling in PL/SQL is all about managing errors or unexpected situations that might come up while your program is running. It lets you define specific actions to take when something goes wrong, like logging the error, rolling back changes, or throwing custom error messages.
A: A view is like a virtual table that you create by combining data from one or more real tables. It behaves like a regular table with rows and columns, but it’s based on the result of an SQL query. You create a view using the CREATE VIEW command.
A: A PL/SQL table is an ordered collection of elements that are all of the same type, and each element is assigned a position based on its index number. Before you can use a PL/SQL table, you need to declare a custom data type, then declare the table as a variable.
A: A cursor in PL/SQL is like a pointer that helps you retrieve and process rows from a SQL query one at a time. Think of it as a tool that helps you work with multiple rows of data from a database.
There are two types of cursors:
A: PL/SQL offers several data types:
A:
Implicit Cursor: Automatically created by Oracle, it fetches one row at a time and gives the programmer less control.
Explicit Cursor: Created and managed by the programmer, it can fetch multiple rows and offers more control and efficiency.
A: PL/SQL packages come with several perks:
A: Here are some common predefined exceptions in PL/SQL:
NO_DATA_FOUND: Occurs when a SELECT statement doesn’t return any rows.
TOO_MANY_ROWS: Happens when a SELECT INTO statement pulls back more than one row.
DUP_VAL_ON_INDEX: Triggered when you try to insert a duplicate value into an indexed column.
ZERO_DIVIDE: Happens when you try to divide a number by zero.
The WHERE CURRENT OF clause in cursors is used to update or delete the most recently fetched row. It’s a handy way to directly affect the row that was last retrieved by the cursor without needing to use the SELECT FOR UPDATE statement.
Syntax: UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name WHERE CURRENT OF cursor_name;
A: COMMIT and ROLLBACK statements are super useful in keeping your database safe and consistent. They let you:
A:
A:
A:
A: You can tweak a column in these situations:
Increasing the width or precision of numeric columns.
Expanding the width of character or numeric columns.
Shrinking a column’s width, but only if it has no data or the table is empty.
Changing the data type, as long as the column is empty.
Switching between CHAR and VARCHAR2 data types if the column is empty or if you don’t change its size.
A: An ORA-03113 error indicates that the communication between the client and the Oracle database has been unexpectedly cut off. This can happen in several scenarios:
A: SQL uses various data types to store data in a specific format. Here are a few examples:
A: When naming a table, keep these rules in mind:
A: Stored procedures are like your database’s best friend! They offer:
A: The SET UNUSED option lets you mark a column as “unused,” meaning it won’t show up in your queries anymore, though the data stays put until you decide to drop it. This is handy when you want to remove a column without immediately affecting performance. Also read these laravel questions and answers to boost your skills
A:
A: Constraints help maintain the integrity of your data by enforcing rules at the table level. You can set constraints when creating a table or after. Types include:
Not Null Constraint: Ensures a column cannot have a NULL value.
Unique Key Constraint: Ensures all values in a column are unique.
Primary Key Constraint: Uniquely identifies each row in a table.
Foreign Key Constraint: Maintains referential integrity between tables.
Check Key Constraint: Ensures values meet a specific condition.
A: Sure! Here’s a simple program:
DECLARE string VARCHAR(10) := 'abababa'; letter VARCHAR(20); reverse_string VARCHAR(10); BEGIN FOR i IN REVERSE 1.. LENGTH(string) LOOP letter := SUBSTR(string, i, 1); reverse_string := reverse_string || letter; END LOOP; IF reverse_string = string THEN dbms_output.Put_line(reverse_string || ' is a palindrome'); ELSE dbms_output.Put_line(reverse_string || ' is not a palindrome'); END IF; END;
A: Comments make your code easier to understand. Here’s how you can write them:
A: TYPE: This is used to match a variable’s data type to a specific table column. Example:
vAttributeName Attribute.Attribute_NameTYPE;
ROWTYPE: This is handy when you need to match a variable’s data type to an entire row in a table. Example:
Rt_var_Student Student_ROWTYPE;
A: Here are a few common exceptions you might encounter:
A: In PL/SQL, cursors are used to handle query result sets. The main difference between implicit and explicit cursors lies in how they are managed:
Implicit Cursor:
Explicit Cursor:
Example:
Implicit Cursor Example
DECLARE
v_emp_name VARCHAR2(100);
BEGIN
SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;
-- Explicit Cursor Example
DECLARE
CURSOR emp_cursor IS SELECT first_name FROM employees;
v_emp_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END LOOP;
CLOSE emp_cursor;
END;
A: Exception handling in PL/SQL is critical for managing runtime errors and ensuring smooth program execution. There are two main types of exceptions in PL/SQL:
Predefined Exceptions: These are automatically raised by the system for common errors (e.g., NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE).
User-defined Exceptions: These are custom exceptions defined by the programmer using the EXCEPTION block.
Custom Exception Handling Example:
DECLARE
insufficient_balance EXCEPTION; -- Define a custom exception
v_balance NUMBER := 500;
v_withdrawal_amount NUMBER := 600;
BEGIN
IF v_withdrawal_amount > v_balance THEN
RAISE insufficient_balance; -- Raise custom exception
ELSE
v_balance := v_balance - v_withdrawal_amount;
DBMS_OUTPUT.PUT_LINE('Transaction successful. New Balance: ' || v_balance);
END IF;
EXCEPTION
WHEN insufficient_balance THEN
DBMS_OUTPUT.PUT_LINE('Error: Insufficient balance for this transaction.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
A: Bulk binding is a PL/SQL feature that allows you to perform SQL operations (like INSERT, UPDATE, DELETE, or SELECT) on collections (such as VARRAY, TABLE, or PL/SQL associative arrays) in a single context switch between PL/SQL and the SQL engine. This can greatly improve performance by reducing the number of context switches.
Key Bulk Binding Constructs:
Example:
DECLARE
TYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_array := emp_id_array(100, 101, 102, 103);
BEGIN
FORALL i IN emp_ids.FIRST..emp_ids.LAST
DELETE FROM employees WHERE employee_id = emp_ids(i);
DBMS_OUTPUT.PUT_LINE('Employees deleted successfully.');
END;
A: Optimizing PL/SQL code involves several strategies to reduce execution time and resource usage. Here are some best practices:
Example:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
BEGIN
-- Bulk collect data instead of fetching it row by row
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;
-- Use FORALL for bulk DML operations
FORALL i IN emp_data.FIRST..emp_data.LAST
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_data(i).employee_id;
DBMS_OUTPUT.PUT_LINE('Salaries updated successfully.');
END;
A: A mutating table error occurs when a trigger attempts to modify a table that is currently being modified by the statement that fired the trigger. This leads to an inconsistent state as the trigger cannot read or modify the table that is in the middle of an update.
Ways to Avoid Mutating Table Errors:
Example of a Mutating Table Error:
CREATE OR REPLACE TRIGGER prevent_mutation
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
-- This will cause a mutating table error
IF :NEW.salary > (SELECT AVG(salary) FROM employees) THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary is above the average.');
END IF;
END;
Solution:
-- One way to avoid the error is to use a compound trigger
CREATE OR REPLACE TRIGGER compound_trigger_example
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
TYPE emp_salary_tab IS TABLE OF employees.salary%TYPE;
avg_salary emp_salary_tab;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(salary) BULK COLLECT INTO avg_salary FROM employees;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.salary > avg_salary(1) THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary is above the average.');
END IF;
END AFTER EACH ROW;
END compound_trigger_example;
This compound trigger prevents mutating table errors by performing the average salary calculation before any row-level changes.
A: The AUTONOMOUS_TRANSACTION pragma in PL/SQL allows you to create a transaction that is independent of the main transaction. When you use this pragma, the PL/SQL block (procedure, function, or trigger) becomes an autonomous transaction, which can commit or roll back changes without affecting the main transaction.
Use Cases:
Example:
CREATE OR REPLACE PROCEDURE log_error(p_error_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; -- Declaring the procedure as an autonomous transaction
BEGIN
INSERT INTO error_log (error_message, log_date)
VALUES (p_error_msg, SYSDATE);
COMMIT; -- Committing within the autonomous transaction
END;
BEGIN
-- Main transaction
INSERT INTO employees (employee_id, first_name, salary)
VALUES (101, 'John Doe', 5000);
-- Simulate an error and log it
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Simulated error');
EXCEPTION
WHEN OTHERS THEN
log_error(SQLERRM);
ROLLBACK; -- Rollback the main transaction
END;
END;
A: Row-Level Security (RLS) is a feature that allows you to control access to rows in a table based on the user's credentials or other criteria. It is often implemented using Oracle's Virtual Private Database (VPD) or by creating security policies within PL/SQL.
Implementation using VPD:
Example:
Step 1: Create the security policy function
CREATE OR REPLACE FUNCTION security_policy(p_schema VARCHAR2, p_object VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN 'department_id = ' || SYS_CONTEXT('USERENV', 'SESSION_USER');
END;
-- Step 2: Apply the policy to the employees table
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'employees',
policy_name => 'employee_policy',
function_schema => 'HR',
policy_function => 'security_policy',
statement_types => 'SELECT, INSERT, UPDATE, DELETE'
);
END;
A:bIn PL/SQL, TYPE and ROWTYPE are attributes used to define variables that inherit the data types of database columns or entire rows of a table.
TYPE:
Example:
DECLARE
v_emp_id employees.employee_id%TYPE; -- Variable with the same data type as employee_id column
BEGIN
SELECT employee_id INTO v_emp_id FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
END;
ROWTYPE: Used to declare a record with the same structure (columns and their data types) as a row in a table or cursor. Useful when you want to work with all columns of a table or query result. Example:
DECLARE
v_employee employees%ROWTYPE; -- Record with the same structure as a row in the employees table
BEGIN
SELECT * INTO v_employee FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee.first_name || ' ' || v_employee.last_name);
END;
When to Use: Use TYPE when you need a variable with the same type as a specific column. Use %ROWTYPE when you need a record to hold an entire row of data from a table or cursor.
SQL Server Training & Certification
A: In PL/SQL, parameters in procedures and functions can be of three types: IN, OUT, and IN OUT. These types define the direction in which data flows between the caller and the subprogram.
IN Parameter:
Example:
CREATE OR REPLACE PROCEDURE show_employee(p_emp_id IN NUMBER) IS
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
OUT Parameter: Used to return a value from the subprogram to the caller. The value is assigned within the subprogram and passed back to the caller. The initial value (if any) is lost when the subprogram is called. Example:
CREATE OR REPLACE PROCEDURE get_employee_name(p_emp_id IN NUMBER, p_emp_name OUT VARCHAR2) IS BEGIN SELECT first_name INTO p_emp_name FROM employees WHERE employee_id = p_emp_id; END;
IN OUT Parameter: Used to pass a value into the subprogram and return a modified value to the caller. The parameter acts as both an IN and OUT parameter, allowing it to be used for input and output. Example: CREATE OR REPLACE PROCEDURE update_salary(p_emp_id IN NUMBER, p_salary IN OUT NUMBER) IS BEGIN UPDATE employees SET salary = p_salary WHERE employee_id = p_emp_id; SELECT salary INTO p_salary FROM employees WHERE employee_id = p_emp_id; END;
Example:
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id IN NUMBER, p_salary IN OUT NUMBER) IS BEGIN UPDATE employees SET salary = p_salary WHERE employee_id = p_emp_id; SELECT salary INTO p_salary FROM employees WHERE employee_id = p_emp_id; END;
A: PL/SQL collections are ordered groups of elements, all of the same type, that are used to store multiple values. Collections are similar to arrays in other programming languages and come in three types:
Associative Arrays (Index-By Tables):
Key-value pairs, where keys can be of any scalar data type (e.g., VARCHAR2, NUMBER).
Unbounded and sparse, meaning you can have gaps between indexes.
Example:
DECLARE
TYPE emp_name_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
v_emp_names emp_name_array;
BEGIN
v_emp_names(1) := 'John Doe';
v_emp_names(2) := 'Jane Smith';
DBMS_OUTPUT.PUT_LINE('Employee 1: ' || v_emp_names(1));
DBMS_OUTPUT.PUT_LINE('Employee 2: ' || v_emp_names(2));
END;
Nested Tables: Similar to associative arrays but can be stored in a database column. They are unbounded but can have gaps (sparse). Example:
DECLARE
TYPE num_table IS TABLE OF NUMBER;
v_numbers num_table := num_table(1, 2, 3, 4, 5);
BEGIN
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || v_numbers(i));
END LOOP;
END;
VARRAYs (Variable-Size Arrays): Bounded collections with a fixed maximum size. They are dense, meaning they cannot have gaps between elements. Example:
DECLARE
TYPE num_varray IS VARRAY(5) OF NUMBER;
v_numbers num_varray := num_varray(1, 2, 3);
BEGIN
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || v_numbers(i));
END
Preparing for your PL/SQL interview with these advanced PL SQL interview questions and basic LWC interview questions will give you a solid advantage. By understanding key concepts like exception handling and bulk processing, you'll feel more confident and ready to showcase your skills.
For even more support, JanBask Training offers courses that help professionals like you master PL/SQL and other essential skills. With expert guidance and practical training, you'll be well-prepared to succeed in your career.
SQL Server MERGE Statement: Question and Answer
Jul 11, 2023 
2.4k
Mastering INSERT and OVER DML Syntax: Interview Questions Guide
Jul 11, 2023 2.1k
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment
