ODI Interview Question and Answers 29 September 2025

 

Q: What is the basic difference between ETL and ELT approach?

A: ETL extracts and transforms data outside the DB before loading, while ELT loads data into the DB first and leverages DB power for transformations.

Q: Suppose you want to load a file from a Unix server or another location to your database, which knowledge modules will you use?

A: I would use an LKM (Load Knowledge Module) for file-to-SQL or file-to-DB transfer, and an IKM (Integration Knowledge Module) to load into the target.

Q: Is there any other way apart from using the LKMs?

A: Yes, we can use DB links, external tables, or ODI procedures with shell scripts to transfer files.

Q: Suppose you want to use the external table, what setup is required?

A: We need to define the directory in Oracle, create the external table pointing to the file, and grant proper read/write permissions.

Q: If my file has a header and I want to read from row 2, what can we do?

A: We can configure the SKIP_ROWS parameter in the LKM or define the external table with the SKIP clause.

Q: What is the use of context in ODI?

A: Context allows us to run the same mapping in different environments (Dev, Test, Prod) by switching logical to physical schema mappings.

Q: Suppose I want to load data into two dev databases with same structure, how can I do that?

A: We can create multiple targets in the mapping or use multiple scenarios/packages to run the same load into both databases.

Q: In an ODI procedure, what is the use of Tag option?

A: Tags are used to organize and categorize procedures for easier management and searching.

Q: What is the difference between Command on Source and Command on Target?

A: Command on Source executes on the source technology to fetch data, while Command on Target executes on the target system to load or transform data.

Q: How to use variable values from Command on Source in Command on Target?

