data/PL_SQL.txt

What is PL/SQL = PL/SQL is Oracle’s procedural extension to SQL. Unlike SQL, which is declarative and focuses on data retrieval and manipulation, PL/SQL allows developers to implement procedural logic, making it a powerful tool for writing complex business rules within the database. It supports variables, loops, conditionals, exception handling, and modular programming through procedures, functions, and packages. It is possible to use PL/SQL without SQL knowledge PL/SQL is tightly integrated with SQL, the most widely used database manipulation language. For example: PL/SQL lets you use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns; 

What is the basic structure of a PL/SQL block = A PL/SQL block is the fundamental unit of execution in PL/SQL, and it consists of four main sections;\\n DECLARE (Optional): Used to define variables, constants, cursors, and user-defined types;\\nBEGIN: The executable section where SQL queries and procedural statements are written;\\nEXCEPTION (Optional): Handles runtime errors and exceptions to ensure graceful error recovery.;

When is a Declaration Statement Required in PL/SQL = In PL/SQL, a declaration statement is required when you need to define variables, constants, cursors, or subprograms before using them in the execution section of a PL/SQL block. The declaration section is optional, but if you plan to use any variables or program objects, you must declare them first.

How many block types are there in PL/SQL = Anonymous;\\nProcedure;\\nFunction;

What are the essential PL/SQL data types = PL/SQL supports various data types, categorized as follows;\\nScalar Types: Single-value types like NUMBER, VARCHAR2, DATE, BOOLEAN.;\\nComposite Types: Collections such as RECORD (custom structures) and TABLE/VARRAY (arrays);\\nReference Types: Pointers to database objects, like REF CURSOR for dynamic query processing;

What are the basic control structures in PL/SQL = PL/SQL includes several control structures that help manage the flow of a program:;\\nLoops: These include LOOP, FOR LOOP, and WHILE LOOP, allowing repetitive execution of statements.;\\nConditional Statements: These include IF and CASE statements, which execute different blocks of code based on conditions. The DECODE() function is another good example of a conditional that is worth studying;

How can we use comments in PL/SQL code = Single-Line Comments:;\\nUse two hyphens (--) to start a single-line comment.;\\nEverything after the -- on that line is ignored by the PL/SQL compiler.;\\n-- This is a single-line comment. v_counter := 0;;\\n -- Initialize the counter variable.;\\nMulti-Line Comments;\\nUse /* to begin a multi-line comment and */ to end it.;\\nEverything between /* and */ is ignored, even if it spans multiple lines.;\\n/* This is a multi-line comment. It can span multiple lines of code. This is useful for longer explanations. */ v_total := 10;

Explain the PL/SQL compilation process = The PL/SQL compilation process consists of the following phases:;\\nSyntax Checking;\\nThe PL/SQL compiler first checks the syntax of the code to ensure it follows proper PL/SQL grammar and structure.;\\nIf syntax errors are found, the compilation stops, and error messages are displayed;\\nExample of a Syntax Error = ;\\nDECLARE;\\nv_name VARCHAR2(50);\\nBEGIN;\\nv_name := 'Oracle;\\nDBMS_OUTPUT.PUT_LINE(v_name);\\nEND;

Missing closing quote in the string = Semantic Checking;\\nThe compiler checks whether database objects (tables, columns, procedures, etc.) referenced in the PL/SQL block exist.;\\nIt ensures that variable declarations and assignments are valid.;\\nIt verifies that the program follows logical consistency.;\\nExample of a Semantic Error = ;\\nDECLARE;\\nv_salary NUMBER;\\nBEGIN;\\nv_salary := emp_salary * 1.1; -- emp_salary is not declared;\\nEND;

emp_salary is not defined = Binding (Dependency Resolution);\\nThe compiler resolves dependencies between the PL/SQL block and database objects.;\\nIf a referenced table, view, or procedure is modified after compilation, the PL/SQL unit may become invalid.;\\nExample:;\\nCREATE OR REPLACE PROCEDURE get_employee AS;\\nBEGIN;\\nSELECT name FROM employees WHERE id = 101;\\nEND;

If the employees table is dropped, the procedure becomes invalid = Code Generation & Storage;\\nIf all checks pass, the PL/SQL block is converted into p-code (procedural code) and stored in the database.;\\nThe p-code is a compiled, intermediate representation of the PL/SQL program that is executed at runtime.;\\nExecution Phase;\\nWhen the PL/SQL block is executed, the Oracle runtime engine reads the p-code and processes it.;\\nIf an error occurs during execution (e.g., division by zero, no data found), the program jumps to the exception-handling section;

Example of Runtime Error = DECLARE;\\nv_num NUMBER;\\nBEGIN   v_num := 10 / 0 -- Division by zero error;\\nEXCEPTION;\\nWHEN ZERO_DIVIDE THEN ;\\nDBMS_OUTPUT.PUT_LINE('Cannot divide by zero');\\nEND;

Key Points in PL/SQL = PL/SQL code is compiled once and stored in the database.;\\nCompilation errors must be fixed before execution.;\\nDependencies on tables, views, and packages affect program validity.;\\nThe execution phase translates the stored p-code into machine code for processing;


What are PL/SQL Cursor = A cursor in PL/SQL is a pointer to the result set of a SQL query. Cursors allow row-by-row processing of query results in PL/SQL programs.;\\nTypes of Cursors;\\nPL/SQL supports two types of cursors:;\\n1.	Implicit Cursors;\\nCreated automatically by Oracle for SELECT INTO, INSERT, UPDATE, and DELETE statements.;\\nNo explicit declaration or handling is required.;\\nUsed when the query returns a single row.;\\nExample of Implicit Cursor:;\\nDECLARE;\\nv_salary NUMBER;\\nBEGIN;\\nSELECT salary INTO v_salary FROM employees WHERE id = 101;\\nDBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);\\nEND;

#SQL query error = If the query returns no rows or multiple rows, it raises exceptions (NO_DATA_FOUND, TOO_MANY_ROWS);

#Explicit Cursors = 
#Declared and controlled by the developer.
#Used for queries that return multiple rows.
#Provides better control over fetching and processing data.
#Steps to Use an Explicit Cursor:
#Declare the cursor.
#Open the cursor.
#Fetch data from the cursor.
#Close the cursor.
#Example of Explicit Cursor:
#DECLARE
#   CURSOR cur_emp IS SELECT name, salary FROM employees WHERE department = 'IT';
 #  v_name employees.name%TYPE;
  # v_salary employees.salary%TYPE;
#BEGIN
 #  OPEN cur_emp;
  # LOOP
   #   FETCH cur_emp INTO v_name, v_salary;
    #  EXIT WHEN cur_emp%NOTFOUND;
     # DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Salary: ' || v_salary);
   #END LOOP;
   #CLOSE cur_emp;
#END;

Cursor Attributes = PL/SQL provides built-in cursor attributes for handling cursor operations:;\\nAttribute	Description;\\n%FOUND	Returns TRUE if the last fetch found a row.;\\n%NOTFOUND	Returns TRUE if the last fetch did not find a row.;\\n%ISOPEN	Returns TRUE if the cursor is open.;\\n%ROWCOUNT	Returns the number of rows fetched so far.;

#Example:
#DECLARE
#   CURSOR cur_emp IS SELECT name FROM employees;
#   v_name employees.name%TYPE;
#BEGIN
 #  OPEN cur_emp;
 #  FETCH cur_emp INTO v_name;
 #  IF cur_emp%FOUND THEN
 #     DBMS_OUTPUT.PUT_LINE('Employee Found: ' || v_name);
 #  END IF;
 #  CLOSE cur_emp;
#END;

Ref Cursors (Dynamic Cursors) = A REF CURSOR is a dynamic cursor that can be opened for different queries at runtime. It allows for flexible and reusable query execution.;\\nExample:;\\nDECLARE;\\nTYPE emp_cursor_type IS REF CURSOR;\\nemp_cur emp_cursor_type;\\nv_name employees.name%TYPE;\\nBEGIN;\\nOPEN emp_cur FOR SELECT name FROM employees WHERE department = 'HR';\\nLOOP;\\nFETCH emp_cur INTO v_name;\\nEXIT WHEN emp_cur%NOTFOUND;\\nDBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);\\nEND LOOP;\\nCLOSE emp_cur;\\nEND;

When to Use Cursors = Use implicit cursors when fetching a single row.;\\nUse explicit cursors when processing multiple rows.;\\nUse REF CURSOR when working with dynamic queries;


Can you label a PL/SQL loop = Yes, in PL/SQL, you can label loops to improve readability and control nested loops effectively. Labels are placed before the loop begins and can be used with the EXIT statement to exit a specific loop when multiple loops are nested.;\\nExample: Labeling Nested Loops;\\nWhen working with nested loops, labeling helps in exiting the desired loop instead of the innermost one.;\\nDECLARE;\\nv_outer NUMBER := 1;\\nv_inner NUMBER;\\nBEGIN;\\n<<outer_loop>>;\\nFOR v_outer IN 1..3 LOOP;\\nDBMS_OUTPUT.PUT_LINE('Outer Loop Iteration: ' || v_outer);\\nv_inner := 1;\\n<<inner_loop>>LOOP;\\nDBMS_OUTPUT.PUT_LINE('   Inner Loop Iteration: ' || v_inner);;\\nv_inner := v_inner + 1;;\\n-- Exit the outer loop when a specific condition is met;\\nEXIT outer_loop WHEN v_outer = 2 AND v_inner = 3;\\n-- Exit only the inner loop when condition is met;\\nEXIT inner_loop WHEN v_inner > 3;;\\nEND LOOP inner_loop;\\nEND LOOP outer_loop;\\nEND;;\\nOutput;\\nOuter Loop Iteration: 1;\\nInner Loop Iteration: 1;\\nInner Loop Iteration: 2;\\nInner Loop Iteration: 3;\\nInner Loop Iteration: 4;\\nOuter Loop Iteration: 2;\\nInner Loop Iteration: 1;\\nInner Loop Iteration: 2;

How Do You Return More Than One Row in PL/SQL = In PL/SQL, you can return multiple rows using cursors, collections (TABLE, VARRAY), or REF CURSORs. Here are the different ways to achieve this:-;\\nUsing Explicit Cursors;\\nA cursor is used to fetch multiple rows one at a time.;\\nExample: Using Explicit Cursor to Return Multiple Rows;\\nDECLARE;\\n   CURSOR cur_emp IS SELECT emp_id, emp_name, salary FROM employees;\\nv_emp_id employees.emp_id%TYPE;\\nv_emp_name employees.emp_name%TYPE;\\nv_salary employees.salary%TYPE;\\nBEGIN;\\nOPEN cur_emp;\\nLOOP;\\nFETCH cur_emp INTO v_emp_id, v_emp_name, v_salary;\\nEXIT WHEN cur_emp%NOTFOUND;\\nDBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_emp_name || ', Salary: ' || v_salary);\\nEND LOOP;\\nCLOSE cur_emp;\\nEND;

bulk collect = Using BULK COLLECT for Returning Multiple Rows Efficiently Instead of fetching rows one by one, BULK COLLECT retrieves all rows at once into a collection (array/table type).;\\nDECLARE;\\nTYPE emp_table IS TABLE OF employees%ROWTYPE;\\nv_emps emp_table;;\\nBEGIN;\\nSELECT * BULK COLLECT INTO v_emps FROM employees;\\nFOR i IN v_emps.FIRST .. v_emps.LAST LOOP;\\nDBMS_OUTPUT.PUT_LINE('ID: ' || v_emps(i).emp_id || ', Name: ' || v_emps(i).emp_name);\\nEND LOOP;\\nEND;

