1.

 How do you identify and resolve a performance bottleneck in a long-running stored

DBMS_PROFILER is an Oracle PL/SQL package used to analyze performance by measuring time spent
on each line of code. It works by creating database tables, starting the profiler, running your code,
stopping the profiler, and analyzing the collected data.

Steps to Use DBMS_PROFILER:

1. Set Up Profiler Tables: Run the [Link] script to create the necessary tables
(plsql_profiler_runs, plsql_profiler_units, plsql_profiler_data) in your schema.

@?/rdbms/admin/[Link]

2. Start Profiler: Call DBMS_PROFILER.START_PROFILER before your code execution

3. Run Your Code: Execute the procedure, function, or anonymous block you want to profile.
4. Stop Profiler: Call DBMS_PROFILER.STOP_PROFILER immediately after the code finishes.

5. Analyze Data: Query the profiler tables to identify bottlenecks.

You can use queries to find total time, number of executions, and max/min time per line.

Example: SELECT * FROM plsql_profiler_data;

Question 2: Explain the significance of BULK COLLECT and FORALL for large data sets.

 Answer: These features reduce the context-switching overhead between the PL/SQL engine
and the SQL engine.
  Code Example:

sql

DECLARE

TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;

v_ids t_emp_ids;

BEGIN

-- Fetching in bulk to minimize context switches

SELECT employee_id BULK COLLECT INTO v_ids FROM employees WHERE department_id = 10;

-- Updating in bulk

FORALL i IN 1..v_ids.COUNT

UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_ids(i);

END;

2. What are the differences between Nested Tables, VARRAYs, and Associative Arrays?

Associative Arrays (Index-by tables): Key-value pairs used primarily for internal PL/SQL logic;
cannot be stored in the database.

DECLARE

TYPE sal_type IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

emp_sal sal_type;

BEGIN

emp_sal('John') := 5000; -- Key is a string

emp_sal('Doe') := 6000;

END;

Nested Tables: Can be stored in database columns; size is dynamic; can be sparse.

DECLARE

TYPE names_nt IS TABLE OF VARCHAR2(20);

my_list names_nt := names_nt('Alice', 'Bob'); -- Constructor needed

BEGIN
 my_list.EXTEND;

my_list(3) := 'Charlie';

END;

VARRAYs: Ordered collections with a fixed maximum size; stored as a single block in the
database, making them faster for small, fixed-size data sets

DECLARE

TYPE color_va IS VARRAY(3) OF VARCHAR2(10); -- Max 3 elements

colors color_va := color_va('Red', 'Green');

BEGIN

[Link];

colors(3) := 'Blue';

END;

3. What is a Mutating Table Error (ORA-04091) and how do you resolve it?

A Mutating Table Error (ORA-04091) occurs in Oracle when a row-level trigger tries to query or
modify the same table that is already being updated by the statement that fired the trigger

Oracle prevents this to maintain data consistency; it won't allow a trigger to see a table in a
"half-changed" state where some rows are updated but the overall operation isn't finished

Why It Happens (The Example)

Let’s use the customers table from the sample database for demonstration.

Suppose you want to update the credit limit for a customer. If the credit is greater than 5 times
of the lowest non-zero credit, the system automatically assigns this credit to the customer.
How to Resolve It

There are several standard ways to fix this, depending on your Oracle version and needs:
Use a Statement-Level Trigger: If you don't need to check values row-by-row, use a statement-
level trigger (remove FOR EACH ROW). These do not suffer from mutation errors because they
fire only after the entire operation is complete.

Resolution: Use a Compound Trigger (introduced in Oracle 11g) to manage state across different
timing points or store data in a temporary package collection and process it at the statement
level.

Exception in oracle PLSQL

Types of Exceptions

1. Predefined System Exceptions

These are common errors automatically raised by Oracle that have predefined names. Common
ones include NO_DATA_FOUND, ZERO_DIVIDE, and TOO_MANY_ROWS
2. Non-Predefined System Exceptions

These are standard Oracle errors (like ORA-02292 for foreign key violations) that do not have a
predefined name. You must declare a name and link it to the Oracle error code using PRAGMA
EXCEPTION_INIT

Example: Handling Foreign Key Violation

3. User-Defined Exceptions

These are custom exceptions you define for specific business logic errors. You must declare them
in the DECLARE section and raise them explicitly using the RAISE keyword

Example: Enforcing Business Rules

 SQLCODE: Returns the number of the last error.

 SQLERRM: Returns the error message associated with the current error code.

 RAISE_APPLICATION_ERROR: A procedure that allows you to issue your own error message
and code (between -20000 and -20999) back to the calling environment.

 Propagating Exceptions: If an exception is not caught in the current block, it "bubbles up" to
the enclosing block until a handler is found or the program terminates

What are the two main parts of a PL/SQL package?

 Package Specification (Spec): The public interface that declares the available components
(procedures, functions, etc.). It does not contain code.

 Package Body: Contains the actual implementation code for the subprograms declared in
the specification.

What are the benefits of using a package?

 Encapsulation: Groups related items together and hides implementation details from the
user.

 Improved Performance: The entire package is loaded into memory at once during the first
call, reducing disk I/O for subsequent calls.

 Security: Developers can grant privileges on the entire package rather than individual
procedures.

 Modularity: Allows for easier management of large application codebases.

 Yes. A package specification can exist alone if it only contains global variables, constants, or
type definitions. However, if the specification declares procedures or functions, a body is
mandatory to define their logic.

Can we have Package body alone and no Specs? Will it be compiled without errors?

No, a package body cannot be compiled without its corresponding package specification. If
you attempt to create a package body alone, the Oracle compiler will return an error

 Primary Error: You will typically encounter error PLS-00304, which explicitly states: "cannot
compile body of [package_name] without its specification".

 Result: The package body will be created as an Invalid object in the database and cannot be
executed.