A: We can define variables in Command on Source and reference them in Command on Target using ODI substitution method (e.g. #VAR1).

Q: You have an employee table, delete where id=1 then insert from temp. If insert fails, rollback delete. How?

A: We can use ODI transaction control or wrap both delete and insert in a single procedure with commit/rollback handling.

Q: How to execute a scenario from a package and pass variables?

A: Use the OdiStartScen command in the package and pass variables through the command options or ODI variables.

Q: What is the difference between KO and OK step in package?

A: OK executes the next step only if the previous one succeeds, KO executes the next step if the previous one fails.

Q: What are slowly changing dimensions (SCD)?

A: SCD handle changes in dimension data over time – Type 1 overwrites, Type 2 adds history, Type 3 stores partial history.

Q: How to implement SCD2 in ODI mapping?

A: Use the ODI IKM Slowly Changing Dimension, which handles effective dates, surrogate keys, and inserts new rows for changes.

Q: If active row is from 1-Jan-2025 to 9999 and new record comes on 29-Sep, how to end old record on 28-Sep?

A: We update the end date of the old record to 28-Sep and insert a new record starting 29-Sep using SCD2 logic.

Q: What is Change Data Capture (CDC)?

A: CDC captures only changed data instead of full loads, improving efficiency. ODI supports CDC via journals and subscribers.

Q: What is the role of subscriber in CDC?

A: Subscribers consume change data from journal tables, allowing multiple consumers for different use cases.

Q: When executing mapping with CDC, does it select from source or JV$/JV$D tables?

A: With CDC enabled, ODI selects from JV$ or JV$D change tables instead of the source table.

Q: Have you worked on Oracle table partitions?

A: Yes, partitions improve query performance and manageability by splitting large tables into smaller pieces.

Q: Have you heard about optimization hints?

A: Yes, optimization hints are directives in SQL that influence the optimizer’s choice of execution plan.

Q: What is the use of APPEND hint?

A: APPEND hint enables direct-path inserts, bypassing buffer cache, improving bulk insert performance.

Q: What are the disadvantages of APPEND hint?

A: It generates more redo/undo, can lock segments, doesn’t reuse free space, and may require parallel DML settings.

Q: Suppose I need to update 100k rows and commit every 10k. How can we achieve this?

A: We can use PL/SQL loops with COMMIT after every 10k updates, or ODI procedures with commit control.

Q: How to check if a query is taking more time today?

A: Check V$SESSION and V$SQL for elapsed time, AWR reports, execution plan changes, or ODI operator logs for runtime comparison.

Q: In Explain Plan, what are Nested Loops, Hash Join, Sort Merge Join?

A: Nested Loops: row-by-row with index, best for small tables. Hash Join: builds hash table, good for large tables. Sort Merge: sorts and merges, good if data already sorted.

Click here for more details

SQL and PLSQL Interview Questions - 09 Sep 2025

Enhancements in Oracle 12c vs 11g

1. Multitenant Architecture (CDB & PDBs)
2. In-Memory Column Store
3. Automatic Data Optimization & Heat Map
4. Advanced Partitioning Enhancements
5. Improved Security (Data Redaction, Unified Auditing)
6. Enhanced PL/SQL Features
7. Online Operations (reduced downtime)
8. Sharding (12cR2)
9. Adaptive Query Optimization, SQL pattern matching, better RMAN features

Summary: 11g had standalone DBs; 12c introduced multitenancy, in-memory, ILM, enhanced security, and adaptive optimization.

JSON Enhancements in 12c

- Native storage in VARCHAR2/CLOB/BLOB with IS JSON constraint.
- Functions: JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_TABLE.
- Indexing support for JSON attributes.
- PL/SQL APIs: JSON_OBJECT_T, JSON_ARRAY_T, etc.
- Integration with REST/Big Data.
11g: No JSON support; 12c: introduced JSON features.

Advantage of JSON Data Type

- Efficient binary storage (less space, faster parsing).
- Faster query performance (no repeated text parsing).
- Automatic validation of JSON data.
- Better indexing (native JSON indexes).
- Seamless integration with modern apps & REST APIs.
- Future-proof (aligned with PostgreSQL/MySQL/MongoDB).

JSON Evolution: 11g vs 12c vs 21c

11g: No JSON support.
12c: JSON stored in VARCHAR2/CLOB/BLOB + IS JSON constraint, slower.
21c+: Native JSON data type, binary format, faster, validated, better indexing.

VARCHAR2 JSON vs JSON Datatype

- VARCHAR2: Flexible, works in 12c, but slower, more storage, manual validation.
- JSON datatype: 21c+, faster, compact, validated automatically, better indexing, but less flexible and requires newer version.

Pluggable Database (PDB)

- PDB is a portable collection of schemas & objects inside a Container Database (CDB).
- CDB has root (CDB$ROOT), seed (PDB$SEED), and multiple PDBs.
Advantages: Consolidation, easier management, portability, rapid provisioning, isolation.
Introduced in Oracle 12c.

User vs Schema

- User: Database account with authentication, owns objects.
- Schema: Collection of objects owned by a user.
- Creating a user automatically creates a schema with same name.
Example: CREATE USER HR; -> Creates HR schema where HR's tables reside.

Advantage of Materialized View vs Table

- Performance boost (precomputed joins/aggregations).
- Automatic refresh (ON COMMIT/ON DEMAND).
- Query rewrite for faster execution (transparent to apps).
- Less manual effort than summary tables.
Disadvantage: Data may not be real-time; overhead for refresh logs.

Temporary Table & Types

- Temporary table stores session/transaction-specific data in TEMP tablespace.
Types:
1. ON COMMIT DELETE ROWS: Data cleared after transaction.
2. ON COMMIT PRESERVE ROWS: Data persists till session ends.
Definition is permanent, data is temporary.

GTT vs Normal Table

- GTT definition permanent, data temporary & session-specific.
- Stored in TEMP tablespace, auto-cleared.
- Less redo/undo overhead.
- Normal table stores permanent data, visible to all users, in permanent tablespace.

Sharing Temporary Table Data

- Definition of GTT is global, shared across sessions.
- Data inside GTT is private per session.
- You cannot see my temporary table data, and I cannot see yours, even in the same database.

Click here for detailed Answers

ODI Interview Question and Answers 04 September 2025

Q: Can you tell me the architecture of ODI?

A: ODI follows a modular architecture with components:
- ODI Studio (design-time client for mappings, load plans, scenarios).
- Repositories (Master & Work) to store metadata.
- ODI Agent (Standalone/Java EE) to execute jobs.
- Topology for defining physical/logical data servers.
- Scenarios & Load Plans for execution in different contexts.

Q: Suppose you are migrating from 11g to 12c. What challenges do you face and how to handle that?

A: Migration challenges include:
- Repository upgrade (Master/Work migration scripts).
- Topology & security changes.
- ODI 12c introduces declarative mappings instead of interfaces.
Approach: Backup repos, run ODI upgrade assistant, validate mappings, test load plans, resolve deprecated KM usage.

Q: Suppose one load plan got failed in production. How to find out the issue and fix that?

A: Steps:
- Check ODI Operator logs for error details.
- Verify agent logs.
- Check source/target connectivity.
- Rerun failed step from restartability point.
- Fix root cause (data issue, connection, KM error) before re-execution.

Q: How to integrate ODI with Oracle Cloud or any third-party API?

A: Approaches:
- Use ODI Technology: RESTful Web Services / SOAP.
- Leverage ODI Procedure with Groovy/Java for API calls.
- Use Staging tables for JSON/XML parsing.
- For Oracle Cloud, configure topology with Oracle DBaaS/Autonomous DB endpoints.

Q: How to track the DML operation in SQL?

A: Methods:
- Triggers to log INSERT/UPDATE/DELETE in audit table.
- Use ORA_ROWSCN pseudocolumn.
- Use Flashback Data Archive for history.
- LogMiner for redo log mining.

Q: What is the substitute of API in ODI?

A: In ODI, Knowledge Modules (KMs) act as reusable templates like APIs.
Also, ODI SDK (Java/Groovy) provides programmatic API for automation.

Q: What is load balancing?

A: Load balancing distributes workloads across multiple servers/agents to avoid bottlenecks and optimize performance.

Q: What is data quality control?

A: Data Quality Control ensures data correctness, completeness, consistency.
- Implemented via ODI CKM (Check Knowledge Module).
- Example: Constraint checks, duplicate checks, referential integrity.

Q: How to load valid record in one table and invalid record in another table?

A: Use CKM for constraint validation.
- Mapping flow → valid rows to Target_Valid, invalid rows (errors) to Target_Error.
- Can also implement filter conditions in mapping.

Q: Suppose we have 3 sources (API, JSON, CSV) and want to load into a single DB table. What is the approach?

A: Steps:
- Extract API → staging table.
- Load JSON using ODI JSON driver → staging.
- Load CSV via File technology → staging.
- Union all staging data → final target table.

Q: What are the views?

A: Views are logical representations of queries stored in DB.
- Types: Simple, Complex, Materialized.
- Used for abstraction, security, performance (materialized).

Q: What is contextual agent in ODI?

A: A logical agent linked with context.
- ODI resolves to physical agent based on execution context (Dev/Test/Prod).
- Useful for portability of scenarios.

Q: What are the different types of facts?

A: Types:
- Additive facts (summable across all dimensions).
- Semi-additive (summable across some dimensions).
- Non-additive (not summable, e.g., ratios).
- Derived/Calculated facts.

Q: What are the different types of dimensions?

A: Types:
- Conformed, Junk, Degenerate, Role-playing, Slowly Changing Dimension (SCD), Static, Time.

Q: What are the different ways of prompting?

A: Ways include:
- ODI Variables (prompt at runtime).
- User input parameters in Load Plans.
- BI/Reporting tool prompts.

Q: What is SCD?

A: Slowly Changing Dimension — handles changes in dimension attributes.
- Type 1: Overwrite.
- Type 2: History tracking with versioning.
- Type 3: Limited history with previous/current fields.

Q: How to load multiple files into a DB table?

A: Methods:
- ODI File technology with wildcards.
- ODI procedure using UTL_FILE/OS command to iterate files.
- Use staging and append to target.

Q: How to handle incremental data load?

A: Methods:
- ODI CDC (Change Data Capture).
- Using LAST_UPDATE_DATE column.
- Using ODI variables to capture max date/key.

Q: What about lookups and joins?

A: In ODI mappings:
- Lookups are defined as lookup components for reference data.
- Joins can be inner/outer, done via mapping join components.
- Pushdown optimization executes joins at source DB.

Q: How to load data from XML to relational DB table?

A: Use ODI XML technology:
- Define XML model via XSD.
- Reverse engineer.
- Map XML elements to target relational table.
- Load via XML driver/KM.

Q: What are different types of Oracle Data Warehouses and their architecture?

A: Types:
- Enterprise Data Warehouse (EDW).
- Data Mart (department-level).
- Operational Data Store (ODS).
- Architecture: Source → Staging → DW (fact/dim) → Data Marts → BI Layer.

Q: What are partition and its types?

A: Partitioning splits large tables for performance.
Types:
- Range, List, Hash, Composite (Range-Hash, Range-List).
- Interval (auto range).
- Reference (child follows parent partition).
- System, Virtual column.

Q: Can you tell me the cursor?

A: A cursor is a pointer to the result set of a query in PL/SQL.
- Types: Implicit, Explicit, Cursor FOR loop, Parameterized.
- Attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
- Used to fetch/process rows one by one in PL/SQL.

Click here for detailed answers

More Question and Answers

SQL and PL/SQL Query for the interview - 28 August 2025

 CREATE TABLE employees (

  emp_id     NUMBER,

  emp_name   VARCHAR2(100),

  manager_id NUMBER

);

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (1, 'Alice', NULL);  -- CEO

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (2, 'Bob', 1);       -- Reports to Alice

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (3, 'Carol', 1);     -- Reports to Alice

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (4, 'Dave', 2);      -- Reports to Bob

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (5, 'Eve', 2);       -- Reports to Bob

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (6, 'Frank', 3);     -- Reports to Carol

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (7, 'Grace', 4);     -- Reports to Dave

select * from employees;

SELECT 

    LPAD(' ', LEVEL * 2) || emp_name AS employee_hierarchy,

    emp_id,

    manager_id,

    LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

SELECT emp_name, LEVEL

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR emp_id = manager_id;

SQL and PL/SQL Query for the interview - 02 July 2025 - 8

 select * from emp order by deptno, sal desc;

--Maximum Salary

select max(sal) from emp;

--3rd highest salary

select ename, sal, rank from (

select ename, sal, dense_rank() over(order by sal desc) as rank from emp)

where rank=3;

--more than avg of their own demartment

select * from emp e1, 

(select deptno, avg(sal) as avg from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.avg;

--can we user case in where condition

SELECT *

FROM emp

WHERE

  CASE 

    WHEN job = 'MANAGER' THEN 1

    WHEN sal > 100000 THEN 2

    ELSE 0

  END = 2;

  

--Can we use decode in where condition

SELECT *

FROM emp

WHERE DECODE(deptno, 10, 'Y', 20, 'P', 'N') in ('P','N');

--Identify the Slow SQLs

SELECT *

FROM (

  SELECT sql_id, executions, elapsed_time,executions avg_time, sql_text

  FROM v$sql

  WHERE elapsed_time

  /*executions*/ IS NOT NULL

  ORDER BY avg_time DESC

)

WHERE ROWNUM <= 10;

--Check if the Package is Still Running or Hung

SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.event, s.wait_class, s.seconds_in_wait

FROM v$session s

WHERE s.username = 'ODI_SRC'

  AND s.status = 'ACTIVE';

--Check for Locks or Blocking Sessions

SELECT

    a.sid blocker_sid,

    b.sid blocked_sid,

    a.username blocker_user,

    b.username blocked_user,

    b.event

FROM v$session a, v$session b

WHERE a.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)

  AND b.blocking_session = a.sid;

--Check for Recent SQL/PLSQL Errors

SELECT *

FROM dba_errors

WHERE name = 'GET_EMP'

  AND owner = 'ODI_SRC';

--Check System Resource Usage

SELECT * FROM v$resource_limit

WHERE resource_name IN ('sessions', 'processes');

--Or

SELECT * FROM v$sysmetric_summary

WHERE metric_name LIKE '%CPU%' OR metric_name LIKE '%Memory%';

--Example: Materialized View

CREATE MATERIALIZED VIEW emp_mv

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

AS

SELECT deptno, COUNT(*) emp_count

FROM emp

GROUP BY deptno;

--How to Check If a View Is Updatable?

SELECT view_name, updatable

FROM user_updatable_columns

WHERE view_name = 'EMP_VIEW';

--Index may be missing or not used due to function or implicit conversion

create index ixd_hiredate on emp(hiredate);

explain plan for

select * from emp where to_char(HIREDATE)='23-12-80';

select * from table(dbms_xplan.display);

--Declared a procedure in the package body and it is not declared in the package specification. Will that package run or not?

CREATE OR REPLACE PACKAGE my_pkg AS

  PROCEDURE proc_pub;

END my_pkg;

CREATE OR REPLACE PACKAGE BODY my_pkg AS

  -- Public procedure (declared in spec)

  PROCEDURE proc_pub IS

  BEGIN

    proc_priv; -- calling private proc

    DBMS_OUTPUT.PUT_LINE('Public procedure');

  END;

  -- Private procedure (not in spec)

  PROCEDURE proc_priv IS

  BEGIN

    DBMS_OUTPUT.PUT_LINE('Private procedure');

  END;

END my_pkg;

EXEC my_pkg.proc_pub;

drop PACKAGE my_pkg;

drop PACKAGE BODY my_pkg;

set serveroutput on;

--3rd highest salary

SELECT sal

FROM (

  SELECT DISTINCT sal

  FROM emp

  ORDER BY sal DESC

)

WHERE ROWNUM =3;

--get employees whose salary is more than the average salary of their own department:

SELECT empno, ename, deptno, sal

FROM emp e

WHERE sal > (

  SELECT AVG(sal)

  FROM emp

  WHERE deptno = e.deptno

);

--mutating table error

CREATE OR REPLACE TRIGGER trg_emp_update

AFTER UPDATE ON emp

--for each row -it cause mutating table error

declare

v_count number;

BEGIN

  -- Valid: statement-level can query emp

  select count(*) into v_count from emp;

  dbms_output.put_line(v_count);

  --INSERT INTO log_table SELECT * FROM emp WHERE deptno = 10;

END;

select * from emp;

update emp set sal=250000 where empno=8376;

Oracle SQL and PL/SQL Interview and Concept Notes - 02 July 2025 - 7

What is a Cursor and Its Types

In PL/SQL, a cursor is a pointer to the result set of a query. It allows row-by-row processing.

Types of Cursors:
1. Implicit Cursor – Automatically created for single-row queries.
2. Explicit Cursor – Developer-defined for multi-row queries.
3. Cursor FOR Loop – Simplified looping over query results.
4. Parameterized Cursor – Accepts input parameters.
5. REF Cursor – Dynamic pointer to query results.

Example of Explicit Cursor:
DECLARE
  CURSOR c_emp IS SELECT empno, ename FROM emp;
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_empno, v_ename;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
  END LOOP;
  CLOSE c_emp;
END;

Cursor Attributes

1. %FOUND – TRUE if last fetch/DML affected rows.
2. %NOTFOUND – TRUE if no rows fetched/affected.
3. %ROWCOUNT – Number of rows fetched/affected.
4. %ISOPEN – TRUE if explicit cursor is currently open.

Example:
UPDATE emp SET sal = sal + 100 WHERE deptno = 10;
IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
ELSE
  DBMS_OUTPUT.PUT_LINE('No rows updated.');
END IF;

Mutating Table Error

Occurs when a row-level trigger tries to read/write from the same table it's modifying.

Error: ORA-04091

Solutions:
1. Use statement-level triggers.
2. Use compound triggers.
3. Use :NEW and :OLD values.
4. Use autonomous transaction procedures.

Example (Avoiding Mutating Error):
Use compound trigger with memory table to collect and process after statement.

Query for Employees Earning More Than Department Average

SELECT empno, ename, deptno, sal
FROM emp e
WHERE sal > (
  SELECT AVG(sal) FROM emp WHERE deptno = e.deptno
);

Types of Analytical Functions

1. Ranking: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
2. Aggregate: SUM(), AVG(), COUNT(), MIN(), MAX()
3. Value: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()
4. Distribution: PERCENT_RANK(), CUME_DIST()
5. Statistical: STDDEV(), VARIANCE(), CORR(), COVAR_POP(), COVAR_SAMP()

DENSE_RANK() Function

DENSE_RANK() assigns rank to rows without skipping ranks for ties.

Example:
SELECT sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rnk FROM emp;

3rd Maximum Salary Query

Method using DENSE_RANK():
SELECT sal FROM (
  SELECT sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
) WHERE rnk = 3;

Maximum Salary Query

SELECT MAX(sal) AS max_salary FROM emp;

To fetch employee(s) with max salary:
SELECT empno, ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);