using ref cursor = Using REF CURSOR for Returning Multiple Rows Dynamically, A REF CURSOR allows returning multiple rows dynamically, which is useful for functions and procedures.;\\nExample: Returning Multiple Rows Using REF CURSOR;\\nDECLARE;\\nTYPE emp_cursor IS REF CURSOR;\\nv_cursor emp_cursor;\\nv_emp_id employees.emp_id%TYPE;\\nv_emp_name employees.emp_name%TYPE;\\nBEGIN;\\nOPEN v_cursor FOR SELECT emp_id, emp_name FROM employees;\\nLOOP;\\nFETCH v_cursor INTO v_emp_id, v_emp_name;\\nEXIT WHEN v_cursor%NOTFOUND;\\nDBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_emp_name);\\nEND LOOP;\\nCLOSE v_cursor;\\nEND;

table function = Using a Table Function (Pipelined Function) A pipelined function allows SQL to fetch multiple rows one at a time like a table;

What is a Subquery in PL/SQL = A subquery is a query nested inside another SQL statement. It is used to retrieve data that will be used in the main query.;\\nSELECT emp_name, salary FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

Single-Row Subquery = Returns only one value (one row and one column). Used with operators like =, >, <, >=, <=.;\\nExample:;\\nSELECT emp_name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Multi-Row Subquery = Returns multiple rows. Used with operators like IN, ANY, ALL.;\\nExample:;\\nSELECT emp_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Correlated Subquery = The inner query depends on the outer query and executes for each row in the outer query.;\\nExample:;\\nSELECT emp_name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);\\nEXISTS Subquery Uses EXISTS to check if at least one row exists in the subquery.;\\nSELECT emp_name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.department_name = 'HR');

Nested Subqueries = A subquery inside another subquery.;\\nExample:;\\nSELECT emp_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location_id = (SELECT location_id FROM locations WHERE city = 'London'));.

Scalar Subquery = Returns a single value and can be used like a column in SELECT.;\\n Example:;\\nSELECT emp_name, (SELECT department_name FROM departments WHERE department_id = e.department_id) AS dept_name FROM employees e;

What are a subprogram in PL/SQL = A subprogram in PL/SQL is a named block of code that performs a specific task and can be reused multiple times. There are two types of subprograms:
1.	Procedures – Perform actions but do not return a value.
2.	Functions – Perform actions and return a value.

Subprograms = Subprograms improve modularity, reusability, and maintainability of PL/SQL code;

What is a Procedure in PL/SQL = A procedure in PL/SQL is a stored subprogram that performs a specific task but does not return a value directly. It is used to execute business logic, update records, validate data, or perform other operations inside the database.
Key Features of a Procedure
•	Modular: Can be called multiple times in different programs.
•	Parameter Passing: Accepts input (IN), output (OUT), or both (IN OUT) parameters.
•	Encapsulation: Hides implementation details.
•	Improves Performance: Stored procedures execute faster since they are precompiled.
Syntax of Procedure
CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) AS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;

Calling Procedure
BEGIN
   greet_user('Nadir');
END;

What is Function in PL/SQL = A function in PL/SQL is a named subprogram that performs a specific task and returns a single value. It is mainly used for calculations, data transformations, and returning computed values

CREATE OR REPLACE FUNCTION get_square (p_number IN NUMBER) 
RETURN NUMBER AS
BEGIN
   RETURN p_number * p_number;
END;

DECLARE
   v_result NUMBER;
BEGIN
   v_result := get_square(6);
   DBMS_OUTPUT.PUT_LINE('Square: ' || v_result);
END;
/

What is the difference between stored procedures and functions = 
Stored procedures and functions are both reusable PL/SQL code blocks, but they serve rather different purposes. Stored procedures are used to perform operations that do not return a value, such as inserting, updating, or deleting data. They are used for tasks that modify data or perform complex operations without returning a result. For example, the procedure below updates the salary of the employee with the given employee_id by adding the specified p_increment such that the salary updates dynamically based on input parameters, CREATE PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS
BEGIN
-- Update the salary of the employee with the given ID
UPDATE employees
SET salary = salary + p_increment
WHERE employee_id = p_emp_id;
END;
Functions, on the other hand, return a value after performing operations. They are suitable for calculations or data retrieval that need to return a result.
The function below simplifies fetching an employee's salary, making it reusable in SQL queries or other procedures.
CREATE FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER AS
v_salary NUMBER;
BEGIN
-- Retrieve the salary for the given employee ID
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_emp_id;
-- Return the retrieved salary  
RETURN v_salary;  
END;

What is pipeline function = A pipelined function is a type of table function in PL/SQL that returns rows one at a time as they are produced, rather than returning the entire collection at once. This improves performance by allowing the caller (SQL query) to start processing rows before the function has completed execution.
Key Features of Pipelined Functions
✔ Improves Performance – Rows are returned incrementally, reducing memory usage.
✔ Useful for Large Datasets – Ideal for processing large amounts of data.
✔ Can be Queried Like a Table – Used in SQL queries as a table function.
✔ Uses the PIPELINED Clause – The function must be defined with PIPELINED.
Syntax of a Pipelined Function
CREATE OR REPLACE FUNCTION get_employees
RETURN emp_table_type PIPELINED IS
BEGIN
   FOR rec IN (SELECT * FROM employees) LOOP
      PIPE ROW (rec); -- Sends a row to the calling query
   END LOOP;
   RETURN;
END;

Define a Collection Type = First, we need a collection type to hold the function’s return values.
CREATE OR REPLACE TYPE emp_record_type AS OBJECT (
   emp_id NUMBER,
   emp_name VARCHAR2(100),
   salary NUMBER
);


What are PL/SQL packages and its structure (constitute) = 
PL/SQL packages are collections of related procedures, functions, and variables that encapsulate code for better organization and reusability. They consist of two parts:
•	Package Specification: Declares public elements (procedures, functions, variables).
•	Package Body: Contains the implementation details of the procedures and functions declared in the specification.
For example, the query below creates a package employee_pkg that defines a procedure to raise an employee's salary and a function to retrieve the total number of employees, with their implementations to be provided in the package body.
-- Create a package named 'employee_pkg' CREATE PACKAGE employee_pkg AS
-- Procedure to increase an employee's salary by a percentage  
PROCEDURE raise_salary(p_emp_id NUMBER, p_percent NUMBER);  

-- Function to return the total number of employees  
FUNCTION get_total_employees RETURN NUMBER;  
END employee_pkg;

What are the benefits of using PL/SQL packages = Key Benefits of Using PL/SQL Packages, Code Encapsulation and Modularity, Packages help organize related procedures and functions into a single unit, making code modular and reusable. This improves code readability and maintainability.
Example:
Instead of defining multiple standalone procedures, you can group related ones in a package:
CREATE OR REPLACE PACKAGE employee_pkg AS
   PROCEDURE add_employee(p_name VARCHAR2, p_salary NUMBER);
   FUNCTION get_salary(p_id NUMBER) RETURN NUMBER;
END employee_pkg;

Performance Improvement = When a package is loaded into memory, all its components are loaded together.
This reduces disk I/O, as procedures and functions are already compiled in memory.
Encapsulation of Business Logic
Packages hide implementation details using the package body, exposing only necessary components through the package specification.
This prevents direct access to sensitive logic.
Example:
The package specification declares the interface, while the package body contains the logic:
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
   PROCEDURE add_employee(p_name VARCHAR2, p_salary NUMBER) IS
   BEGIN
      INSERT INTO employees (name, salary) VALUES (p_name, p_salary);
   END;

   FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS
      v_salary NUMBER;
   BEGIN
      SELECT salary INTO v_salary FROM employees WHERE id = p_id;
      RETURN v_salary;
   END;
END employee_pkg;

Overloading Capability = 
✅ Packages support function and procedure overloading, allowing multiple procedures/functions with the same name but different parameters.
Example:
CREATE OR REPLACE PACKAGE math_pkg AS
   FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;
   FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER, p_num3 NUMBER) RETURN NUMBER;
END math_pkg;

Security and Access Control
Packages can be granted privileges instead of individual procedures.
This improves security and access control.
Example:
GRANT EXECUTE ON employee_pkg TO hr_user;

Better Exception Handling
Packages allow centralized exception handling by defining all exception logic inside the package body.
Example:
CREATE OR REPLACE PACKAGE error_pkg AS
   PROCEDURE log_error(p_err_msg VARCHAR2);
END error_pkg;

CREATE OR REPLACE PACKAGE BODY error_pkg AS
   PROCEDURE log_error(p_err_msg VARCHAR2) IS
   BEGIN
      INSERT INTO error_log (error_message, log_time) VALUES (p_err_msg, SYSDATE);
   END;
END error_pkg;

Code Reusability
Once created, package components can be reused in different parts of an application without duplicating code.
Example:
Instead of writing the get_salary function multiple times, it can be called from different PL/SQL blocks:
DECLARE
   v_salary NUMBER;
BEGIN
   v_salary := employee_pkg.get_salary(101);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;

Dependency Management = If a packaged function or procedure is modified, only the package body needs recompilation, not the entire application.
Can We Run a Package Without a Body in PL/SQL?
Yes, a package specification can exist without a package body, and the package can still be executed if it only contains declarations such as constants, variables, cursors, and procedure/function prototypes.
Can We Define a Procedure in a Package Body Without Declaring It in the Package Specification?
Yes, you can define a procedure or function inside a package body without declaring it in the package specification. However, such procedures or functions will be private to the package and cannot be accessed from outside the package;

What are PL/SQL triggers = Triggers are PL/SQL blocks that execute automatically in response to specific database events, such as insertions, updates, or deletions. Triggers are used to enforce business rules. They also are commonly used to perform auditing. They are categorized into:
•	Row-Level Triggers: Execute once for each affected row.
•	Statement-Level Triggers: Execute once per SQL statement, regardless of the number of rows affected.
The query below creates an AFTER UPDATE trigger trg_salary_audit on the employees table that logs salary changes into the salary_audit table, capturing the employee ID, old and new salary, and the update timestamp.
-- Create or replace a trigger 'trg_salary_audit' CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees -- Fires after salary updates in 'employees' table FOR EACH ROW -- Executes for each updated row BEGIN
-- Inserts old and new salary details into 'salary_audit' table INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

What are the methods of exception handling in PL/SQL = PL/SQL provides error-handling mechanisms for stability and to prevent crashes. The types of exceptions include the following:
•	Predefined Exceptions: Built-in exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE.
•	User-Defined Exceptions: Custom exceptions are declared using EXCEPTION and raised using RAISE.
•  ZERO_DIVIDE: Raised when dividing a number by zero.
•  NO_DATA_FOUND: Raised when a SELECT INTO query returns no rows.
•  TOO_MANY_ROWS: Raised when a SELECT INTO query returns more than one row.
•  VALUE_ERROR: Raised for invalid data conversion.
•  DUP_VAL_ON_INDEX: Raised for unique constraint violations;

How can you verify whether an UPDATE statement is executed or not = The SQL %NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records. If the last SQL statement run did not affect any rows, this variable returns TRUE.
For example, the query below updates the salary of employees in department 10 by increasing it by 10%, and then checks whether any rows were affected by the UPDATE statement using the %NOTFOUND attribute. If no rows were updated, it outputs a message saying, "No rows were updated." If rows were updated, it outputs the number of rows that were affected using the SQL%ROWCOUNT attribute.
DECLARE -- Declare a variable to store the number of rows updated rows_updated INTEGER; BEGIN -- Perform an UPDATE statement on the 'employees' table UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
-- Check if any rows were updated by using %NOTFOUND
IF SQL%NOTFOUND THEN
    -- If no rows were updated, print a message
    DBMS_OUTPUT.PUT_LINE('No rows were updated.');
