Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

Top PL/SQL Interview Questions And Answers

Introduction

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.

PL SQL Interview Questions for Beginners

Q1: What is PL/SQL?

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.

Q2: What are the Key Parts of a Trigger?

A: A trigger is made up of three key components:

  • A triggering event or statement
  • A condition or restriction
  • An action that gets executed

Q3: Can You Explain What a Trigger Is and How It Works?

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.

Database

Q4: When Do You Need a DECLARE Block?

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.

Q5: What’s the Difference Between Syntax and Runtime Errors?

A: 

  • Syntax Error: These errors pop up when you’re writing the code and are usually due to incorrect PL/SQL syntax. They need to be fixed before the code can run.
  • Runtime Error: These errors occur while the program is running. They’re usually caused by things like dividing by zero or data type mismatches. You can handle these errors using PL/SQL’s exception handling feature.

Q6: Why Is a DECLARE Block Necessary Sometimes?

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.

Q7: What Is Exception Handling in PL/SQL?

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.

Q8: What’s a View in SQL?

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.

Q9: What’s a PL/SQL Table?

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.

Q10: What Is a Cursor in PL/SQL, and What Types Are There?

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:

  • Implicit Cursor: Automatically created by Oracle when you perform operations like SELECT INTO, INSERT, DELETE, or UPDATE. Oracle manages this cursor for you, including its lifecycle and status.
  • Explicit Cursor: Created by the programmer and defined in the declaration section of the code. You’re in charge of its lifecycle, using commands like OPEN, FETCH, and CLOSE.

Q11: What Data Types Are Available in PL/SQL?

A: PL/SQL offers several data types:

  • Scalar: A simple data type with no internal structure, like CHAR, DATE, VARCHAR2, NUMBER, and BOOLEAN.
  • Composite: A complex data type that combines different types and has internal components that you can work with together, like RECORD, TABLE, and VARRAY.
  • Reference: Stores pointers that link to other programs or data elements, like REF CURSOR.
  • Large Object: Holds locators for large items stored outside of the table, like videos or images. Examples include BLOB, BFILE, CLOB, and NCLOB.

Q12: How Do Implicit and Explicit Cursors Differ?

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.

Q13: What Are the Advantages of Using PL/SQL Packages?

A: PL/SQL packages come with several perks:

Enforced Information Hiding

  • Encapsulation: Group related code and data into a single package, keeping everything organized and modular.
  • Reusability: Write code once and reuse it in different programs, reducing repetition.
  • Information Hiding: Keep the details hidden by only exposing necessary parts of the package.
  • Improved Performance: Packages are precompiled and stored in the database, which can speed up execution.

Q14: Can You Give Some Examples of Predefined Exceptions?

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.

Q15: What’s the Purpose of WHERE CURRENT OF in Cursors?

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;

PL SQL Interview Questions for Intermediate

Q16: What Are the Benefits of Using COMMIT and ROLLBACK Statements?

A: COMMIT and ROLLBACK statements are super useful in keeping your database safe and consistent. They let you:

  • Make sure your data is consistent and reliable.
  • Preview changes before deciding to keep them.
  • Group related operations together for better organization.

Q17: How Do ROWTYPE and TYPE Differ?

A: 

  • ROWTYPE: Think of it as a shortcut to create a variable that matches the structure of an entire table row.
  • TYPE: This one is for when you want a variable to have the same data type as a specific column in a table.

Q18: What’s the Main Difference Between a Mutating Table and a Constraining Table?

A: 

  • Mutating Table: A table that’s in the process of being modified, like when you’re running a DML (Data Manipulation Language) operation or if it has triggers set up.
  • Constraining Table: A table that’s being checked to enforce referential integrity, ensuring that relationships between tables are consistent.

Q19: What Are COMMIT, ROLLBACK, and SAVEPOINT in PL/SQL?