Constraints and Types

1. PRIMARY KEY – Unique and NOT NULL.
2. UNIQUE – No duplicate values.
3. NOT NULL – Must contain value.
4. CHECK – Validates condition.
5. FOREIGN KEY – Enforces relationship with another table.

Yellow Interface

"Yellow interface" refers to ODI 11g's old data integration interface, which had a yellow UI. In ODI 12c, it's replaced by Mappings.

Bulk Collect

Used to fetch multiple rows into collections in one go to improve performance.

Example:
SELECT empno BULK COLLECT INTO v_empnos FROM emp;

Types of Errors in PL/SQL

1. Syntax Errors – Compile-time issues.
2. Compilation Errors – Incorrect declarations.
3. Runtime Errors – At execution time.
4. Logical Errors – Wrong result, no crash.
5. Server Errors – ORA-xxxxx codes.
6. User-Defined Errors – Raised using RAISE or RAISE_APPLICATION_ERROR.

Click for full details in ChatGPT

ODI and Oracle SQL Questions & Answers - 6

What is Index Rebuild in Oracle?

Index Rebuild is the process of re-creating an index to improve performance, remove fragmentation, and reclaim space.

SQL:
ALTER INDEX index_name REBUILD;
ALTER INDEX index_name REBUILD ONLINE;
ALTER INDEX index_name REBUILD PARALLEL 4;