ELSE
    -- If rows were updated, print how many rows were affected
    rows_updated := SQL%ROWCOUNT;  -- Store the number of rows updated
    DBMS_OUTPUT.PUT_LINE(rows_updated || ' rows were updated.');
END IF;
END; 

What are the methods of optimizing performance in PL/SQL = Minimizing context switches between SQL and PL/SQL is crucial for optimizing performance. Each switch incurs overhead, which can slow down execution times, especially in situations involving frequent transitions between the two;

How do you use bulk operations to minimize context switches = PL/SQL provides bulk processing techniques to optimize SQL-to-PL/SQL interaction by fetching or modifying multiple rows at once.
For example, the PL/SQL block below retrieves all employees from department 10 using BULK COLLECT into a collection and iterates through it to print each employee's name, improving performance by minimizing context switches between SQL and PL/SQL.
DECLARE
-- Define a table-type collection based on the 'employees' table structure
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_emps emp_table; -- Declare a variable of this type
BEGIN
-- Bulk fetch employees from department 10 into the collection
SELECT * BULK COLLECT INTO v_emps FROM employees WHERE department_id = 10;
-- Loop through the collection and print employee names  
FOR i IN 1..v_emps.COUNT LOOP  
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i).employee_name);  
END LOOP;  
END;

FORALL FOR UPDATE = DECLARE
-- Define a table-type collection for employee IDs
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
-- Initialize collection with specific employee IDs
v_emp_ids t_emp_ids := t_emp_ids(101, 102, 103);
BEGIN
-- Bulk update salaries by 10% for specified employee IDs
FORALL i IN 1..v_emp_ids.COUNT
UPDATE employees SET salary = salary * 1.10 WHERE employee_id = v_emp_ids(i);
END;

What are dynamic SQL and Ref Cursors = Dynamic SQL allows executing SQL statements dynamically at runtime, which is useful when dealing with variable table names, columns, or query structures.
The following PL/SQL block uses dynamic SQL to count the number of rows in the employees table and prints the result. I like this kind of approach because it allows flexibility.
DECLARE
v_table_name VARCHAR2(50) := 'employees'; -- Store table name
v_count NUMBER; -- Variable to hold row count
BEGIN
-- Dynamically count rows in the specified table
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count;
-- Print the total count  
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);  
END;

Ref Cursor = Ref Cursors are dynamic cursors that can be opened, fetched, and closed at runtime, enabling the passing of query results between program units.
The PL/SQL block below uses a REF CURSOR to fetch and print employee names from department 20. The cursor is opened dynamically, iterated through using a loop, and closed after processing.
DECLARE
-- Define a REF CURSOR type
TYPE emp_ref_cursor IS REF CURSOR;
v_cursor emp_ref_cursor; -- Declare a cursor variable
v_name employees.employee_name%TYPE; -- Variable to store employee name
BEGIN
-- Open the cursor for employees in department 20
OPEN v_cursor FOR SELECT employee_name FROM employees WHERE department_id = 20;
-- Fetch and print employee names in a loop  
LOOP  
    FETCH v_cursor INTO v_name;  
    EXIT WHEN v_cursor%NOTFOUND;  
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);  
END LOOP;  

-- Close the cursor  
CLOSE v_cursor;  
END;
DECLARE
-- Define a REF CURSOR type
TYPE emp_ref_cursor IS REF CURSOR;
v_cursor emp_ref_cursor; -- Declare a cursor variable
v_name employees.employee_name%TYPE; -- Variable to store employee name
BEGIN
-- Open the cursor for employees in department 20
OPEN v_cursor FOR SELECT employee_name FROM employees WHERE department_id = 20;
-- Fetch and print employee names in a loop  
LOOP  
    FETCH v_cursor INTO v_name;  
    EXIT WHEN v_cursor%NOTFOUND;  
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);  
END LOOP;  

-- Close the cursor  
CLOSE v_cursor;  
END;

When do we use a REF cursor = 
1.	When Query Results Need to be Dynamic
o	Unlike regular cursors, REF cursors can be associated with different SELECT statements dynamically.
2.	When Returning Query Results to a Client Application
o	Used in Oracle Forms, Reports, or Java applications to fetch results dynamically.
3.	When Passing Query Results Between Procedures or Functions
o	REF cursors can be passed as parameters to other PL/SQL subprograms.
4.	When Handling Large Data Efficiently
o	REF cursors fetch rows one by one, reducing memory consumption.
DECLARE
   TYPE emp_ref_cursor IS REF CURSOR;  -- Declare REF CURSOR type
   emp_cur emp_ref_cursor;  -- Declare REF CURSOR variable
   v_emp_name employees.name%TYPE;
BEGIN
   -- Open REF CURSOR dynamically
   OPEN emp_cur FOR SELECT name FROM employees WHERE department_id = 10;

   -- Fetch and display records
   LOOP
      FETCH emp_cur INTO v_emp_name;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
   END LOOP;

   -- Close the cursor
   CLOSE emp_cur;
END;
Why Use This?
•	The cursor query is dynamic, meaning it can be modified at runtime.
•	Reduces dependency on fixed query definitions.
Using REF CURSOR as an OUT Parameter in a Procedure
CREATE OR REPLACE PROCEDURE get_employees_by_dept (
   p_dept_id IN NUMBER,
   p_emp_cursor OUT SYS_REFCURSOR  -- REF CURSOR as an OUT parameter
) AS
BEGIN
   OPEN p_emp_cursor FOR
   SELECT employee_id, name, salary FROM employees WHERE department_id = p_dept_id;
END;
Calling the Procedure
DECLARE
   emp_cur SYS_REFCURSOR;
   v_id employees.employee_id%TYPE;
   v_name employees.name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   -- Call the procedure to get employees in department 20
   get_employees_by_dept(20, emp_cur);

   -- Fetch and print results
   LOOP
      FETCH emp_cur INTO v_id, v_name, v_salary;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name || ' - ' || v_salary);
   END LOOP;

   CLOSE emp_cur;
END;
Allows fetching results dynamically from a procedure.
Supports multiple rows, making it ideal for returning large datasets;


How do you handle mutating table errors = 
Mutating table errors occur when a trigger attempts to modify the table it is triggered on. A compound trigger allows breaking trigger execution into multiple phases such as BEFORE, AFTER, and FOR EACH ROW to prevent direct modification issues.
The compound trigger below logs salary changes in the salary_audit table efficiently by collecting data before each row update and performing a bulk insert after the statement, reducing context switches and improving performance.
CREATE OR REPLACE TRIGGER trg_salary_audit
FOR UPDATE OF salary ON employees -- Trigger fires on salary updates
COMPOUND TRIGGER
-- Declare an associative array to store audit records  
TYPE t_salary_audit IS TABLE OF salary_audit%ROWTYPE INDEX BY PLS_INTEGER;  
v_audit_data t_salary_audit;  
v_idx PLS_INTEGER := 0;  

-- Before updating each row, store old and new salary details  
BEFORE EACH ROW IS  
BEGIN  
    v_idx := v_idx + 1;  
    v_audit_data(v_idx).employee_id := :OLD.employee_id;  
    v_audit_data(v_idx).old_salary := :OLD.salary;  
    v_audit_data(v_idx).new_salary := :NEW.salary;  
    v_audit_data(v_idx).change_date := SYSDATE;  
END BEFORE EACH ROW;  

-- After the statement, insert all audit records in bulk  
AFTER STATEMENT IS  
BEGIN  
    FORALL i IN 1..v_idx  
        INSERT INTO salary_audit VALUES v_audit_data(i);  
END AFTER STATEMENT;  
END trg_salary_audit;

What is overloading in PL/SQL = Overloading allows multiple procedures or functions with the same name but different parameters to be defined within a package. This enhances code readability and maintainability by providing multiple ways to perform similar operations.
In the query below, the package body implements two overloaded procedures named update_salary: One increases an employee's salary by a specified amount, while the other sets a new salary with an effective date, updating the employees table accordingly.
-- Create the package specification CREATE OR REPLACE PACKAGE emp_pkg AS
-- Procedure to increment salary by a specified amount
PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER);
-- Overloaded procedure to set a new salary with an effective date  
PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE);
END emp_pkg; /
-- Create the package body CREATE OR REPLACE PACKAGE BODY emp_pkg AS
-- Procedure to increment salary by a specified amount  
PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
BEGIN  
    UPDATE employees  
    SET salary = salary + p_increment  
    WHERE employee_id = p_emp_id;  
END update_salary;  

-- Overloaded procedure to set a new salary with an effective date  
PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE) AS  
BEGIN  
    UPDATE employees  
    SET salary = p_new_salary, last_update = p_effective_date  
    WHERE employee_id = p_emp_id;  
END update_salary;  
END emp_pkg; 

What is a save exception = A SAVE EXCEPTION in PL/SQL refers to a mechanism that allows handling multiple errors without stopping execution when performing bulk operations using FORALL.
By default, when an error occurs in a FORALL statement, the entire operation fails. However, by using SAVE EXCEPTIONS, PL/SQL allows the successful operations to continue and captures the errors separately.
How SAVE EXCEPTION Works
1.	When using FORALL for bulk DML operations, errors can occur for some rows.
2.	Instead of stopping execution on the first error, we can collect the exceptions without rolling back the entire operation.
3.	SAVE EXCEPTIONS stores errors in the SQL%BULK_EXCEPTIONS collection.
Example of SAVE EXCEPTION in PL/SQL
Let's say we need to update multiple employee salaries in bulk, but some employee IDs might not exist.
DECLARE
   TYPE emp_id_list IS TABLE OF employees.employee_id%TYPE;
   v_emp_ids emp_id_list := emp_id_list(101, 102, 999, 104, 888); -- IDs 999 and 888 do not exist
   errors_count NUMBER;
BEGIN
   FORALL i IN v_emp_ids.FIRST..v_emp_ids.LAST SAVE EXCEPTIONS
      UPDATE employees
      SET salary = salary * 1.10
      WHERE employee_id = v_emp_ids(i);
   
   DBMS_OUTPUT.PUT_LINE('All updates successful!');
EXCEPTION
   WHEN OTHERS THEN
      errors_count := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.PUT_LINE('Total Errors: ' || errors_count);
      
      FOR i IN 1..errors_count LOOP
         DBMS_OUTPUT.PUT_LINE('Error in record ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || 
                              ' - Error Code: ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
      END LOOP;
END;

Explanation
1.	We define a PL/SQL collection (emp_id_list) containing multiple employee IDs.
2.	FORALL executes an UPDATE statement for all IDs.
3.	If an ID does not exist, it triggers an error but does not stop execution.
4.	The SAVE EXCEPTIONS clause stores the errors in SQL%BULK_EXCEPTIONS.
5.	In the EXCEPTION block:
We count the number of errors.
We loop through SQL%BULK_EXCEPTIONS to print error details.
Total Errors: 2
Error in record 3 - Error Code: 1403
Error in record 5 - Error Code: 1403
Here, 1403 refers to the NO_DATA_FOUND error because employee IDs 999 and 888 do not exist;

What are the compiler directives and pragmas in PL/SQL = PL/SQL provides compiler directives (PRAGMA) to optimize code and handle exceptions. The common pragmas include:
•	PRAGMA EXCEPTION_INIT: Associates a user-defined exception with an Oracle error code.
•	PRAGMA SERIALLY_REUSABLE: Optimizes package memory usage for scalability.
The PL/SQL block below handles the insertion of an employee with an invalid salary by using a custom exception e_invalid_salary mapped to error code -20001. If the exception is raised, it prints an error message.
DECLARE
e_invalid_salary EXCEPTION; -- Declare custom exception for invalid salary
PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001); -- Associate exception with error code -20001
BEGIN
-- Attempt to insert an employee with invalid salary
INSERT INTO employees (employee_id, salary) VALUES (999, -1000);
EXCEPTION
-- Handle the custom exception and print a message
WHEN e_invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Invalid salary detected!');
END;

