Oracle Database, PL/SQL, and Reports: Comprehensive Guide

Part 1: Database Architecture

Q: Explain the difference between an Oracle database instance and an Oracle database. Describe the main components of each and explain how they work together when a client application executes a SQL query.

The Database consists of physical files (data files, control files, redo logs). It stores the actual data and logical objects like tables and indexes.
The Instance consists of the System Global Area (SGA) shared memory and background processes. It provides the processing power to manage and access the data.
The PGA (Program Global Area) is private memory allocated to individual server processes for things like sorting and session data.

How they work together: When a client sends a SQL statement, a server process receives it (using its PGA). It parses the statement, the optimizer creates an execution plan, and the instance executes it using the SGA (buffer cache) and accesses the database files as needed.

Summary: The database stores the data, the instance processes it, and the PGA handles the private memory needs of the server processes doing the work.

Q: An Oracle database has multiple users connected at the same time. Does each user get a separate SGA and PGA?

SGA: Shared by all users in the instance. (No, users don't get their own).
PGA: Private memory for each individual server process.
The 3 Components: The User Process runs the client app, the Server Process handles requests, and the Session tracks the logical connection.
Relationship: Because multiple sessions can share server processes, there is no strict one-to-one relationship between them.

Q: Why can Oracle benefit from using a bind variable (e.g., :employee_id) instead of embedding a different literal employee ID in each SQL statement?

A bind variable lets a SQL statement accept different values at runtime without changing the SQL text.

Key Benefits:

Performance: Oracle can reuse the existing cursor and execution plan, reducing hard parsing and CPU overhead.
Shared Pool Efficiency: Prevents the database from filling up with thousands of distinct SQL statements.
Security: Helps prevent SQL injection when values are bound properly (instead of concatenated).

The Catch: They don't guarantee optimal performance. Because data distribution varies, Oracle might use Adaptive Cursor Sharing to create multiple child cursors when different values need different execution plans.

Q: Will Oracle always use the index because one exists on EMPLOYEE_ID? Why or why not?

No. The Cost-Based Optimizer (CBO) evaluates statistics, cardinality, selectivity, and clustering factors to choose the cheapest plan.

Highly selective queries (e.g., finding one Employee ID in a huge table) usually benefit from an index.
Low selectivity queries (returning a large portion of the table) often run faster with a Full Table Scan, because reading many index entries and jumping to table blocks costs more than just reading the whole table.

Takeaway: Never assume an index is being used or is faster. Always check the actual execution plan and runtime statistics.

Q: Composite Index Example (department_id, salary). Which query benefits more, Query A (WHERE department_id = 10) or Query B (WHERE salary > 5000)?

Query A benefits directly. Query B typically cannot use this index.

Why: A composite index follows the "Leftmost Prefix" rule. It is sorted first by department_id, then by salary. Query A filters by the leading column, so Oracle can use an index range scan. Query B filters only by the second column; because it cannot skip the first column, Oracle usually ignores this index and performs a Full Table Scan.
Importance of column order: You must put the most frequently queried column (or the one with high selectivity used in WHERE clauses) first. The index is only useful if the leading column is included in the query.

Oracle Database Architect Level: TEST_RESULTS Scenarios

Scenario 1: Full Table Scan vs. Index

Suppose TEST_RESULTS contains one million rows, and you execute:

SELECT test_id, test_status
FROM test_results
WHERE test_id > 10;

Assume:

TEST_ID has a unique index.
The condition matches 900,000 rows.
TEST_STATUS is not included in the index.

Question: Would you expect Oracle to use the index and then access the table for 900,000 rows, or choose a full table scan? Explain why.

Answer:

I would expect Oracle to choose a Full Table Scan.

Here is the reason why:

Even though an index exists on TEST_ID, the WHERE test_id > 10 condition matches 900,000 out of 1 million rows (90% of the table). This is extremely low selectivity.

If Oracle used the index, it would have to:

Perform an Index Range Scan to read 900,000 index entries.
Perform a Table Access by RowID for those 900,000 rows to fetch the test_status column (since it is not in the index).

Reading 900,000 index blocks plus 900,000 table blocks requires far more I/O operations than simply reading the entire table sequentially. Because a Full Table Scan uses multiblock I/O (reading many blocks at once), it is significantly cheaper and faster for retrieving almost the entire table.

Scenario 2: A Real Performance-Tuning Scenario

Suppose an Oracle query is running slowly:

SELECT test_id, test_status
FROM test_results
WHERE test_id = 121;

The execution plan uses an index, and the estimated cost is low, but the query still runs slowly in production.

Question: Does a low estimated COST guarantee that the query will execute quickly in reality? Why or why not?

Answer:

No. A low estimated COST does not guarantee fast execution in production. COST is an optimizer estimate used to compare execution plans; it is not actual elapsed time.

The actual performance may differ because of stale statistics, data distribution, bind variable values, system load, or I/O waits. Therefore, I would validate the execution plan and measure actual runtime performance using execution statistics, rather than relying on estimated COST alone.

Part 2: PL/SQL Concepts

Difference between a Procedure and a Function?

Procedure: May return one or more values using OUT & INOUT parameters, or may not return a value. Can only be called in a PL/SQL block.
Function: Must return a single value and has the RETURN clause in its definition. Can be called in SQL SELECT statements.

Difference between ROWID and ROWNUM?

ROWID: Gives the hexadecimal string representing the physical address of a row in the database.
ROWNUM: Gives a sequence number indicating the order in which rows are retrieved from the database.

Examples of Pseudo Columns?
NEXTVAL, CURRVAL, LEVEL, SYSDATE

Difference between Implicit and Explicit Cursors?

Implicit Cursors: Automatically created by Oracle for all DML statements. (e.g., SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN).
Explicit Cursors: Created by users for multi-row SELECT statements.

How to create a table in a procedure or function?
Use dynamic SQL. Example:

DECLARE
    L_STMT VARCHAR2(100);
BEGIN
    DBMS_OUTPUT.PUT_LINE('STARTING ');
    L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';
    EXECUTE IMMEDIATE L_STMT;
    DBMS_OUTPUT.PUT_LINE('end ');
END;

Explain the usage of WHERE CURRENT OF clause in cursors?
Used in conjunction with FOR UPDATE in the cursor select statement. It means delete/update the current record specified by the cursor without having to repeat the WHERE clause. Example:

DECLARE
    CURSOR wip_cur IS
    SELECT acct_no, enter_date
    FROM wip
    WHERE enter_date < SYSDATE - 7
    FOR UPDATE;
BEGIN
    FOR wip_rec IN wip_cur LOOP
        INSERT INTO acct_log (acct_no, order_date)
        VALUES (wip_rec.acct_no, wip_rec.enter_date);
        
        DELETE FROM wip WHERE CURRENT OF wip_cur;
    END LOOP;
END;

What is the purpose of FOR UPDATE?
Selecting in FOR UPDATE mode locks the result set of rows in update mode. The rows cannot be updated or deleted by others until a commit or rollback is issued.

What is RAISE_APPLICATION_ERROR?
A procedure defined by Oracle allowing developers to raise an exception with a custom error number (between -20000 and -20999) and message.

DECLARE
   num_tables NUMBER;
BEGIN
   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
   IF num_tables < 1000 THEN
      raise_application_error(-20101, 'Expecting at least 1000 tables');
   END IF;
END;

What is a Mutating Error?
Occurs when a trigger on a table tries to read/retrieve data from the same table that is currently being updated by the triggering statement.

Can we have commit/rollback in DB triggers?
No, having Commit/Rollback inside a trigger defeats the standard of whole transaction commit/rollback. To carry out independent commits, use an Autonomous Transaction block inside the trigger.

What is an Autonomous Transaction?
A transaction embedded in another transaction, but functions independently. Within the autonomous transaction, Oracle views the triggering table as it was before any changes occurred, avoiding mutating table errors.

What is a REF Cursor?
A data type in PL/SQL that represents a cursor or result set. Unlike normal cursors (which are static), a REF cursor can be opened dynamically for different queries at runtime.

Is Truncate a DDL or DML statement?
Truncate is a DDL statement. It automatically commits, is not rollbackable, and changes the storage definition of the object.

What happens when you drop/rename a package?
Packages that use the renamed package will have to be modified and recompiled. A simple compilation of the renamed package won't suffice.

What are Cascading Triggers?
When a trigger fires, a SQL statement within its action can potentially fire other triggers.

What are Materialized Views?
A database object that stores the results of a query (sometimes called snapshots). Used to speed up data replication.

-- Create Materialized View Log to track changes
CREATE MATERIALIZED VIEW LOG ON items;

-- Create Materialized View
CREATE MATERIALIZED VIEW items_summary_mv
ON PREBUILT TABLE
REFRESH FORCE AS
SELECT a.PRD_ID, a.SITE_ID, sum(a.GMS) GMS
FROM items@remotedb a
GROUP BY a.PRD_ID, a.SITE_ID;

Part 3: Oracle Reports (Comprehensive Q&A)

Layout & Design

How many different layouts are available?

Tabular
Form Like
Form Letter
Mailing Label
Group Left
Group Above
Matrix
Matrix with group

What is the difference between Frame and Repeating Frame?

Frames: Surround other objects to protect them from being overwritten.
Repeating Frames: Surround fields created for a group's columns and print once for each record of the group.

What are the 3 types of Columns in reports?

Placeholder Column: Stores a value for a variable.
Formula Column: Used for mathematical calculations.
Summary Column: Performs aggregate functions (SUM, COUNT, MAX, MIN, AVG).

What is Flex mode and Confine mode?

Confine mode (On): Child objects cannot be moved outside their enclosing parent.
Flex mode (On): Parent borders "stretch" when child objects are moved against them.

What is Page Protect property?
Indicates whether to keep an entire object and its contents on the same logical page. If it cannot fit, it moves to the next page.

What are Widow Lines and Widow Records?

Widow Lines: Minimum number of lines of boilerplate text that should appear on a page. If they don't fit, the text moves to the next page.
Widow Records: Minimum number of instances (records) that should appear on a page. If they don't fit, the repeating frame starts on the next page.

What are Anchors?
Defines the relative position of an object to the object it is anchored to (vertical and horizontal positioning). Types: Implicit (created by Oracle at runtime) and Explicit (created by you).

Parameters & Variables

What is a Lexical Parameter?
Used to replace WHERE or ORDER BY conditions at runtime (replaces text).

What are Bind Variables?
Used for replacing a single parameter value in the SELECT statement.

What are the various values of Horizontal/Vertical sizing property?

Contract: Size decreases if data is short, but cannot increase.
Expand: Size increases if data is tall, but cannot decrease.
Fixed: Height is the same on each page regardless of data.
Variable: Object may expand or contract to fit data.

Triggers

What are the 5 Global Report Triggers and their firing sequence?

Before Parameter Form
After Parameter Form
Before Report
Between Pages
After Report

What is a Format Trigger?
A PL/SQL function that fires before an object is printed. Returns Boolean (True = print, False = don't print).

What is a Validation Trigger?
PL/SQL functions executed when parameter values are specified on the command line and when accepting the Runtime Parameter Form.

SRW Package & Output

What is the SRW Package?
The standard Reports Builder built-in package. It extends report capabilities, controls execution, outputs runtime messages, and performs DDL.
Common Procedures: SRW.MESSAGE, SRW.DO_SQL, SRW.RUN_REPORT, SRW.USER_EXIT, SRW.INIT.

How do you mail the output of a report?

Use UTL_SMTP.
Use MAILX called in a shell script registered as a concurrent program.

How do you write the report output to Excel or text file?

Use the TEXT_IO package.
Use SPOOL in the After Report trigger.

What is Report Bursting?
The capability of producing multiple copies of a report (or portions of it) in different output formats and delivering them to multiple destinations simultaneously.

Data Model & Queries

What is a Data Model?
A logical group of Report Objects created via queries. The queries build the groups, and the groups populate the report.

What is a Data Link?
Joins multiple queries in a report. A parent-child relationship causing the child query to execute once for each instance of its parent group.

What are Non-query fields?
Aggregated information, calculated information, and string functions.

What is more efficient: Maximum Rows or Group Filter?
Maximum Rows is more efficient because it restricts the amount of data fetched by the query at the database level. A Group Filter must retrieve all records before applying the filter criteria in the report.

Pages

Oracle Interview Question