Oracle SQL, PL/SQL & APEX Interview

Preparation
1. Performance Issue in a Query – Debugging Approach
When a query is slow, follow a step-by-step approach to identify the root cause:
1. Identify the Query – Use V$SQL, AWR, or SQL Monitor reports to find high-resource
queries.
2. Check Execution Plan – Look for full table scans, missing indexes, or costly operations.
3. Index Analysis – Ensure proper indexes exist and are used.
4. Statistics – Verify table/index statistics are updated.
5. Locking & Blocking – Check for blocking sessions.
6. Query Rewrite – Optimize subqueries/joins.
7. System Check – Monitor CPU, memory, and I/O.

2. COMMIT, ROLLBACK, and SAVEPOINT

Command Description

COMMIT Makes all changes in the current

transaction permanent.

ROLLBACK Reverts all changes since the last COMMIT

or SAVEPOINT.

SAVEPOINT Creates a checkpoint in a transaction to

rollback partially.

3. Importance of %TYPE and %ROWTYPE

%TYPE – Declares a variable with the same datatype as a table column or another variable.
Example:
v_sal emp.sal%TYPE;
• Benefit: Auto-syncs with datatype changes.

%ROWTYPE – Declares a record for a full row of a table.

Example:
v_emp emp%ROWTYPE;
• Benefit: Simplifies handling of multiple columns.
4. Difference between Triggers and Constraints
Feature Trigger Constraint

Purpose Executes PL/SQL code Enforces simple data

after/before DML events integrity rules

Scope Complex rules across Simple rules (NOT NULL,

multiple tables PK, FK, UNIQUE, CHECK)

Reliability Can be disabled Always active unless

dropped

Performance Slower (executes Faster (built into DB

procedural code) engine)

5. PL/SQL Script – Print Series 99,96,93…3

SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER := 99;
BEGIN
WHILE v_num >= 3 LOOP
DBMS_OUTPUT.PUT_LINE(v_num);
v_num := v_num - 3;
END LOOP;
END;
/

6. SQL Practical Questions

Q1. Show Dept Name and Employee Count
SELECT d.dname, COUNT(e.empno) AS emp_count
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname;

Q2. Display Top Five Salaries

SELECT employee_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
Q3. Employees Joined in 1981
SELECT * FROM employee
WHERE TO_CHAR(date_of_joining, 'YYYY') = '1981';

7. Oracle APEX Practical Questions

Q4. Dynamic Textboxes based on Dropdown → Use Dynamic Action + SQL region with
APEX_ITEM.TEXT.
Q5. Public App + Interactive Grid with Salary Editable → Change Authentication + Add
Validation.
Q6. Date Picker Validation → Add PL/SQL Validation: IF :P1_TO_DATE > :P1_FROM_DATE
THEN RETURN 'To Date cannot be greater than From Date'; END IF;
Q7. Get Application Item in JavaScript:
apex.server.process('GET_APP_ITEM_VALUE', {x01: 'APP_ITEM'}, {success: function(pData)
{console.log(pData);}});

Additional Practical Questions

Q5. Public Application + Interactive Grid (Salary Editable Only)

Steps:
1. Change Authentication → Shared Components → Authentication Schemes → Create No
Authentication → Set as Current.
2. Create Interactive Grid on EMP table.
3. Make all columns Read Only except Salary.
4. Add Validation on Salary column:
PL/SQL Code:
IF :SAL < 1000 THEN
RETURN 'Salary must be at least 1000';
END IF;

Q6. Interactive Grid + Date Picker Validation

Requirement: Two date pickers (P1_FROM_DATE, P1_TO_DATE). On button click, validate
that To Date is not greater than From Date.
Steps:
1. Create Interactive Grid on EMP.
2. Add Date Pickers: P1_FROM_DATE, P1_TO_DATE.
3. Add a Button (Submit/Search).
4. Create Validation (PL/SQL Function Returning Error Text):
IF :P1_TO_DATE > :P1_FROM_DATE THEN
RETURN 'To Date cannot be greater than From Date';
END IF;
Q7. Get Application Item Value in JavaScript
Method 1: If item is on the current page →
var val = $v('P1_ITEM_NAME');
console.log(val);

Method 2: If it’s an Application Item (server-side only) →

1. Create On-Demand Application Process:
htp.p(:APP_ITEM_NAME);
2. Call in JavaScript:
apex.server.process('GET_APP_ITEM_VALUE', {x01: 'APP_ITEM_NAME'}, {
success: function(pData){ console.log('Application Item Value: ' + pData); }
});

Extended Interview Questions – SQL, PL/SQL, APEX, Fusion

Slow running query on APEX page – Fix/Process