What are the different methods to trace and debug PL/SQL code = PL/SQL provides several built-in packages for tracing and debugging code performance. Common methods include using DBMS_TRACE to track execution flow, DBMS_APPLICATION_INFO to monitor session activity, and DBMS_SESSION to gather session-level diagnostic information.
The query below first enables SQL tracing using DBMS_SESSION.set_sql_trace(TRUE), then sets client-specific session information with DBMS_APPLICATION_INFO.set_client_info() for monitoring. The PL/SQL block is executed, which simulates an UPDATE operation. Finally, it disables the tracing after the execution using DBMS_SESSION.set_sql_trace(FALSE).
-- Enable tracing for the current session using DBMS_SESSION BEGIN -- Start session-level tracing DBMS_SESSION.set_sql_trace(TRUE); END; 
-- Set application information using DBMS_APPLICATION_INFO BEGIN -- Set the application name and action for session monitoring DBMS_APPLICATION_INFO.set_client_info('Trace Debug Session'); DBMS_APPLICATION_INFO.set_action('Debugging PL/SQL Code'); END; 
-- Example PL/SQL block that simulates a process for debugging DECLARE v_employee_id NUMBER := 100; BEGIN -- Example query to fetch employee details FOR rec IN (SELECT first_name, last_name FROM employees WHERE employee_id = v_employee_id) LOOP DBMS_OUTPUT.put_line('Employee: ' || rec.first_name || ' ' || rec.last_name); END LOOP;
-- Simulate some logic that could be traced IF v_employee_id = 100 THEN DBMS_OUTPUT.put_line('Employee ID is 100'); END IF; END;
-- Disable tracing after the session is complete BEGIN -- Stop session-level tracing DBMS_SESSION.set_sql_trace(FALSE); END; 

How do you design triggers in heavily concurrent environments = 
Assume a scenario where you need to enforce a business rule where an employee’s salary cannot be updated more than once per day. However, the database experiences high transaction concurrency, and a simple trigger could lead to contention or performance issues.
Instead of using a row-level trigger that fires for every update and can slow down performance, use a statement-level trigger with a log table to prevent multiple salary updates within the same day.
For example, the trigger below prevents multiple salary updates for an employee on the same day by checking the salary_update_log table before allowing an update. If the salary has already been updated today, an error is raised; otherwise, the update date is logged.
-- Create a table to log the last salary update for each employee CREATE TABLE salary_update_log ( employee_id NUMBER PRIMARY KEY, -- Employee ID as primary key last_update DATE -- Date of the last salary update );
-- Create or replace a compound trigger to prevent multiple salary updates on the same day CREATE OR REPLACE TRIGGER trg_prevent_multiple_salary_update
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
-- Declare a variable to store last salary update date per row
TYPE emp_log_type IS TABLE OF DATE INDEX BY PLS_INTEGER;
emp_log emp_log_type;

BEFORE STATEMENT IS  
BEGIN  
    -- Load existing salary update logs into memory for reference
    FOR rec IN (SELECT employee_id, last_update FROM salary_update_log) LOOP  
        emp_log(rec.employee_id) := rec.last_update;  
    END LOOP;  
END BEFORE STATEMENT;  

BEFORE EACH ROW IS  
BEGIN  
    -- Check if an entry exists for this employee
    IF emp_log.EXISTS(:NEW.employee_id) THEN  
        -- Validate if salary was updated today
        IF emp_log(:NEW.employee_id) = TRUNC(SYSDATE) THEN  
            RAISE_APPLICATION_ERROR(-20010, 'Salary can only be updated once per day');  
        ELSE  
            -- Update log in memory
            emp_log(:NEW.employee_id) := TRUNC(SYSDATE);  
        END IF;  
    ELSE  
        -- Insert a new log entry into memory
        emp_log(:NEW.employee_id) := TRUNC(SYSDATE);  
    END IF;  
END BEFORE EACH ROW;  

AFTER STATEMENT IS  
BEGIN  
    -- Apply changes to the log table after all row updates
    FOR i IN emp_log.FIRST .. emp_log.LAST LOOP  
        MERGE INTO salary_update_log l  
        USING (SELECT i AS employee_id, emp_log(i) AS last_update FROM DUAL) s  
        ON (l.employee_id = s.employee_id)  
        WHEN MATCHED THEN  
            UPDATE SET l.last_update = s.last_update  
        WHEN NOT MATCHED THEN  
            INSERT (employee_id, last_update) VALUES (s.employee_id, s.last_update);  
    END LOOP;  
END AFTER STATEMENT;  
END trg_prevent_multiple_salary_update;

How do you split large transactions into smaller chunks for reliability = 
Imagine a scenario where a banking system requires a bulk update to adjust interest rates for millions of customer accounts. Executing a single large transaction might lock tables for too long or lead to rollback failures.
For this case, use bulk processing with COMMIT in batches to process the data incrementally and avoid contention.
The PL/SQL block below implements the solution by using BULK COLLECT to fetch account IDs and then iterates through them to update the interest rate by 5%. It commits the changes after every 1000 updates to improve performance and reduce resource usage. A final commit ensures any remaining updates are saved.
DECLARE  
    -- Define a collection type for account IDs  
    TYPE t_accounts IS TABLE OF NUMBER;  -- Use NUMBER instead of referencing accounts.account_id
    v_account_ids t_accounts;  -- Variable to store account IDs  
    v_batch_size CONSTANT NUMBER := 1000;  -- Batch size for commits  

BEGIN  
    -- Bulk collect all account IDs into the collection  
    SELECT account_id BULK COLLECT INTO v_account_ids FROM accounts;  

    -- Loop through each account ID to update the interest rate  
    FOR i IN 1 .. v_account_ids.COUNT LOOP  
        UPDATE accounts  
        SET interest_rate = interest_rate * 1.05  -- Increase interest rate by 5%  
        WHERE account_id = v_account_ids(i);  

        -- Commit after every 1000 updates  
        IF MOD(i, v_batch_size) = 0 THEN  
            COMMIT;  
        END IF;  
    END LOOP;  

    COMMIT; -- Final commit for any remaining updates  
END;

How do you organize complex logic into packages for maintainability = 
A retail company needs to implement an order processing system that involves multiple steps: Checking inventory availability, reserving stock, calculating discounts, and logging transaction history.
Instead of writing separate standalone procedures, you need to organize this logic in a structured, maintainable way. Therefore, use PL/SQL packages to encapsulate related procedures and functions, improving code reusability and maintainability
The package specification below defines functions and procedures for order processing, including checking inventory, reserving stock, calculating discounts, and logging transaction statuses. It provides a modular approach to handling order-related tasks.
-- Create a package specification for order processing functions and procedures 

CREATE PACKAGE order_processing_pkg AS
-- Function to check if enough inventory is available for the product
FUNCTION check_inventory(p_product_id NUMBER, p_quantity NUMBER) RETURN BOOLEAN;  

-- Procedure to reserve stock for a specific order
PROCEDURE reserve_stock(p_order_id NUMBER, p_product_id NUMBER, p_quantity NUMBER);  

-- Function to calculate discount based on customer and total amount
FUNCTION calculate_discount(p_customer_id NUMBER, p_total_amount NUMBER) RETURN NUMBER;  

-- Procedure to log the status of a transaction
PROCEDURE log_transaction(p_order_id NUMBER, p_status VARCHAR2);  
END order_processing_pkg;
-- Create the package body implementing the functions and procedures for order processing CREATE PACKAGE BODY order_processing_pkg AS
-- Function to check if sufficient inventory is available for the product
FUNCTION check_inventory(p_product_id NUMBER, p_quantity NUMBER) RETURN BOOLEAN AS  
    v_available_qty NUMBER;  -- Variable to store available quantity  
BEGIN  
    -- Retrieve the available quantity from inventory
    SELECT stock_quantity INTO v_available_qty FROM inventory WHERE product_id = p_product_id;  
    -- Return true if enough stock is available, otherwise false
    RETURN v_available_qty >= p_quantity;  
END check_inventory;  

-- Procedure to reserve stock for a specific order
PROCEDURE reserve_stock(p_order_id NUMBER, p_product_id NUMBER, p_quantity NUMBER) AS  
BEGIN  
    -- Deduct the ordered quantity from the inventory
    UPDATE inventory SET stock_quantity = stock_quantity - p_quantity WHERE product_id = p_product_id;  
END reserve_stock;  

-- Function to calculate a discount based on the total order amount
FUNCTION calculate_discount(p_customer_id NUMBER, p_total_amount NUMBER) RETURN NUMBER AS  
    v_discount NUMBER := 0;  -- Initialize discount to 0  
BEGIN  
    -- Apply 10% discount if the total amount is greater than 500
    IF p_total_amount > 500 THEN  
        v_discount := p_total_amount * 0.10;  
    END IF;  
    -- Return the calculated discount
    RETURN v_discount;  
END calculate_discount;  

-- Procedure to log the transaction status
PROCEDURE log_transaction(p_order_id NUMBER, p_status VARCHAR2) AS  
BEGIN  
    -- Insert a log entry for the order status
    INSERT INTO order_log (order_id, status, log_date) VALUES (p_order_id, p_status, SYSDATE);  
END log_transaction;  
END order_processing_pkg;

How do you handle deadlocks in high-transaction systems = Assume a scenario where a financial system frequently updates multiple related tables simultaneously. Deadlocks occur when two transactions wait on each other’s locked resources, causing performance bottlenecks.
To solve this problem, always lock rows in a consistent order across transactions. Also, use the NOWAIT or SKIP LOCKED clause to prevent waiting indefinitely.
For example, the PL/SQL block below attempts to lock a specific row in the accounts table for the update using the FOR UPDATE NOWAIT clause, which causes the transaction to fail immediately if another session already locks the row. After locking, it updates the transaction status and commits the changes. If an error occurs, it catches the exception and prints an error message.
DECLARE
v_balance NUMBER(15,2); -- Declare variable to store the account balance
BEGIN
-- Lock the account row for update to prevent other sessions from modifying it
SELECT balance INTO v_balance FROM accounts
WHERE account_id = 101 FOR UPDATE NOWAIT;
-- Update the transaction status to 'Processed' for all transactions related to this account  
UPDATE transactions  
SET status = 'Processed'  
WHERE account_id = 101 AND status = 'Pending';  -- Update only pending transactions  

COMMIT;  -- Commit the changes  
EXCEPTION
-- Handle errors, such as locking issues or unexpected exceptions
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Transaction failed: ' || SQLERRM);
ROLLBACK; -- Ensure rollback in case of failure
END;