A: 

  • COMMIT: This command locks in any changes you’ve made during the current transaction, making them permanent and visible to everyone else.
  • ROLLBACK: If you change your mind, this command lets you undo changes made in the current transaction.
  • SAVEPOINT: Think of this as a checkpoint within your transaction. You can roll back to this point instead of undoing everything.

Q20: When Can You Modify a Column in a Table?

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.

Q21: What Does the ORA-03113 Error Mean?

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:

  • The server crashes.
  • The server process gets killed at the OS level.
  • Network issues.
  • The client struggles with managing multiple connections.

Q22: What Are Some Common Data Types in SQL?

A: SQL uses various data types to store data in a specific format. Here are a few examples:

  • VARCHAR2(size): For variable-length strings (1 to 4000 characters).
  • CHAR(size): For fixed-length strings (1 to 2000 characters).
  • NUMBER(P,S): For numbers with precision (1-38) and scale (-84 to 127).
  • DATE: For date values.
  • LONG: For long text, up to 2GB.
  • CLOB: For large character objects, up to 4GB.
  • RAW(size): For raw binary data (up to 2000 bytes).
  • BLOB: For binary large objects, up to 4GB.
  • ROWID: A unique identifier for a row in a table.

Q23: What Are the Rules for Naming a Table?

A: When naming a table, keep these rules in mind:

  • Start the name with a letter.
  • The name can be up to 30 characters long.
  • Use only letters, numbers, underscores (_), dollar signs ($), or hash symbols (#).
  • The name should be unique and not clash with any other object owned by the same user.
  • Avoid using Oracle reserved words.

Q24: What Are the Benefits of Stored Procedures?

A: Stored procedures are like your database’s best friend! They offer:

  • Better Performance: Since they’re precompiled, they run faster.
  • Higher Productivity: Write once, use often.
  • Ease of Use: They simplify complex tasks.
  • Increased Scalability: They grow with your needs.
  • Interoperability: They work well across different systems.
  • Advanced Security: You can lock down access to sensitive data.
  • Replication: They make it easy to duplicate data across systems.

Q25: What Is the SET UNUSED Option?

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

Q26: How Do IN, OUT, and IN OUT Parameters Work?

A: 

  • IN: Passes a value to a procedure; it acts like a constant.
  • OUT: Sends a value back from the procedure to the caller; starts uninitialized.
  • IN OUT: Passes a value in and returns an updated value to the caller.

Q27: What are Constraints and What Types Are There?

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.

Q28: Can You Write a PL/SQL Program to Check if a String Is a Palindrome?

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;

Q29: How Do You Write Comments in PL/SQL?

A: Comments make your code easier to understand. Here’s how you can write them:

  • Single-line comment: Use -- to comment out a single line.
  • Multi-line comment: Use /*...*/ to comment out multiple lines.

Q30: What’s the Importance of TYPE and ROWTYPE in PL/SQL?

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;

Advanced PL SQL Interview Questions

Q31: What Are Some Common PL/SQL Exceptions?

A: Here are a few common exceptions you might encounter:

  • INVALID_NUMBER: Raised when a conversion between strings and numbers fails.
  • TOO_MANY_ROWS: Triggered when a SELECT statement returns more than one row.
  • ACCESS_INTO_NULL: Happens when you try to assign a value to an uninitialized object.
  • CASE_NOT_FOUND: Occurs when none of the conditions in a CASE statement are met.
  • ZERO_DIVIDE: Raised when an attempt is made to divide by zero.
  • NO_DATA_FOUND: Raised when a SELECT INTO statement returns no rows.

Q32: What is the difference between an implicit cursor and an explicit cursor in PL/SQL?

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:

  • Automatically created by Oracle when a SQL statement (like SELECT INTO, INSERT, UPDATE, or DELETE) is executed.
  • The cursor lifecycle (open, fetch, close) is managed by Oracle.
  • It is limited to handling single-row queries. If the query returns multiple rows, an error (NO_DATA_FOUND or TOO_MANY_ROWS) is raised.
  • Implicit cursors are easier to use but less flexible.

Explicit Cursor:

  • Explicitly defined by the programmer to handle multi-row queries.
  • The programmer has full control over the cursor lifecycle, i.e., opening, fetching, and closing the cursor.
  • Explicit cursors provide more control and are useful for handling complex queries that return multiple rows.

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;

Q33: How do you handle exceptions in PL/SQL? Can you give an example of defining custom exceptions?

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;

Q34: Explain the concept of bulk binding in PL/SQL and how it improves performance.

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:

  • FORALL: Used for performing DML operations on a collection of data.
  • BULK COLLECT: Used for fetching multiple rows into a collection in a single operation.

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;

Q35: How can you optimize PL/SQL code for better performance?

A: Optimizing PL/SQL code involves several strategies to reduce execution time and resource usage. Here are some best practices:

  • Use Bulk Binding: Utilize BULK COLLECT and FORALL to minimize context switches between PL/SQL and SQL.
  • Minimize SQL Execution: Avoid repetitive SQL queries inside loops. Instead, fetch necessary data in a single query using collections.
  • Use Collection Methods Wisely: Methods like COUNT, EXISTS, LIMIT, and PRIOR can optimize operations on collections.
  • Optimize Loops: Use simple loops and minimize the work done inside loops. For example, avoid SQL queries inside loops whenever possible.
  • Leverage SQL Profiler: Use Oracle's SQL Profiler to identify performance bottlenecks in your code.
  • Use Appropriate Data Types: Use proper data types to reduce unnecessary type conversion overhead.
  • Avoid Unnecessary Computations: Avoid complex expressions and computations that are not needed in the final output.
  • Use Native Compilation: Compile PL/SQL code using native compilation to improve execution speed.

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;

Q36: What is a mutating table error and how can it be avoided in PL/SQL?

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:

  • Use Statement-Level Triggers: Prefer statement-level triggers (BEFORE STATEMENT or AFTER STATEMENT) over row-level triggers to avoid mutating table errors.
  • Use Temporary Tables: Use a temporary table or collection to hold the data that would cause the mutation and process it after the triggering event.
  • Decompose the Trigger Logic: Decompose the complex logic into multiple triggers or procedures to handle the data manipulation separately.

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.

Q37: What is the purpose of the AUTONOMOUS_TRANSACTION pragma in PL/SQL, and when would you use it?

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:

  • Logging: You might want to log errors or events in a table without affecting the main transaction.
  • Audit Trail: Use it to maintain an audit trail, ensuring that the audit data is committed even if the main transaction fails.
  • Complex Business Logic: When certain operations need to be committed regardless of the success or failure of the surrounding transaction.

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;

Q38: Explain the concept of row-level security (RLS) and how it can be implemented in PL/SQL.

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:

  • Create a Policy Function: The function defines the security policy, determining which rows a user can access.
  • Apply the Policy to a Table: Use the DBMS_RLS package to associate the policy function with a table.

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;

Q39: What is the difference between TYPE and ROWTYPE in PL/SQL? When would you use each?

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:

  • Used to declare a variable with the same data type as a column or another variable.
  • Ensures that your variable's data type automatically matches the column's data type, even if the column's type changes in the future.
  • Commonly used for single column or variable types.

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

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Q40: Describe the difference between IN, OUT, and IN OUT parameters in PL/SQL procedures and functions.

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:

  • Used to pass values into the subprogram.
  • The value cannot be modified within the subprogram.
  • Default parameter mode.

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;

Q41: What are PL/SQL collections, and how do you work with them?

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

Conclusion

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.

Trending Courses

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models

Upcoming Class

18 days 02 Nov 2024

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

11 days 26 Oct 2024

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

3 days 18 Oct 2024

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

3 days 18 Oct 2024

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

3 days 18 Oct 2024

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

10 days 25 Oct 2024

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

3 days 18 Oct 2024

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

10 days 25 Oct 2024

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

4 days 19 Oct 2024

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

18 days 02 Nov 2024

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

31 days 15 Nov 2024

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

10 days 25 Oct 2024