1. Identify problem query using APEX debug.
2. Analyze execution plan with EXPLAIN PLAN.
3. Possible fixes: indexes, bind variables, remove unnecessary columns, pagination,
materialized views, caching.
4. Test query in SQL Developer → Apply fix → Re-test in APEX.

Use of Analytical Functions (LEAD, LAG, DENSE_RANK)

LEAD: Access next row value.
LAG: Access previous row value.
DENSE_RANK: Rank without skipping numbers.
Example:
SELECT empno, ename, sal,
LAG(sal) OVER (ORDER BY sal) AS prev_sal,
LEAD(sal) OVER (ORDER BY sal) AS next_sal,
DENSE_RANK() OVER (ORDER BY sal DESC) AS rank
FROM emp;

Change template of Interactive Grid

Yes. Go to Page Designer → Interactive Grid → Attributes → Appearance/Template Options.
Use JavaScript/CSS for advanced customization.

Passing values between pages in Oracle APEX

Using Page Items (Set Items + Branch), Session State, or URL Parameters (f?
p=APP_ID:PAGE_ID:SESSION::NO::ITEM:VALUE).
Practical scenario using APEX Collections
Used for multi-step forms, staging uploaded data (Excel/CSV), temporary carts in e-
commerce, or session-based temp tables.

Database Links usage

Used to query or perform DML on remote databases. Example: SELECT * FROM
emp@db_link_name;

Package/Procedure/Function
Procedure: Performs task, no return.
Function: Returns a value, can be used in SQL.
Package: Groups procedures, functions, variables.

Debug in procedure
Use DBMS_OUTPUT.PUT_LINE, DBMS_DEBUG, exception logging, or APEX debug mode.

Dynamic SQL
Build SQL statements at runtime. Example: EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM
emp' INTO v_count;

Ref Cursor
Cursor variable pointing to dynamic result sets, returned from procedures/functions.

Reflow Report
Responsive report layout for small screens. Features: collapsible columns, mobile-friendly.
Advantages: user-friendly on mobile, auto-hide extra columns.

Interactive Grid vs Interactive Report

Interactive Report: Filtering, sorting, download, charts.
Interactive Grid: All IR features + inline editing, validations, DML support.

Why cursor is used

To process rows one by one when SQL cannot handle row-by-row logic.

Update collection mid-session

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(p_collection_name=>'TEMP', p_seq=>1,
p_attr_number=>2, p_attr_value=>'UpdatedValue');

PL/SQL Table
Also called associative array; in-memory collection indexed by numbers or strings.

Experience UNIX/Linux
Used SQL*Plus scripts, shell scripts, cron jobs, FTP/SFTP, grep, tail, vi, scp for DB and
integration monitoring.
Experience REST API
Consumed Fusion REST APIs, developed APEX REST data sources, fetched Supplier Invoices
via Fusion Payables API.

Creating API in APEX

Use RESTful Web Services module → define Resource Templates + Handlers (GET/POST)
backed by PL/SQL logic.

Authentication vs Authorization
Authentication: Who are you? Authorization: What can you do? Used SSO, APEX built-in, DB
authentication.

Page Zero in APEX

Global page items/regions shared across app. Can create HTML regions for nav bar, logo,
alerts.

Server-side vs Client-side condition in Dynamic Actions

Client-side: evaluated in browser.
Server-side: evaluated in session state on server.

Custom process for Interactive Grid

Disable automatic DML process and create custom PL/SQL process to handle
insert/update/delete.

APEX Row Status

APEX$ROW_STATUS column: C=Created, U=Updated, D=Deleted.

AJAX Callback
On-Demand Process invoked via apex.server.process from JavaScript; used for async
communication.

Debugging slow APEX page

Enable APEX Debug Mode → check query time, PL/SQL process, rendering time → optimize
queries, caching, reduce heavy computations.

Triggers, Mutation Trigger, Uses, Types

Row-level, Statement-level; Before, After, Instead Of; Mutation trigger occurs when trigger
modifies same table; used for auditing, complex rules.

Fusion REST API & XDO BI Reporting

Used Fusion REST APIs for CRUD; BI Publisher for custom reports.

Fusion Sandbox
Safe testing environment for customization without impacting production.
Migration of Fusion Components
Export/Import via Customization Set Manager; moved integrations, reports, sandboxes.

OIC Integration – EBS/Fusion/OCI

Used DB Adapter/FTP; monitored with concurrent jobs; business events logged in OIC
Console.

Roles required
Integration Specialist, SOAAdmin, BIAdministrator.

APEX Items naming

APEX: Page Items; PL/SQL: Bind variables (:P1_ITEM); JS: $v('P1_ITEM').

APEX Plug-ins
Used for file upload preview, dynamic LOVs, charts.