Similarly, this PL/SQL block processes pending transactions by locking them for update with FOR UPDATE SKIP LOCKED, allowing it to skip over rows that are already locked by other sessions. It updates each transaction's status to 'Processing' and commits the changes at the end.
DECLARE
-- Declare a cursor to select pending transactions and lock rows for update CURSOR c_pending_txns IS
SELECT transaction_id FROM transactions WHERE status = 'Pending' FOR UPDATE SKIP LOCKED; -- Skip locked rows
BEGIN
-- Loop through the pending transactions FOR txn IN c_pending_txns LOOP
-- Update the status of each transaction to 'Processing' UPDATE transactions SET status = 'Processing' WHERE transaction_id = txn.transaction_id;
END LOOP;
COMMIT; -- Commit the changes to finalize the transaction updates
END;


What is a Virtual Column in Oracle = 
A Virtual Column is a computed column that does not store data physically but derives its value dynamically from an expression or function. Virtual columns reduce storage requirements and simplify queries by avoiding the need to repeatedly compute derived values.
Key Features of Virtual Columns
 Stored as metadata only – No physical storage.
 Automatically computed when queried.
 Can be indexed for performance improvement.
 Cannot be updated directly (as values are computed).
 Can use expressions, functions, and other columns to derive values.
CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   salary NUMBER,
   annual_salary AS (salary * 12)   -- Virtual Column
);

SELECT employee_id, salary, annual_salary FROM employees;
Using Virtual Columns in Indexing
Virtual columns can be indexed to improve query performance.
CREATE INDEX idx_annual_salary ON employees(annual_salary);

Using a Function in a Virtual Column
You can also use built-in functions:
CREATE TABLE students (
   student_id NUMBER PRIMARY KEY,
   full_name VARCHAR2(100),
   name_upper AS (UPPER(full_name)) -- Virtual column with function
);

What are the differences between syntax and runtime errors = 
Syntax Errors:
•	What: Violates language rules (grammar).
•	When: Detected before program runs (compilation).
•	Example: Misspelled keyword, missing semicolon.
•	Concise: Code doesn't follow language's structure.
Runtime Errors:
•	What: Occurs during program execution.
•	When: Detected while program is running.
•	Example: Dividing by zero, accessing a non-existent file.
•	Concise: Code runs, but encounters problems;

What are the differences between ROLLBACK and ROLLBACK TO statements in PL/SQL = 
•	The ROLLBACK command is used to undo any modification made since the transaction’s start.
•	The transaction may only be rolling back using the ROLLBACK TO command up to a SAVEPOINT. The transaction stays active even before the command is provided since the transactions cannot be rolled back before the SAVEPOINT;

What is the purpose of SYSDATE and USER keywords = Give information about date is SYSDATE and USER will going to show the connected user;

What are the uses of SYS.ALL_DEPENDENCIES = The dependencies between all the procedures, packages, triggers, and functions that the current user can access are described by SYS.ALL_DEPENDENCIES;

What is the main difference between a mutating table and a constraining table = A table that can be changed using a DML statement or one with triggers defined is said to be a mutating table. The table that is read for a referential integrity constraint is referred to as a constraining table;

Differentiate between %ROWTYPE and %TYPE = %ROWTYPE: It is used to declare a variable that has the structure of the records in a table.
%TYPE: To declare a column in a table that contains the value of that column, use the %TYPE property. The variable’s data type and the table’s column are the same;

Discuss SQLERRM and SQLCODE. What is the importance of PL/SQL = 
•	SQLCODE returns the error number for the most recent error found.
•	SQLERRM returns the error message for the most recent error.
SQLCODE and SQLERRM can be used in exception handling in PL/SQL to report the error that happened in the code in the error log database;

Explain three basic parts of a trigger in PL/SQL = 
This is a straightforward question that shouldn’t require much thought. If candidates have experience with PL/SQL, they will understand how triggers function in this programming language. 
Below are the three basic parts of a trigger in PL/SQL: 
A triggering statement or event
A restriction
An action
Consider whether your applicants can explain why each of these parts is important before you hire them;

Explain the daily activities of a PL/SQL developer. = Developers complete many tasks using the PL/SQL language. The specific activities they work on depend on their programming language skills and objectives. Experienced candidates should fully understand the job role and the responsibilities it entails. Make a note of each response to determine which candidate has the most knowledge. 
Here are some of a PL/SQL developer’s daily activities: 
Create database objects, tables, statements, and sequences 
Implement procedures and functions in a program 
Declare business constraints and resolve triggers 
Create cursors for data manipulation 
Experiment with different cursor attributes 
Support arrays using PL/SQL collections
Develop applications for a programming language strategy;



What programming constructs does PL/SQL support = Candidates should know what features and constructs PL/SQL supports. The more experience they have, the more capable they will be of completing day-to-day activities. If the candidate doesn’t understand these features, they may not have enough knowledge of programming in PL/SQL.
Below are some constructs that PL/SQL supports: 
Variables and constants
Triggers
Cursor attribute management
Stored procedures and packaging
SQL support 
Flow control 
Exception management 
Loops, statements, and assignments 
Object-oriented programming
What are the various packages available for PL-SQL Developers?
The several packages available for PL/SQL developers are:

DBMS_ALERT	alert an application using triggers when particular database values change. The alerts are transaction-based and asynchronous.
DBMS_OUTPUT	display output from PL/SQL blocks, packages, subprograms and triggers. Mostly used for displaying PL/SQL debugging information.
DBMS_PIPE	different sessions communicate over named pipes using this package. The procedures PACK_MESSAGE and SEND_MESSAGE pack a message into a pipe, then send it to another session.
HTF and HTP	allow PL/SQL programs to generate HTML tags.
UTL_FILE	lets PL/SQL programs read and write OS text files.
UTL_HTTP	allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in HTML format.
UTL_SMTP	allows PL/SQL programs to send emails over SMTP;


What is the difference between SGA and PGA = 
SGA	PGA
System Global Area	Program Global Area
Contains data and control information for one Oracle database instance	Contains data and control information exclusively for a single Oracle process
Shared memory region for components	Non-shared memory region
example: cached data blocks and SQL areas	Example: session memory, SQL work area;

Give a simple way to run a query faster = Answer: By using ROWID. It is not a physical column but the logical address of a row. It contains the block number, file number and row number thereby reducing I/O time hence making query execution faster


1. TRCSESS (Trace Session)
TRCSESS is a tool used to process and convert raw trace files generated by Oracle's SQL trace or Oracle Net tracing into a more readable format. It helps in consolidating multiple trace files into a single trace file for easier analysis.
Purpose of TRCSESS:
•	Consolidates trace files: If you have multiple trace files generated for a session, TRCSESS can combine them into a single file.
•	Filters trace information: It allows you to filter specific trace information based on session IDs, timestamps, etc., making the data easier to analyze.
•	Makes raw trace data readable: The output generated by TRCSESS is a more human-readable format that simplifies performance analysis.
How TRCSESS Works:
•	It processes Oracle trace files (created by enabling SQL_TRACE or using 10046 tracing).
•	It reads the trace files, extracts relevant information, and formats it in a more readable way.
•	You can specify various parameters such as the output file name, session IDs, timestamp ranges, etc.
trcess -o output_file.trc input_trace_file.trc
Common Use Cases:
•	Analyzing SQL performance: By consolidating trace files from a session, you can understand the sequence of SQL statements and their execution times.
•	Identifying bottlenecks: TRCSESS helps in identifying the slowest operations in a trace file.
2. TKPROF (Trace and Profiling Tool)
TKPROF is an Oracle utility that formats and analyzes SQL trace files generated by the database, providing detailed information about query performance, such as execution time, wait events, and resource usage. It helps DBAs and developers optimize SQL queries by giving insight into how each query performs in the database.
Purpose of TKPROF:
•	Formats trace files: It converts raw trace data into a readable report that is easier to analyze.
•	Provides performance metrics: It reports on the performance of SQL queries, showing how long each query took, how many rows were returned, and other valuable information.
•	Identifies expensive SQL statements: Helps in finding the most resource-intensive queries that need optimization.
How TKPROF Works:
•	It reads trace files generated by SQL trace (SQL_TRACE or 10046 tracing).
•	It produces a report that includes execution times, parse times, wait events, SQL execution plans, and other relevant performance metrics.
Example Command:

tkprof input_trace_file.trc output_report_file.prf
This command generates a performance report that shows the execution details of SQL queries in a readable format.
TKPROF Output Example:
The output of TKPROF includes the following key sections:
•	SQL Statements: Lists all SQL statements executed during the traced session.
•	Execution Times: Shows the time spent on parsing, executing, and fetching results for each query.
•	Wait Events: Displays the wait events encountered by the database during query execution (e.g., disk I/O, locks).
•	Rows Processed: Shows the number of rows affected by each SQL query.
Common Use Cases:
•	SQL Query Optimization: TKPROF helps in identifying slow-running SQL queries by analyzing their execution time.
•	Performance Tuning: It highlights which queries are consuming the most resources, helping DBAs to focus on the most critical performance issues.
•	Investigating Errors and Bottlenecks: It is used to analyze slow sessions, understand execution delays, and investigate issues related to database performance.
Key Differences Between TRCSESS and TKPROF
Feature	TRCSESS	TKPROF
Purpose	Converts raw trace files into a readable format, consolidates multiple trace files.	Analyzes trace files and generates a performance report.
Input	Oracle trace files generated by SQL trace.	Raw trace files, usually from SQL trace or 10046 trace.
Output	A readable, consolidated trace file.	A performance report showing SQL execution details.
Main Use	Simplifies trace file analysis by merging and formatting trace data.	Optimizes SQL queries by providing detailed performance metrics.
Common Users	DBAs and Developers for consolidating and reviewing trace data.	DBAs and Developers for performance tuning and analysis.
Conclusion
Both TRCSESS and TKPROF are essential tools in Oracle's performance tuning toolkit:
•	TRCSESS is primarily used for consolidating, filtering, and making raw trace files more readable.
•	TKPROF takes raw trace data and provides a detailed performance analysis to help optimize SQL queries and diagnose bottlenecks.
Together, these tools can assist you in identifying, analyzing, and fixing performance problems in your Oracle database, ultimately leading to improved performance and efficiency;

What is Bulk Bind = 
Bulk Bind in PL/SQL refers to the process of binding multiple values to a collection (such as a PL/SQL array) at once, rather than processing them individually in a loop. This method can greatly improve performance, especially when dealing with large volumes of data, because it minimizes context switches between the PL/SQL engine and SQL engine.
In traditional PL/SQL programming, you would loop over a collection and process each element one by one. With Bulk Bind, the entire collection is passed to the SQL engine in a single operation, which reduces the number of context switches and enhances performance;

When to Use Bulk Bind = 
•	For processing multiple rows of data: It’s ideal when you need to process or manipulate large sets of data, as it can handle large data volumes more efficiently than traditional methods.
•	When you need to perform bulk operations (such as insert, update, or delete) on data within a collection.
How Does Bulk Bind Work?
There are two main methods for performing bulk operations in PL/SQL using bulk bind:
1.	BULK COLLECT: Used to fetch multiple rows from a SQL query into a collection in one operation.
2.	FORALL: Used to execute DML (Data Manipulation Language) statements (insert, update, delete) for multiple rows in one operation.
Example 1: BULK COLLECT
BULK COLLECT is used to fetch multiple rows into a PL/SQL collection such as a VARRAY or nested table.
DECLARE
   TYPE emp_table IS TABLE OF employees%ROWTYPE;
   v_emp emp_table;