Used after heavy DML, fragmentation, or logical corruption.

What is a Partition in Oracle?

Partitioning divides a large table or index into smaller, manageable pieces.
Types: Range, List, Hash, Interval, Composite.

SQL Example:
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

What is Index and Why We Use It?

An index improves the speed of data retrieval operations.

Types: B-Tree, Bitmap, Unique, Composite, Function-Based.

SQL Example:
CREATE INDEX emp_idx ON employees(emp_id);

Strategy to Update 10 Million Records in Oracle

Use batching and commit periodically:

PL/SQL Example:
DECLARE
  l_rows NUMBER := 1;
BEGIN
  WHILE l_rows > 0 LOOP
    UPDATE your_table
    SET col1 = 'new_value'
    WHERE condition AND ROWNUM <= 50000;
    l_rows := SQL%ROWCOUNT;
    COMMIT;
  END LOOP;
END;

Alternative: Use Parallel DML or CTAS + RENAME for better performance.

How to Debug a Slow Query in Oracle

Steps:
1. Check execution plan: EXPLAIN PLAN FOR <your_query>;
2. View plan: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
3. Gather stats: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE');
4. Check blocking sessions: SELECT * FROM v$session WHERE blocking_session IS NOT NULL;
5. Use AWR: @$ORACLE_HOME/rdbms/admin/awrrpt.sql (if licensed)

What is Gather Stats in Oracle?

Gathering statistics helps the optimizer choose efficient plans.

SQL:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

Identify Bad Records in ODI (e.g., Junk Address Data)

Steps:
1. Load data to staging table.
2. Use REGEXP_LIKE or CASE to validate address column.
3. Route bad data to error table.

SQL Validation Example:
CASE
  WHEN NOT REGEXP_LIKE(ADDRESS, '^[A-Za-z0-9,\.-\s]{5,}$') THEN 'INVALID'
  ELSE 'VALID'
END

Query errors:
SELECT * FROM ERR_CUSTOMER;

Run Sub-Packages in Parallel in ODI

Yes, use 'Fork' in ODI Package to run sub-packages B and C in parallel.
Use 'Join' if you want to synchronize after both complete.

Load from One Source to Multiple Targets in ODI

Supported ways:
1. Multiple targets in same mapping.
2. Use SPLIT component to route conditionally.

3. Use different mappings for complex cases and run via a package.