Primary Key vs Foreign Key

Primary Key: Unique identifier, no NULL, can be composite.
Foreign Key: Refers PK in another table.

Oracle PL/SQL & SQL – Interview Q&A

1. Advantages of Collections

Answer:

 Store multiple values in memory (arrays, lists, tables).

 Useful for bulk processing (Bulk Collect, FORALL).
 Reduce context switching between PL/SQL and SQL.
 Can temporarily store intermediate data.
 Improve performance for large datasets.

2. What are Table Joins

Answer:

 Combines rows from two or more tables based on related columns.

  Types: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL
OUTER JOIN, CROSS JOIN.

3. Use of LEFT OUTER JOIN

Answer:

 Returns all rows from the left table, and matching rows from the right table.
 If no match, NULL is returned for right table columns.

4. If no matching record from left table

Answer:

 In a LEFT OUTER JOIN, columns from the right table will have NULL values.

5. Difference between INNER JOIN and LEFT JOIN

Feature INNER JOIN LEFT JOIN

Rows returned Only matching rows All rows from left + matching right rows

Nulls No nulls for non-matched rows Nulls appear for non-matched right rows

Usage Only common data needed Include all left table rows

6. Difference between Procedure and Function

Feature Procedure Function

Returns value No Yes (single value)

Used in SQL No Yes (can be used in SELECT)

Purpose Performs task Computes and returns result

7. Can we use all types of functions in SQL query?

Answer:
  Yes, except procedures (cannot be used in SQL).
 Functions returning values can be used in SELECT, WHERE, ORDER BY.

8. Worked in Unix? Basic Commands

Answer:

 Commands like: ls, cd, pwd, mkdir, cp, mv, rm, grep, tail, head,
chmod.
 Used for script execution, file movement, job scheduling, monitoring logs.

9. Use of cat command

Answer:

 Concatenates and displays file content.

 Example: cat file.txt or cat file1 file2 > file3.

10. Use of Analytical Functions in PHP

Answer:

 SQL queries executed from PHP can include analytic functions like RANK,
DENSE_RANK, LEAD, LAG.
 Used for reporting, ranking, cumulative calculations directly in SQL.

11. How to write DENSE_RANK

Answer:

SELECT empno, ename, sal,

DENSE_RANK() OVER (ORDER BY sal DESC) AS rank
FROM emp;

12. Partition Questions / Schema Objects

Answer:
  Created Schemas, Tables, Constraints, Indexes, Privileges, Roles, DB
directories, Sequences, External tables, Synonyms.
 Used SQL Loader for bulk data load.

13. Cursor, Triggers, Procedure, Function, Package

Answer:

 Cursor: Handles row-by-row processing.

 Triggers: Automatic action on DML.
 Procedure: Executes tasks.
 Function: Returns value.
 Package: Collection of procedures, functions, variables.

14. Performance Optimization Features

Answer:

 Bulk Collect: Fetch multiple rows in one go.

 FORALL: Bulk DML.
 Dynamic SQL: Build SQL at runtime.
 Ref Cursor: Return dynamic result set.
 Collections: Temporary data storage.

15. PRAGMA Usage

Answer:

 AUTONOMOUS_TRANSACTION: Run independent transaction inside

procedure.
 EXCEPTION_INIT: Assign custom names to Oracle error numbers.

16. Hands-on with XML, JSON, Web Services

Answer:

 Used XMLTYPE, JSON_OBJECT, UTL_HTTP for APIs.

 Consume and produce XML/JSON for integration.
17. Analytical Functions

Answer:

 RANK, DENSE_RANK, LEAD, LAG, NTILE, CUME_DIST, SUM OVER(),

AVG OVER().
 Used for ranking, cumulative totals, moving averages.

18. Dynamic SQL

Answer:

 Execute SQL dynamically at runtime using EXECUTE IMMEDIATE.

 Used when table names or columns are unknown at compile time.

19. Difference between Procedure and Function

Answer:

 Procedure: Performs actions, may or may not return value.

 Function: Returns value, can be used in SQL queries.

20. Parameters in Procedure and Function

Answer:

 Modes: IN, OUT, IN OUT.

 Can pass scalars, records, collections.

21. DML in Procedure and Function

Answer:

 Procedure: Can perform any DML.

 Function: Can perform DML only if autonomous transaction is used;
otherwise, restricted in SELECT context.
22. Call a Function outside a Shell

Answer:

 Use SQL*Plus:

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(function_name(param));
END;
/

23. Cursor Attributes

Answer:

 %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.

 Used to check cursor state during processing.

24. What is an Index

Answer:

 Database object to speed up data retrieval.

 Can be unique, composite, bitmap.
 Example:

CREATE INDEX idx_emp_sal ON emp(sal);

Warehouse Management, APEX & PL/SQL – Interview Q&A

1. Warehouse Management

Answer:

 High-level functionality: Inventory tracking, order fulfillment, shipment

management, stock replenishment, reporting, barcode scanning, cycle counting.
 Development process:
 o Gather requirements from functional consultants, business users, or
managers.
o Analyze and design solution (process flows, PL/SQL packages, APEX
pages).
o Develop and test.
o Review and deploy to production.

2. Experience working with users for testing

Answer:

 Conducted unit testing, UAT (User Acceptance Testing), and functional

testing with end users.
 Example: Developed an APEX dashboard for stock reporting; users tested
filter and export features.

3. Example of development

Answer:

 Requirement: Display all warehouse items below reorder level.

 Solution:
o Created an APEX interactive report with PL/SQL query to fetch low-
stock items.
o Added dynamic filters, conditional highlighting, and export
functionality.
o Tested with end users; implemented feedback.

4. Query tooling for performance improvement

Answer:

 Experience using EXPLAIN PLAN, SQL Trace, TKPROF, DBMS_XPLAN to

analyze query performance.
 Optimized queries by:
o Adding indexes.
o Using bind variables.
o Refactoring queries to avoid unnecessary joins and subqueries.
5. Solving performance issues on APEX pages

Answer:

 Enable APEX debug mode.

 Identify slow regions via page rendering statistics.
 Optimize SQL queries for affected regions.
 Use pagination, collections, and caching.
 Avoid unnecessary computations in regions or PL/SQL processes.

6. Debugging a slow page or region

Answer:

 Break down by region queries in APEX debug output.

 Check region rendering time, DML processes, and dynamic actions.
 Use DBMS_OUTPUT or logs in PL/SQL blocks.

7. Setting value of a page item

Answer:

 In JavaScript/jQuery:

$s('P1_ITEM', 'value'); // Set value

$v('P1_ITEM'); // Get value

 In PL/SQL:

:P1_ITEM := 'value';

8. Populating APEX collections

Answer:

 Method 1:
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name,
p_query);
 Method 2: APEX_COLLECTION.ADD_MEMBER in loops or bulk collect.
9. Experience with PL/SQL packages, functions, procedures

Answer:

 Created reusable packages with procedures and functions for warehouse

calculations, API integration, and bulk updates.
 Used IN, OUT, IN OUT parameters.

10. Using output parameters

Answer:

 Output parameters allow returning data from a procedure/function to the caller.

PROCEDURE get_emp_name(p_empno IN NUMBER, p_name OUT VARCHAR2);

11. Bulk collect / bulk processing

Answer:

 Bulk operations reduce context switching and improve performance.

 Handle exceptions with SAVE EXCEPTIONS and FORALL block.

Example:

FORALL i IN l_emp.FIRST..l_emp.LAST SAVE EXCEPTIONS

UPDATE emp SET sal = sal * 1.1 WHERE empno = l_emp(i).empno;
EXCEPTION
WHEN OTHERS THEN
FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Error at: ' || SQL
%BULK_EXCEPTIONS(j).ERROR_INDEX);
END LOOP;

12. No-copy option in OUT parameters

Answer:

 NOCOPY allows pass-by-reference instead of pass-by-value for large collections.

 Reduces memory overhead and improves performance.
13. Advanced SQL – XML/JSON

Answer:

 Used XMLTYPE, JSON_OBJECT, JSON_TABLE to process hierarchical and

web-service data in SQL queries.

14. RESTful API experience

Answer:

 Developed/consumed APEX RESTful services.

 Integrated external system APIs for invoices, orders, and inventory.
 Used UTL_HTTP and PL/SQL packages for API calls.

15. Calling external API in APEX

Answer:

 Define RESTful Web Service in APEX.

 Use APEX_WEB_SERVICE.MAKE_REQUEST or AJAX to consume APIs.
 Can be synchronous or asynchronous.

16. Apex Web Service / RESTful service package

Answer:

 APEX provides RESTful services using PL/SQL for CRUD operations.

 Can be exposed for external applications.

17. Using JavaScript in APEX

Answer:

 Dynamic actions, client-side validations, interactive grids, DOM manipulation.

 Examples: Setting page items, hiding/showing regions, custom formatting.
18. Difference: Oracle Forms vs Oracle APEX
Feature Oracle Forms Oracle APEX

Deployment Client-server Web-based

UI Limited customization Modern web UI

Integration Harder with web services Easy with REST APIs

Mobility Desktop only Mobile responsive

19. APEX version

Answer:

 Use latest stable version available (e.g., APEX 23.x).

 Check via: Help → About → Version in workspace