BEGIN
   -- Use BULK COLLECT to fetch multiple rows at once
   SELECT * BULK COLLECT INTO v_emp FROM employees WHERE department_id = 10;

   -- Process the fetched rows
   FOR i IN 1..v_emp.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_emp(i).employee_id || ' - ' || v_emp(i).first_name);
   END LOOP;
END;
In this example, BULK COLLECT fetches all the rows from the employees table where the department_id is 10, into the v_emp collection in a single operation.
Example 2: FORALL
FORALL is used for performing bulk DML operations (like insert, update, or delete) on a collection. It reduces the overhead of performing one DML operation for each row by executing them in bulk.
DECLARE
   TYPE emp_ids IS TABLE OF NUMBER;
   v_ids emp_ids := emp_ids(101, 102, 103);
BEGIN
   -- Use FORALL to perform a bulk DELETE operation
   FORALL i IN v_ids.FIRST..v_ids.LAST
      DELETE FROM employees WHERE employee_id = v_ids(i);
END;
Here, FORALL allows for the deletion of multiple rows in the employees table in one bulk operation, reducing the context switches between PL/SQL and SQL.
Benefits of Using Bulk Bind:
1.	Improved Performance: Bulk operations reduce context switching between PL/SQL and SQL engines, which improves the overall performance, especially when processing large volumes of data.
2.	Efficient Memory Management: Bulk collections help in better memory usage by reducing the overhead of context switches and allowing more data to be loaded or processed at once.
3.	Simplified Code: Reduces the need for looping and repetitive DML operations, simplifying your code and making it more readable.
Considerations:
•	Bulk operations require careful handling of large data sets to avoid running out of memory.
•	Be mindful of the size of the collection: Too large a collection can cause memory issues, so it’s a good practice to limit the collection size when working with massive data sets.
•	%FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN attributes are not available for bulk operations. These attributes work with cursors, but for bulk operations, you will need to manage row counts and checks manually.

Difference Between a Nested Table and a VARRAY = 
In PL/SQL, both Nested Tables and VARRAYs are used to store collections of data, but they have distinct characteristics and use cases. Here's a detailed comparison between the two:
1. Definition:
•	Nested Table:
o	A nested table is a collection that allows for the storage of an arbitrary number of elements. It is not limited by a predefined size and can be sparse (i.e., it can have non-contiguous indices).
o	Nested tables are like arrays but are stored in database tables and can be stored as a column in a table.
•	VARRAY (Variable-Size Array):
o	A VARRAY is a collection that stores elements in a contiguous block of memory. It has a fixed, predefined size limit that is set when the VARRAY is defined.
o	VARRAYs are more like traditional arrays with a fixed maximum size.
2. Size Limit:
•	Nested Table:
o	There is no size limit on a nested table. It can store any number of elements, depending on the available memory and resources.
o	The size is dynamic and grows or shrinks as needed.
•	VARRAY:
o	A VARRAY has a fixed size when it is defined. The maximum number of elements is specified during the creation of the VARRAY type.
o	Once the size is defined, it cannot exceed that limit.
3. Memory Storage:
•	Nested Table:
o	A nested table is stored outside of the PL/SQL program (in the database). It is stored as a separate table or object.
o	Nested tables can be sparse (they can have gaps in the index), which means that some elements may not be contiguous.
•	VARRAY:
o	A VARRAY is stored contiguously in memory, and it retains its data structure within the PL/SQL program.
o	All elements are stored in a contiguous block of memory.
4. Indexing:
•	Nested Table:
o	Indexed by a primary key or subscript. The indices in a nested table can be non-contiguous, meaning some indices can be skipped.
•	VARRAY:
o	Indexed by a contiguous integer range starting from 1. It stores elements in a contiguous range of indices.
5. Use Cases:
•	Nested Table:
o	Ideal for handling large datasets where you need flexibility in adding or removing elements.
o	Useful when you want to store sparse data or sets of data that may grow or shrink dynamically over time.
•	VARRAY:
o	Best suited for scenarios where the size is fixed, or when you want to store small collections with a limited number of elements.
o	Suitable for use cases where the collection's size is known ahead of time and you need a contiguous structure.
6. Data Type:
•	Nested Table:
o	A nested table is created from a collection type, which can be a record or an object type.
o	Example:
CREATE TYPE my_nested_table AS TABLE OF VARCHAR2(100);
•	VARRAY:
o	A VARRAY is also created from a collection type but with a maximum size limit specified.
o	Example:
CREATE TYPE my_varray AS VARRAY(5) OF VARCHAR2(100);
7. Operations:
•	Nested Table:
o	Can be modified (insert, delete, update) using standard DML operations in SQL.
o	Can be stored in a database table column.
o	Supports SQL queries for retrieval.
•	VARRAY:
o	VARRAY elements can be inserted, but because they are contiguous, operations like insertion or deletion are less efficient compared to nested tables.
o	Typically used in-memory and is rarely stored directly in the database.
8. Example:
Nested Table Example:
-- Define a nested table type
CREATE TYPE emp_names_nt AS TABLE OF VARCHAR2(100);

DECLARE
   -- Declare a variable of the nested table type
   v_emp_names emp_names_nt;
BEGIN
   -- Initialize the nested table
   v_emp_names := emp_names_nt('Alice', 'Bob', 'Charlie');

   -- Add another employee to the nested table
   v_emp_names.EXTEND;
   v_emp_names(4) := 'David';

   -- Print employee names
   FOR i IN 1..v_emp_names.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_emp_names(i));
   END LOOP;
END;
VARRAY Example:
-- Define a VARRAY type with a maximum size of 3
CREATE TYPE emp_names_varray AS VARRAY(3) OF VARCHAR2(100);

DECLARE
   -- Declare a variable of the VARRAY type
   v_emp_names emp_names_varray := emp_names_varray('Alice', 'Bob', 'Charlie');
BEGIN
   -- Print employee names
   FOR i IN 1..v_emp_names.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_emp_names(i));
   END LOOP;
END;
9. Performance:
•	Nested Table:
o	Since it can dynamically grow, nested tables are more flexible but may consume more memory and resources when dealing with large data.
o	They are more efficient for large data manipulations and can be indexed for better performance in queries.
•	VARRAY:
o	VARRAYs are more memory-efficient and perform better with small datasets. However, they are less flexible due to their fixed size.
o	As the size grows, the performance of VARRAY operations may degrade because elements are stored contiguously.
Summary of Differences:
Feature	Nested Table	VARRAY
Size	No fixed size, can grow dynamically	Fixed size (predefined max size)
Storage	Stored outside in a table	Stored in contiguous memory block
Indexing	Non-contiguous indices	Contiguous indices
Use Case	Large datasets, flexible size	Small, fixed collections
Performance	More flexible, but may consume more memory for large data	More memory-efficient for small collections
Operations	Insert, delete, update are possible	Limited operations (fixed size)
Typical Use	Dynamic data, large sets	Small, known collections;

How do you access the fields of a record in PL/SQL = 
 Define a record type with multiple fields
    TYPE emp_record_type IS RECORD (
        emp_id   NUMBER,
        emp_name VARCHAR2(50),
        salary   NUMBER
    );

    -- Declare a variable of the record type
    v_emp emp_record_type;
--- Accessing field
v_emp.<field_name>;

What is the overloading of a procedure = Same concept define above for package same procedure name with different parameters;

What is the difference between temporary and permanent tablespaces = 
•  Temporary Tablespace:
A temporary tablespace is used for storing temporary data created during SQL operations, such as sorting and joining operations. Temporary tablespaces are primarily used for storing intermediate data and are automatically cleaned up when the session ends or the operation completes. They are not used for permanent storage of database objects like tables, indexes, or views.
•  Permanent Tablespace:
A permanent tablespace, on the other hand, is used for storing permanent data such as tables, indexes, and other database objects that need to persist. This is where most of the database's actual data is stored. Data in permanent tablespaces is not automatically deleted or cleaned up; it is retained for the life of the database unless explicitly removed by the user;

What is an autonomous transaction = 
PRAGMA AUTONOMOUS_TRANSACTION is a compiler directive in PL/SQL that allows a block (procedure, function, trigger, or anonymous block) to act independently of the main transaction. This means that the autonomous transaction can commit or roll back changes without affecting the main transaction.
CREATE OR REPLACE PROCEDURE log_error(p_err_msg VARCHAR2) 
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_log (error_message, log_date) 
    VALUES (p_err_msg, SYSDATE);
    COMMIT;  -- Required to make changes permanent in autonomous transaction
END;
Key Features:
1.	Independent Transaction:
2.	Useful in Logging and Auditing:
3.	Commit or Rollback Required:
4.	Execution Context:
Understanding "Main" and "Independent" Transactions in PRAGMA AUTONOMOUS_TRANSACTION
In PL/SQL, transactions control database changes with COMMIT or ROLLBACK. When you use PRAGMA AUTONOMOUS_TRANSACTION, you create a separate transaction that works independently of the main transaction.
1. Main Transaction
•	The transaction that starts when a PL/SQL block executes.
•	It is affected by COMMIT or ROLLBACK at the end of the session or explicitly inside the block.
•	Changes made in this transaction are pending until committed.
2. Independent (Autonomous) Transaction
•	A separate transaction that runs inside the main transaction but does not depend on it.
•	It can COMMIT or ROLLBACK its changes independently without affecting the main transaction.
•	Used mainly in logging, auditing, and error handling.
Example to Demonstrate the Difference
Scenario:
A company wants to log every transaction update in an audit_log table. If the update fails, the audit log should still be recorded.
Implementation:
Main Transaction (Update Employee Salary)
BEGIN
    UPDATE employees 
    SET salary = salary + 1000
    WHERE employee_id = 101;
    -- This is the main transaction
    -- If the update fails, no changes happen unless committed
END;
Independent (Autonomous) Transaction (Logging the Action)
CREATE OR REPLACE PROCEDURE log_action(p_message VARCHAR2) 
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO audit_log (message, log_time) 
    VALUES (p_message, SYSDATE);
    COMMIT; -- Independent commit, ensuring log is saved even if main transaction rolls back
END;
Using Both Together
BEGIN
    UPDATE employees 
    SET salary = salary + 1000
    WHERE employee_id = 101;
    
    -- Call the logging procedure (this runs in an independent transaction)
    log_action('Salary updated for Employee 101');

    -- ROLLBACK the main transaction
    ROLLBACK; -- The salary update is undone, but the log remains!

END;
Results:
1.	Main Transaction Rolled Back:
Employee's salary remains unchanged.
2.	Independent Transaction Committed:
Log entry in audit_log is saved permanently.

Check in USER_SOURCE (For Current User’s Objects)
You can search for all occurrences of PRAGMA AUTONOMOUS_TRANSACTION in your stored procedures, functions, packages, or triggers.
SELECT name, type, line, text
FROM user_source
WHERE UPPER(text) LIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'
ORDER BY name, line;

Other sources are DBA Source, Trigger source, all source etc depends on rights you hold;

What is the role of a hierarchical profiler = The PL/SQL Hierarchical Profiler (DBMS_HPROF) is a built-in tool in Oracle used to analyze and optimize PL/SQL code performance. It provides a hierarchical breakdown of execution times, helping developers identify bottlenecks, inefficient code, and performance issues.
Key Features of Hierarchical Profiler:
1.	Identifies Slow Code Segments – Helps find slow-performing procedures, functions, and SQL queries.
2.	Call Tree Analysis – Shows the hierarchy of calls, i.e., which functions or procedures are calling other subprograms.
3.	Tracks Execution Time – Provides execution time details for each subprogram (inclusive and exclusive execution times).
4.	Helps Optimize Code – By analyzing the profiling results, developers can rewrite inefficient code for better performance.
5.	Stored in Database Tables – The profiler stores profiling results in database tables for further analysis;

How to Use the Hierarchical Profiler = 
Step 1: Enable Profiling in PL/SQL Code
You need to start profiling before running your PL/SQL block.
EXEC DBMS_HPROF.START_PROFILING(location => 'PLSQL_PROF_DIR', filename => 'plsql_profiler.trc');
Step 2: Run Your PL/SQL Code
BEGIN
   -- Call procedures or functions that you want to profile
   update_salary(101, 5000);
   process_orders;
END;
Step 3: Stop Profiling
EXEC DBMS_HPROF.STOP_PROFILING;
Step 4: View Profiling Data
After running the profiler, you can analyze results using the DBMS_HPROF.ANALYZE function or querying profiling tables.
SELECT * FROM DBMS_HPROF.SELF_RUNS;
You can also extract details such as:
•	Time spent in each function
•	Number of calls to each procedure
•	Execution hierarchy;

What is the difference between a Function and a Procedure = 
Feature	Function	Procedure
Return Type	Returns a value	Does not return a value
Usage	Used in SQL statements	Used for complex operations
Example	SELECT get_salary(101) FROM dual;	CALL update_salary(101, 5000);

Compare SQL and PL/SQL = 
Feature	SQL	PL/SQL
Type	Declarative	Procedural
Execution	Executes single query at a time	Executes blocks of code
Use	Used for data manipulation	Used for programming logic;


Data Types in PL/SQL = 
4. What data types are available in PL/SQL?
PL/SQL supports:
•	Scalar types (VARCHAR2, NUMBER, DATE, BOOLEAN)
•	Composite types (RECORD, TABLE, VARRAY)
•	Reference types (CURSOR, REF CURSOR)
Example:
DECLARE
   v_number NUMBER := 100;
   v_text   VARCHAR2(50) := 'PL/SQL';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_text || ' ' || v_number);
END;


What are the roles of PLVrb and PLVcmt in PL/SQL = Roles of PLVrb and PLVcmt in PL/SQL
PLVrb (PL/SQL Revert Buffer) and PLVcmt (PL/SQL Commit) are part of the PL/Vision library, a third-party package developed by Steven Feuerstein to enhance PL/SQL programming efficiency. These utilities help developers manage transactions effectively.
1. PLVrb (PL/SQL Revert Buffer)
•	The PLVrb package is used to rollback transactions in a controlled manner.
•	It acts as a buffer that captures rollback points so that you can undo changes selectively.
BEGIN
   PLVrb.savepoint('before_update');  -- Savepoint before update

   UPDATE employees SET salary = salary + 500 WHERE department_id = 10;

   IF salary > 10000 THEN
      PLVrb.rollback;  -- Rolls back to 'before_update'
   END IF;
END;
Key Feature: Allows rolling back to specific points rather than a full rollback.
2. PLVcmt (PL/SQL Commit)
•	The PLVcmt package is used to commit transactions in PL/SQL.
•	It ensures that commits happen in a structured and controlled way rather than blindly committing every change.
Example Usage:
BEGIN
   UPDATE employees SET bonus = bonus + 1000 WHERE job_id = 'MANAGER';

   IF SQL%ROWCOUNT > 0 THEN
      PLVcmt.commit;
   ELSE
      PLVrb.rollback;  -- Rollback if no rows affected
   END IF;
END;
BEGIN
   UPDATE employees SET bonus = bonus + 1000 WHERE job_id = 'MANAGER';

   IF SQL%ROWCOUNT > 0 THEN
      PLVcmt.commit;
   ELSE
      PLVrb.rollback;  -- Rollback if no rows affected
   END IF;
END;
Key Feature: Provides controlled commits based on business logic rather than committing automatically;

Why Use PLVrb and PLVcmt Instead of Oracle Built-ins = 
You're absolutely right that Oracle already provides built-in transaction control statements like COMMIT, ROLLBACK, SAVEPOINT, and ROLLBACK TO SAVEPOINT. However, PLVrb and PLVcmt (from PL/Vision) offer additional flexibility and abstraction over these standard commands. Here’s why they can be useful:
1. Centralized Transaction Control
In large-scale applications, using built-in transaction controls everywhere can make the code harder to manage. Instead, PLVrb and PLVcmt allow you to:
•	Encapsulate transaction logic in reusable procedures.
•	Apply consistent transaction management across the entire application.
📌 Example:
Instead of writing COMMIT and ROLLBACK everywhere, you can simply call PLVcmt.commit; or PLVrb.rollback;.
2. Named Savepoints with Meaningful Names
Oracle’s SAVEPOINT command allows you to create rollback points, but it lacks a standardized naming approach.
PLVrb enables you to manage multiple rollback points more efficiently with descriptive names.
Example:
Using standard Oracle syntax:
SAVEPOINT before_update;
UPDATE employees SET salary = salary + 500;
ROLLBACK TO before_update;
With PLVrb:
PLVrb.savepoint('before_update');
UPDATE employees SET salary = salary + 500
PLVrb.rollback;  -- Rolls back to 'before_update'

3. Avoiding Accidental Commits
In Oracle, a COMMIT is final—once committed, it cannot be undone. Developers sometimes mistakenly commit transactions prematurely, leading to data integrity issues.
PLVcmt helps prevent accidental commits by:
•	Providing an explicit way to commit based on specific logic.
•	Allowing transaction control to be handled in one place, reducing the risk of unintentional commits.
Example:
With standard Oracle syntax:
UPDATE employees SET salary = salary + 500 WHERE department_id = 10;
COMMIT; -- Unintended commit may occur
With PLVcmt:
UPDATE employees SET salary = salary + 500 WHERE department_id = 10;
PLVcmt.commit; -- Controlled commit
Advantage: Allows better control over when commits should happen.
4. Improved Debugging and Logging
When debugging a large PL/SQL application, finding where and why a rollback occurred can be challenging.
PLVrb provides logging mechanisms that help in tracking rollback points.
Example:
PLVrb.savepoint('step1');
UPDATE employees SET salary = salary + 1000 WHERE job_id = 'MANAGER';

IF SQL%ROWCOUNT = 0 THEN
   PLVrb.rollback; -- Debugging rollback
   DBMS_OUTPUT.PUT_LINE('Rollback executed due to zero rows updated');
END IF;

How Does a Sort Merge Join Work = 
A Sort Merge Join consists of two main steps:
1.	Sort Phase
o	Both input datasets (tables) are sorted on the join key.
2.	Merge Phase
o	The sorted datasets are merged together by sequentially scanning and matching the rows.
3.	When Does Oracle Use a Sort Merge Join?
4.	Oracle chooses a Sort Merge Join in these scenarios: ✅ When there is no index on the join columns.
✅ When both tables are large, and a Hash Join is not feasible.
✅ When JOIN conditions involve inequality (>=, <=, BETWEEN), which cannot use a Hash Join.
✅ When the optimizer decides that sorting is cheaper than using an index-based Nested Loop Join.
Example of a Sort Merge Join
Assume we have two tables, employees and departments, and we are joining them on department_id.
Query Using a Sort Merge Join
SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
Forcing a Sort Merge Join (if needed)
To force a Sort Merge Join, use the USE_MERGE hint:
SELECT /*+ USE_MERGE(e d) */
    e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
How to Check If Oracle Used a Sort Merge Join?
Run the EXPLAIN PLAN command:
EXPLAIN PLAN FOR
SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
If a Sort Merge Join was used, you will see something like;
 

What Does the OPEN Cursor Command Do in PL/SQL = 
In PL/SQL, the OPEN command is used to initialize and execute an explicit cursor. When a cursor is opened, Oracle processes the associated SQL query, identifies the result set, and prepares it for fetching;

How do you declare a constant in PL/SQL = 
In PL/SQL, you can declare a constant using the CONSTANT keyword. A constant is a variable whose value cannot be changed once it is assigned. Constants are useful when you need a fixed value that should not be modified during the execution of the PL/SQL block.
DECLARE
   -- Declare a constant for the maximum salary
   MAX_SALARY CONSTANT NUMBER := 100000;

   -- Declare a constant for the company name
   COMPANY_NAME CONSTANT VARCHAR2(50) := 'TechCorp';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Max Salary: ' || MAX_SALARY);
   DBMS_OUTPUT.PUT_LINE('Company Name: ' || COMPANY_NAME);
END;
How will you restrict the string length in PL/SQL?

DECLARE
   v_name VARCHAR2(20);  -- Restricts the string length to 20 characters
BEGIN
   v_name := 'Oracle PL/SQL';
   DBMS_OUTPUT.PUT_LINE(v_name);  -- Prints the string
END;
Which command deletes a package in PL/SQL?
DROP PACKAGE emp_pkg;
What is the purpose of the DBMS_OUTPUT package?
The DBMS_OUTPUT package in PL/SQL is used to send output from PL/SQL code to the console or log for debugging or informational purposes. It allows you to display messages, variable values, and other information that is useful for tracking the execution of a PL/SQL block, procedure, or function.
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;

What is the difference between a HASH JOIN and a SORT MERGE JOIN, and which one is more efficient for large datasets = 
•	Hash Join: Typically faster for smaller or unsorted datasets. It builds a hash table for one of the tables and then probes the hash table for matching rows from the other table.
•	Sort Merge Join: Typically better for larger datasets, especially when both tables are already sorted on the join columns. It sorts both tables and then merges the results;

How do you analyze and interpret an execution plan to optimize a PL/SQL program = 
•	An execution plan shows how Oracle will execute a SQL query, including which indexes will be used, how tables will be joined, and what operations will be performed (e.g., full table scan, index scan).
•	Steps for optimization:
o	Use EXPLAIN PLAN to generate the execution plan and identify potential inefficiencies (such as full table scans or unnecessary joins).
o	Look for operations like full table scans, sort operations, or nested loops that can be avoided with better indexes or more efficient queries.
o	Use SQL*Plus or Oracle SQL Developer to review the plan and consider the use of indexes, query refactoring, and partitioning;

How would you optimize a PL/SQL function that is being called frequently and is causing performance issues = 
Answer:
•	Profile the function using tools like Oracle SQL Trace or AWR reports to identify performance bottlenecks.
•	Optimize SQL queries within the function, ensuring proper indexing, avoiding unnecessary subqueries, and minimizing context switches.
•	Consider using caching for frequently accessed data to reduce repetitive processing.
•	If the function is performing complex computations, consider refactoring it to minimize the amount of work done within the function.

How would you optimize a query in PL/SQL that performs poorly due to full table scans = 
•	Solution 1: Use Indexing: Ensure that the columns in the WHERE clause of your query are indexed. This will allow the database to perform an index scan instead of a full table scan.
•	Solution 2: Query Refactoring: Consider breaking the query into smaller subqueries or using joins effectively to minimize the amount of data processed at once.
•	Solution 3: Optimize SQL: Avoid using functions on indexed columns in the WHERE clause. Also, ensure that the query is written to take advantage of available indexes and that the execution plan is examined and optimized;

What is the role of INDEX in PL/SQL performance optimization, and how would you choose the right index = 
•	An index improves query performance by reducing the number of rows that need to be scanned. It is particularly useful for select queries with WHERE conditions and JOIN operations.
•	When choosing the right index:
o	Use composite indexes for queries involving multiple columns in the WHERE clause.
o	Use bitmap indexes for columns with low cardinality (e.g., gender, status).
o	Consider function-based indexes when querying on expressions or functions.
o	Ensure that indexes are maintained and updated when performing DML operations;

How can you improve the performance of a query that performs JOIN operations between multiple tables = 
•	Ensure that appropriate indexes exist on the join columns.
•	Use INNER JOIN instead of OUTER JOIN if not needed, as OUTER JOIN tends to be more resource-intensive.
•	Try to filter data earlier by applying conditions to the tables in the FROM clause, before the join.
•	Avoid using functions on the join columns because this can prevent Oracle from using indexes.
Example:
SELECT emp.name, dept.name
FROM employees emp
JOIN departments dept
ON emp.department_id = dept.department_id
WHERE dept.location = 'NY';

What is a “bind variable” and how does it improve performance in PL/SQL = 
•	A bind variable is a placeholder used in SQL statements, which is then replaced with actual values during execution. It improves performance by reducing the need to re-parse SQL statements, allowing Oracle to reuse execution plans.
•	Bind variables are especially beneficial in scenarios with highly repeated queries where the values change but the query structure remains the same.
•	Bind variables help prevent SQL injection attacks and reduce soft parse overhead.
Example:
SELECT * FROM employees WHERE department_id = :dept_id;

What is the impact of using EXISTS versus IN in SQL queries, and when should each be used for better performance = 

•	EXISTS is more efficient when checking for the existence of a record. It returns TRUE as soon as a match is found, and it generally performs better in subqueries that return large result sets.
•	IN is suitable when comparing a value to a fixed set of values. It may perform poorly when dealing with large subqueries since it requires checking each element in the result set.
•	Best practice: Use EXISTS for correlated subqueries and IN for static lists or when the subquery returns a small number of values.
Example:
-- Using EXISTS (more efficient in most cases)
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d WHERE e.department_id = d.department_id
);

-- Using IN (use when the subquery returns a small number of values)
SELECT * FROM employees
WHERE department_id IN (10, 20, 30);

What is partitioning, and how does it help in optimizing large tables in PL/SQL = 
•	Partitioning divides large tables into smaller, more manageable pieces (partitions) while still treating them as a single table. Each partition can be stored and accessed independently, which improves query performance by limiting the number of rows accessed.
•	Types of partitioning:
o	Range Partitioning: Based on a range of values (e.g., date ranges).
o	List Partitioning: Based on a list of values (e.g., department codes).
o	Hash Partitioning: Even distribution of data across partitions.
o	Composite Partitioning: Combination of multiple partitioning methods.
Benefits:
•	Faster query performance by scanning only the relevant partitions.
•	Efficient data management and easier backups.
•	Partition pruning: Oracle automatically skips irrelevant partitions during query execution.

How would you optimize the performance of a PL/SQL program that processes a large number of DML (Data Manipulation Language) statements =
•	Use FORALL to perform DML operations in bulk, reducing context switches between PL/SQL and SQL.
•	Use BULK COLLECT to fetch multiple rows at once and minimize the overhead of row-by-row processing.
•	Ensure that proper indexes are available on the tables being modified.
•	Use commits strategically: Commit after processing a batch of records, instead of committing after each record, to avoid unnecessary IO operations;

How do you identify and resolve bottlenecks in a PL/SQL program = 
•	Use Profiling Tools: Tools like SQL Trace, TKPROF, AWR, and ASH reports provide detailed insights into how the PL/SQL code performs.
•	Check Execution Plans: Use EXPLAIN PLAN to analyze the SQL execution plan and identify inefficient operations, such as full table scans or improper join methods.
•	Look for Unnecessary Loops: Minimize the use of loops in SQL statements or use BULK COLLECT and FORALL to reduce the number of iterations and context switches.
•	Optimize I/O: Reduce unnecessary disk I/O by making use of indexes and optimizing SQL queries;

What are the key differences between UNION and UNION ALL, and how do they impact performance = 
•	UNION combines the results of two queries and removes duplicates, which requires additional sorting and processing. This can impact performance when dealing with large datasets.
•	UNION ALL combines the results of two queries but does not remove duplicates, making it faster than UNION because it does not require sorting.
•	Recommendation: Use UNION ALL when duplicates are not a concern to avoid unnecessary overhead.
Example:
-- Using UNION (removes duplicates, slower)
SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

-- Using UNION ALL (faster)
SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;

What is a materialized view, and how does it help improve query performance in PL/SQL = 
•	A materialized view is a database object that stores the result of a query physically. It can be refreshed periodically, and querying a materialized view is much faster than re-running the query each time, especially for complex queries.
•	Benefits:
o	Significantly reduces query response time by storing the results of expensive queries.
o	It is particularly useful for aggregated data or data that does not change frequently.
o	You can refresh the materialized view using different methods such as manual, on-demand, or incremental refresh;

What is the difference between ROWNUM and ROW_NUMBER() in PL/SQL, and when would you use each for optimization = 
Answer:
•	ROWNUM is a pseudo-column that returns a unique number for each row returned by a query, starting from 1 for the first row. It is faster and can be used for limiting results but has some limitations, such as not working with ORDER BY properly.
•	ROW_NUMBER() is a window function that returns a unique number for each row based on the order specified. It is more flexible than ROWNUM and works well with ORDER BY.
Use case:
•	Use ROWNUM when you need to limit the number of rows returned by a query without concern for specific ordering.
•	Use ROW_NUMBER() when you need to assign row numbers based on a specific order or partitioning;

How would you optimize a query that performs a GROUP BY operation on a large dataset = 
•	Use appropriate indexes on the columns being grouped.
•	Consider partitioning the table to reduce the number of rows processed for large datasets.
•	Use parallel query execution to improve performance when dealing with large aggregations.
•	Optimize the SELECT clause by selecting only the necessary columns and filtering out unneeded rows;

Explain the concept of Parallel Execution in PL/SQL and how it affects query performance = 
•	Parallel Execution allows multiple processes to work on the same query simultaneously. It improves performance, especially for large datasets and complex queries.
•	You can enable parallelism by using the PARALLEL hint in your SQL queries.
•	It is typically used for full table scans, large aggregations, and joins on large tables.
Example:
SELECT /*+ PARALLEL(4) */ department_id, COUNT(*) 
FROM employees 
GROUP BY department_id;

What are the trade-offs of using a WITH clause in PL/SQL = 
•	The WITH clause (also known as Common Table Expressions - CTEs) is useful for breaking complex queries into simpler, reusable parts.
•	Advantages:
o	Improves query readability and maintainability.
o	Useful for recursively querying hierarchical data.
o	Can reduce repeated calculations by defining a subquery once and referencing it multiple times.
•	Disadvantages:
o	Performance impact: The CTE may be materialized (temporarily stored in memory or disk), which can degrade performance for large result sets.
o	Use with caution: CTEs may cause performance overhead if the underlying query is not well optimized.
Example:
WITH emp_cte AS (
    SELECT department_id, COUNT(*) AS employee_count 
    FROM employees 
    GROUP BY department_id
)
SELECT * FROM emp_cte;

What are some best practices to follow when handling large transactions in PL/SQL = 
•	Limit transaction size: Instead of performing a single large transaction, break it into smaller transactions to avoid large lock contention and rollback segments.
•	Use Savepoints: Use savepoints to allow partial commits and rollbacks without affecting the entire transaction.
•	Commit in batches: Commit after a set number of rows have been processed instead of committing after each row to improve performance.
•	Avoid unnecessary locks: Keep transactions short and ensure that the system is not waiting on locks for too long.
•	
Example
DECLARE
    counter NUMBER := 0;
BEGIN
    FOR rec IN (SELECT * FROM employees WHERE department_id = 10) LOOP
        UPDATE employees SET salary = salary + 500 WHERE employee_id = rec.employee_id;
        counter := counter + 1;
        IF counter >= 100 THEN
            COMMIT;
            counter := 0;
        END IF;
    END LOOP;
    COMMIT;
END;

How does AUTOTRACE help in optimizing SQL queries in PL/SQL = 
•	AUTOTRACE is a tool provided by Oracle that allows you to examine the execution plan and statistics of a query. It can be used to analyze the performance of SQL queries by displaying the execution plan, buffer usage, and other relevant metrics.
•	Usage: It helps in identifying issues such as full table scans, missing indexes, and inefficient joins.
SET AUTOTRACE ON
SELECT * FROM employees WHERE department_id = 10;

What is the difference between NESTED LOOPS and MERGE JOIN in terms of performance optimization = 
•	Nested Loops Join: Best for smaller datasets or when one of the tables has a small number of rows and can be used for looking up data in the second table. Performance degrades as the size of the tables increases.
•	Merge Join: Suitable for large tables when both tables are sorted on the join column. Merge joins are more efficient than nested loops for large datasets, especially when there is an index on the join columns.
•	Considerations: Use nested loops when the tables involved are small, and use merge joins for larger tables or when there are indexes on the join keys;

What is the role of INFORMATION_SCHEMA in optimizing PL/SQL queries = 
•	The INFORMATION_SCHEMA is a system view that provides metadata about the database, such as table structures, column definitions, indexes, and constraints. By querying these views, you can optimize PL/SQL code by:
o	Identifying missing or unused indexes.
o	Checking for constraints or foreign keys that may slow down DML operations.
o	Reviewing column data types to ensure they are appropriate for performance.
SELECT table_name, index_name 
FROM user_indexes 
WHERE table_name = 'EMPLOYEES';

How would you implement caching in PL/SQL to improve performance = 
•	Result Caching: Use Oracle's result cache feature, which stores the results of queries in memory, allowing repeated execution of the same query to return faster.
•	PL/SQL Caching: Store frequently used values in PL/SQL variables or collections to avoid repeated database calls.
•	Materialized Views: Use materialized views to cache the results of complex queries and refresh them periodically.
Example
SELECT /*+ RESULT_CACHE */ * 
FROM employees 
WHERE department_id = 10;

How do you use SQL_TRACE to optimize PL/SQL code performance = 
•	SQL_TRACE generates a trace file that provides detailed information about the execution of SQL statements. It helps you identify which SQL statements are consuming the most resources (e.g., CPU, I/O).
•	Use: It helps you optimize queries by looking at execution plans, identifying bottlenecks, and adjusting SQL statements.
Example
ALTER SESSION SET SQL_TRACE = TRUE;
-- Execute the PL/SQL block or query
ALTER SESSION SET SQL_TRACE = FALSE;

What are the key differences between OLTP and OLAP systems, and how do they affect PL/SQL query optimization = 
•	OLTP (Online Transaction Processing) systems are designed for fast query processing of transactional data. Optimizing PL/SQL for OLTP involves minimizing I/O, using indexes, avoiding locking, and ensuring quick response times for high-concurrency scenarios.
•	OLAP (Online Analytical Processing) systems are optimized for complex queries on large datasets, often involving aggregations. Optimizing PL/SQL for OLAP involves techniques such as using materialized views, parallel execution, and partitioning
Example:
•	OLTP: Optimizing small, frequent updates or inserts in a financial system.
•	OLAP: Optimizing large data aggregations in a reporting or analytical system;

What is Dynamic Sampling in Oracle, and how does it improve query optimization = 
•	Dynamic Sampling is a feature in Oracle that allows the optimizer to collect sample statistics for a query during its execution if there are no statistics available, or if existing statistics are insufficient.
•	This feature helps Oracle generate better execution plans for queries where statistics are not up to date or available.
Example
SELECT /*+ dynamic_sampling(t 2) */ * FROM employees t WHERE department_id = 10;