DEV Community: Wassim Ben saida

I Broke Production with a PL/SQL Loop. Here Are the 10 Steps I Skipped

Wassim Ben saida — Sat, 29 Aug 2026 14:47:01 +0000

The job was supposed to run for four minutes. At 7:40 the next morning it was still running, half the rows were updated, and I couldn't roll any of it back.

Here is the shape of what I wrote — simplified, but not by much:

BEGIN
  FOR r IN (SELECT item_id, qty FROM stock_lines) LOOP
    UPDATE stock_summary
       SET qty = qty + r.qty
     WHERE item_id = r.item_id;

    COMMIT;                      -- "so it doesn't fill the undo"
  END LOOP;
END;
/

It looks harmless. It is not. Three separate mistakes are stacked in those nine lines, and each one maps to a fundamental I had skipped:

One UPDATE per row. 400,000 rows meant 400,000 round trips between the PL/SQL engine and the SQL engine. That's the four minutes turning into eight hours.
COMMIT inside a cursor loop. Oracle needs undo data to keep serving my open cursor a consistent view. I kept committing it away, and somewhere past row 300,000 the job died with ORA-01555: snapshot too old.
No transaction boundary. Because I had committed 300,000 times, there was nothing to roll back to. The table was in a state that was neither "before" nor "after". We restored from backup.

The honest diagnosis wasn't "he doesn't know PL/SQL". It was that I had learned SQL in the order tutorials teach it syntax first, consequences never and then jumped straight to writing procedural code against a live database.

So I went back and did it properly, in order. Ten steps. The first seven are SQL, the language for asking questions. The last three are PL/SQL, which is what turns a query into a program living inside the database. Each has one diagram, and each is something that would have prevented some version of my bad night.

Where to practice: Oracle Live SQL runs in the browser with nothing to install, or install Oracle Database Free (23ai) with SQL Developer. Both ship with the HR sample schema used below.

Step 1 — The model comes before the syntax

A table is a set of rows. Each row is one fact ("employee 100 earns 24,000"). Each column is one attribute of that fact. There is no built-in order if you want rows in an order, you ask for it.

Two kinds of keys hold everything together:

Primary key — identifies a row uniquely. EMPLOYEE_ID in EMPLOYEES.
Foreign key — points at another table's primary key. DEPARTMENT_ID in EMPLOYEES points at DEPARTMENTS.

The distinction I got wrong for months: a foreign key is not a join. A join is something you write in a query. A foreign key is a constraint a rule the database enforces whether you ask for it or not. Insert an employee into department 999 when it doesn't exist, and Oracle refuses:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

That error is the database protecting you. Read the model before writing anything — DESCRIBE employees, then USER_CONSTRAINTS and USER_INDEXES. Ten minutes there saves an hour of debugging, and in my case would have told me that stock_summary.item_id had no index at all.

Step 2 — A SELECT doesn't run in the order you write it

You write:

SELECT   department_id, COUNT(*) AS nb
FROM     employees
WHERE    salary > 3000
GROUP BY department_id
HAVING   COUNT(*) > 2
ORDER BY nb DESC;

Oracle logically processes it in a different order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

This one fact explains most beginner errors:

-- fails: nb does not exist yet when WHERE runs
SELECT COUNT(*) AS nb FROM employees WHERE nb > 2 GROUP BY department_id;

-- works: ORDER BY runs after SELECT, so the alias is visible
SELECT COUNT(*) AS nb FROM employees GROUP BY department_id ORDER BY nb DESC;

WHERE can't see a SELECT alias because SELECT hasn't run yet. ORDER BY can, because it runs last. Learn the six steps and half your syntax errors stop being mysterious.

Step 3 — Filtering, and the NULL trap

WHERE keeps a row only when the condition is TRUE. Not FALSE, and here's the trap not UNKNOWN.

NULL is not zero and not an empty string. It means unknown. Any comparison against an unknown produces UNKNOWN, and those rows get dropped:

-- rows with NULL commission are returned by NEITHER query
SELECT * FROM employees WHERE commission_pct  = 0.2;
SELECT * FROM employees WHERE commission_pct != 0.2;

Both silently skip the same rows. In an aggregation feeding a report, that's a number that is quietly wrong rather than loudly broken. Use the dedicated tools:

WHERE commission_pct IS NULL                  -- the only way to test for NULL
SELECT NVL(commission_pct, 0)                 -- substitute a default
SELECT COALESCE(bonus, commission_pct, 0)     -- first non-NULL of the list

Two Oracle specifics worth knowing on day one:

Oracle treats an empty string '' as NULL. WHERE name = '' matches nothing, ever.
NOT IN against a subquery that returns even one NULL returns zero rows. Use NOT EXISTS; make it your default.

Step 4 — Joins: `ON` decides what matches, the join type decides who survives

Every join answers two separate questions, and conflating them is the classic bug.

What counts as a match? The ON clause.
What happens to rows with no match? The join type.

SELECT e.first_name, d.department_name
FROM   employees e
LEFT JOIN departments d ON d.department_id = e.department_id;

INNER JOIN — matched rows only.
LEFT JOIN — every employee, even one with no department (department columns come back NULL).
RIGHT JOIN — every department, even an empty one.
FULL OUTER JOIN — both sides, matched or not.

Three rules that matter in practice:

Alias every table (e, d) and prefix every column. In a five-table query, unqualified columns are how you join the wrong thing without noticing.

A filter on an outer-joined table in WHERE silently turns it into an inner join. To filter the optional side, the condition goes in ON:

LEFT JOIN departments d
       ON d.department_id = e.department_id
      AND d.location_id   = 1700      -- keeps the LEFT behaviour

Row count exploded after a join? Your join key isn't unique on one side. This is the one that had me "fixing" data that was never wrong — I was double-counting it. Count first, join second.

You'll meet Oracle's legacy (+) syntax in old code. Read it, don't write it.

Step 5 — Aggregation: many rows in, one row per group out

GROUP BY slices rows into buckets; aggregate functions collapse each bucket into one row: COUNT, SUM, AVG, MIN, MAX.

SELECT   d.department_name,
         COUNT(*)             AS headcount,
         ROUND(AVG(e.salary)) AS avg_salary
FROM     employees e
JOIN     departments d ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING   AVG(e.salary) > 3000
ORDER BY avg_salary DESC;

Two rules cover nearly everything:

Every column in SELECT that isn't inside an aggregate must be in GROUP BY, or you get ORA-00979: not a GROUP BY expression.
WHERE filters rows before grouping; HAVING filters groups after. Filter in WHERE whenever you can — fewer rows to group is also faster.

One subtlety that shows up in every audit: COUNT(*) counts rows, COUNT(column) counts non-NULL values in that column. The gap between the two numbers is usually the data-quality problem you were sent to find.

Step 6 — Subqueries, WITH, and window functions

A subquery is a query inside a query, and it can live in several places:

-- scalar: one row, one column
SELECT first_name, (SELECT MAX(salary) FROM employees) AS top_salary FROM employees;

-- inline view: a table you invented on the spot
SELECT * FROM (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id)
WHERE avg_sal > 5000;

-- named up front with WITH (a CTE) - far more readable
WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM   employees
  GROUP  BY department_id
)
SELECT d.department_name, a.avg_sal
FROM   dept_avg a
JOIN   departments d ON d.department_id = a.department_id
WHERE  a.avg_sal > 5000;

Make WITH your default. Three named CTEs beat one query nested four levels deep — for you now, and for whoever inherits it next year.

Then window functions, the step that separates casual SQL from professional SQL. GROUP BY reduces your rows; OVER () keeps them and adds a calculated column beside each one:

SELECT first_name,
       department_id,
       salary,
       RANK()            OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       ROUND(AVG(salary) OVER (PARTITION BY department_id))                     AS dept_avg
FROM   employees;

Every employee still comes back, now with their rank inside their department and the department average next to their own salary.

This is also where my incident really started. My "running total per item" requirement had a one-line answer — SUM(qty) OVER (PARTITION BY item_id ORDER BY line_date) — and because I didn't know it existed, I reached for a loop. Learn ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running SUM ... OVER, and a whole category of procedural code stops being necessary.

Step 7 — DML and transactions: nothing is real until you COMMIT

Reading is half the job. INSERT, UPDATE, DELETE and MERGE change data but your changes are invisible to everyone else until you commit them.

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 60;
SAVEPOINT before_delete;
DELETE FROM employees WHERE hire_date < DATE '1990-01-01';

ROLLBACK TO before_delete;   -- the DELETE never happened, the UPDATE survives
COMMIT;                      -- now the raise is permanent and public

MERGE deserves its own paragraph — "insert if new, update if existing" in a single statement, and it replaces an enormous amount of bad PL/SQL. Including mine:

MERGE INTO stock_summary t
USING (SELECT item_id, SUM(qty) qty FROM stock_lines GROUP BY item_id) s
   ON (t.item_id = s.item_id)
WHEN MATCHED     THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (item_id, qty) VALUES (s.item_id, s.qty);

That is the entire job I had written as a loop. One statement, one transaction, one COMMIT at the end — and fully reversible until then.

Three things that will save you:

Write the WHERE before the SET. An UPDATE with no WHERE updates every row. Better: run it as a SELECT first, check the count, then convert it.
DDL commits implicitly. One CREATE TABLE or TRUNCATE mid-work and your open transaction is committed for you, silently. No rollback available.
A transaction is your undo button. Committing inside a loop is throwing that button away, once per row. If the unit of work is "the whole file", then the transaction is the whole file.

Step 8 — Enter PL/SQL: every program is the same shape

SQL asks questions. It has no variables, no loops, no error handling. PL/SQL adds all three and runs inside the database, right next to the data.

Every PL/SQL program — anonymous block, procedure, function, trigger — is this shape:

DECLARE                                    -- optional
  v_name employees.first_name%TYPE;
BEGIN                                      -- required
  SELECT first_name INTO v_name
  FROM   employees
  WHERE  employee_id = 100;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION                                  -- optional
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee 100');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one row');
END;
/

Notice immediately:

SELECT ... INTO must return exactly one row. Zero raises NO_DATA_FOUND; two or more raise TOO_MANY_ROWS. Both are exceptions, not warnings.
%TYPE anchors your variable to the column's datatype. Widen FIRST_NAME to 60 characters next year and your code follows automatically. %ROWTYPE does the same for a whole row: v_emp employees%ROWTYPE;.
DBMS_OUTPUT.PUT_LINE is your console.log — but enable it first (SET SERVEROUTPUT ON, or the DBMS Output panel in SQL Developer). Silence usually means you forgot.
The trailing / tells the client "run this block". It's part of the tool, not the language.

Then the control structures, which will look familiar:

IF v_salary > 5000 THEN ... ELSIF ... ELSE ... END IF;
FOR i IN 1..10 LOOP ... END LOOP;
WHILE v_count < 10 LOOP ... END LOOP;
CASE v_grade WHEN 'A' THEN ... ELSE ... END CASE;

Step 9 — Cursors, and the mistake that cost me a night

A cursor is a pointer over a query's result set. The readable way to walk one is the implicit cursor FOR loop:

BEGIN
  FOR r IN (SELECT employee_id, salary FROM employees WHERE department_id = 60) LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' : ' || r.salary);
  END LOOP;
END;
/

No OPEN, no FETCH, no CLOSE, no %NOTFOUND Oracle handles it. Write the explicit form only when you truly need the control.

Now the lesson I paid for. Oracle runs two engines: the PL/SQL engine and the SQL engine. Every SQL statement inside a loop is a round trip between them — a context switch. Do it 400,000 times and you pay 400,000 times. That was my eight hours.

The fix is to move data in batches:

DECLARE
  TYPE t_line IS TABLE OF stock_lines%ROWTYPE;
  l_lines t_line;
  CURSOR c IS SELECT * FROM stock_lines;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_lines LIMIT 1000;   -- LIMIT protects your PGA
    EXIT WHEN l_lines.COUNT = 0;

    FORALL i IN 1 .. l_lines.COUNT
      UPDATE stock_summary
         SET qty = qty + l_lines(i).qty
       WHERE item_id = l_lines(i).item_id;
  END LOOP;
  CLOSE c;

  COMMIT;                         -- once, at the end. One unit of work.
END;
/

BULK COLLECT pulls many rows per switch. FORALL sends many DML statements per switch. Same logic, seconds instead of hours.

Look closely at where the COMMIT moved. My original committed inside the fetch loop, and that is exactly what produced ORA-01555: snapshot too old: Oracle needs undo data to keep serving the open cursor its consistent read, and I kept committing that undo away. If a batch really is too large for one transaction, you don't sprinkle commits — you make the job restartable, with a status column or a watermark table, so a rerun resumes cleanly instead of double-counting.

And the rule above all the others: the fastest PL/SQL is the PL/SQL you deleted. Before writing any loop, ask whether one UPDATE ... WHERE or one MERGE does the same job. Mine did. It took four minutes.

Step 10 — Named units: procedures, functions, packages, triggers

Anonymous blocks are for experiments. My eight-hour job was an anonymous block pasted into SQL Developer — no name, no version, no review, and nothing to point at afterwards. Real code gets a name and lives in the database.

CREATE OR REPLACE PROCEDURE raise_salary (
  p_employee_id IN employees.employee_id%TYPE,
  p_pct         IN NUMBER
) IS
BEGIN
  UPDATE employees
     SET salary = salary * (1 + p_pct/100)
   WHERE employee_id = p_employee_id;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Unknown employee: ' || p_employee_id);
  END IF;
END raise_salary;
/

A procedure does something. A function returns a value and is callable from SQL. A trigger fires by itself when a table changes — powerful, and easy to regret, because logic nobody can see is logic nobody can debug.

The unit you should actually ship is the package: a specification (the public contract) and a body (the implementation).

CREATE OR REPLACE PACKAGE hr_api AS
  PROCEDURE raise_salary(p_employee_id IN NUMBER, p_pct IN NUMBER);
  FUNCTION  headcount(p_department_id IN NUMBER) RETURN NUMBER;
END hr_api;
/

CREATE OR REPLACE PACKAGE BODY hr_api AS
  -- private helper, invisible outside the package
  FUNCTION employee_exists(p_id IN NUMBER) RETURN BOOLEAN IS ...

  PROCEDURE raise_salary(...) IS ... END;
  FUNCTION  headcount(...)   RETURN NUMBER IS ... END;
END hr_api;
/

Packages give you encapsulation, a stable public interface, session state, and one object to grant privileges on. They also stop you recompiling half the schema every time you change a line.

Finish with exceptions, and take this one seriously:

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ...
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;                      -- re-raise. Always.
END;

WHEN OTHERS THEN NULL; is the most damaging line in PL/SQL. It turns a failure into a success and hides it forever. Log it, then re-raise it.

What the post-mortem actually said

Not "he doesn't know PL/SQL". Something more specific, and more fixable: I had learned the syntax without the consequences, in whatever order the tutorials happened to present them.

Skip step 3 and you'll spend a week hunting a NULL. Skip step 7 and you'll commit something you can't take back. Skip step 9 and you'll write a nightly job that outlives the night — which is, precisely, what I did.

The compressed version, for the next person:

Symptom	Actual cause
Query returns nothing	A `NULL` in the filter, or `NOT IN` over a `NULL`
`ORA-00979: not a GROUP BY expression`	Non-aggregated column missing from `GROUP BY`
Row count exploded after a join	Join key isn't unique on one side
`LEFT JOIN` behaves like `INNER`	Filter on the optional table sits in `WHERE`, not `ON`
"My changes disappeared"	Never committed, or a DDL committed something else first
`ORA-01555: snapshot too old`	Committing inside a loop that is still fetching
`NO_DATA_FOUND` inside a procedure	`SELECT INTO` found zero rows
Job runs for hours	Row-by-row loop; needs `BULK COLLECT`/`FORALL`, or plain SQL

Open Live SQL, take the HR schema, and work the ten steps in order. One evening each. Ten evenings is the difference between writing queries and being trusted with a database at 3 a.m.

What was your production incident? Mine was a loop but I suspect the WHERE-less UPDATE wins the popular vote.

Building a Self-Healing Framework for Oracle EBS Concurrent Programs (Native PL/SQL, No External Tooling)

Wassim Ben saida — Fri, 28 Aug 2026 12:42:02 +0000

A hands-on walkthrough of a native PL/SQL framework that detects, corrects, and re-validates critical Oracle E-Business Suite concurrent programs after patches, DR tests, and switchovers — without any external automation tool.

The problem

Every Oracle E-Business Suite environment runs dozens of critical concurrent programs around the clock: workflow background processing, purge routines, interface loaders, report generators. After any sensitive operation — a security patch, a clone, a disaster-recovery (DR) test, a switchover — someone has to confirm that all of those programs came back and are running normally.

Traditionally that check is manual, slow, and depends on an expert who knows exactly which programs matter and how often each one should run. That expert becomes a bottleneck, and manual checks are easy to get wrong under time pressure.

This article describes a native PL/SQL framework that industrializes the check. It runs a full Detection → Correction → Re-validation cycle, on demand, entirely inside Oracle EBS — no external automation tool, no screen-scraping robot, no additional license.

Why native, not an interface robot?

Interface-driven automation (RPA) that drives EBS screens is a common first instinct. On Oracle EBS R12 it has a well-known weakness: screen automations break after patches an unexpected pop-up, an error message, or a shifted field freezes the robot. That is exactly the moment you most need the check to be reliable.

A low-level control should rest on what does not move: the concurrent-processing tables and the FND submission APIs. That is what this framework does.

Architecture at a glance

The framework has three layers.

1. Data layer — configuration and journaling tables (generic prefix XMON):

Table	Purpose
`XMON_CRITICAL_PROGRAMS`	Registry of monitored programs: expected user/responsibility, absence thresholds, enable and auto-resubmit flags.
`XMON_CRITICAL_ARGUMENTS`	Expected arguments per program, used when resubmitting.
`XMON_CONTROL_RUNS`	Header of each control execution: type, window, status, anomaly counters.
`XMON_CONTROL_RESULTS`	Per-request detail: phase, status, severity, correction-required flag.
`XMON_CORRECTION_AUDIT`	Trace of each corrective action: program, mode (simulation/real), submitted request, status.

2. Processing layer — three PL/SQL packages:

Package	Concurrent program	Function
`XMON_MONITOR_PKG`	Global Control	Collects requests in the window, compares them to the registry, flags anomalies.
`XMON_CORRECTIVE_PKG`	Targeted Correction	Resubmits authorized failing programs with their original identity and arguments.
`XMON_VALIDATION_PKG`	Final Check	Re-runs a control after correction to confirm anomalies cleared.

3. Exposure layer — the three packages are registered as standard concurrent programs and attached to a request group, so operators launch them from the normal Submit Request window. No SQL knowledge is needed for day-to-day use.

The three phases chain into one on-demand cycle:

The data model

Here is the core registry table, trimmed to the columns that matter for this article.

CREATE TABLE xmon_critical_programs (
    critical_program_id        NUMBER          NOT NULL,
    enabled_flag               VARCHAR2(1)  DEFAULT 'Y' NOT NULL,
    criticality_code           VARCHAR2(30) DEFAULT 'CRITICAL' NOT NULL,
    application_short_name     VARCHAR2(50)    NOT NULL,
    concurrent_program_name    VARCHAR2(100)   NOT NULL,
    user_concurrent_program_name VARCHAR2(240),
    expected_user_name         VARCHAR2(100)   NOT NULL,
    expected_resp_key          VARCHAR2(100)   NOT NULL,
    expected_resp_appl_short   VARCHAR2(50)    NOT NULL,
    max_absence_minutes        NUMBER,
    allow_auto_resubmit_flag   VARCHAR2(1)  DEFAULT 'N' NOT NULL,
    warning_is_blocking_flag   VARCHAR2(1)  DEFAULT 'N' NOT NULL,
    business_description       VARCHAR2(4000),
    CONSTRAINT xmon_critical_programs_pk PRIMARY KEY (critical_program_id),
    CONSTRAINT xmon_critical_programs_u1 UNIQUE (concurrent_program_name),
    CONSTRAINT xmon_critical_programs_ck1 CHECK (enabled_flag IN ('Y','N')),
    CONSTRAINT xmon_critical_programs_ck3 CHECK (allow_auto_resubmit_flag IN ('Y','N'))
);

Two flags carry the whole safety model:

enabled_flag — is this program monitored? Set on every program you want under surveillance.
allow_auto_resubmit_flag — may the framework resubmit it automatically? This is a safety lock, defaulting to N. Only idempotent programs with no dangerous side effects should be set to Y. Sensitive programs (accounting posts, external file transfers, purges) stay detected and reported but never auto-resubmitted — a human decides.

max_absence_minutes drives absence detection. A NULL value means no absence check — appropriate for manually launched or irregular programs.

Phase 1 — Detection

The Global Control entry point mirrors the signature of a standard concurrent program (errbuf/retcode first), so it can run both as a concurrent request and as an anonymous block.

PROCEDURE run_global_control(
    errbuf                  OUT NOCOPY VARCHAR2,
    retcode                 OUT NOCOPY VARCHAR2,
    p_date_from             IN VARCHAR2,
    p_date_to               IN VARCHAR2,
    p_include_normal        IN VARCHAR2 DEFAULT 'Y',
    p_auto_correct          IN VARCHAR2 DEFAULT 'N',
    p_simulation            IN VARCHAR2 DEFAULT 'Y',
    p_operation_type        IN VARCHAR2 DEFAULT NULL,
    p_operation_reference   IN VARCHAR2 DEFAULT NULL
);

Dates arrive as strings and are parsed with the EBS canonical converter, which safely falls back to a default window when the input is empty or malformed:

l_date_from := NVL(fnd_date.canonical_to_date(p_date_from), TRUNC(SYSDATE) - 1);
l_date_to   := NVL(fnd_date.canonical_to_date(p_date_to),   SYSDATE);

Detection compares actual concurrent requests against the registry and flags two anomaly families:

Execution failure — the program ran but completed in error (phase_code = 'C', status_code = 'E').
Abnormal absence — the program has not run for longer than its max_absence_minutes threshold.

A read-only query that reproduces the heart of the detection logic:

SELECT p.concurrent_program_name,
       MAX(r.actual_start_date)                                   AS last_run,
       ROUND((SYSDATE - MAX(r.actual_start_date)) * 24 * 60)      AS age_minutes,
       p.max_absence_minutes,
       CASE
         WHEN p.max_absence_minutes IS NOT NULL
          AND (SYSDATE - MAX(r.actual_start_date)) * 24 * 60
              > p.max_absence_minutes
         THEN 'ABSENCE'
         ELSE 'OK'
       END                                                        AS absence_verdict
FROM   xmon_critical_programs p
JOIN   fnd_concurrent_programs cp
       ON cp.concurrent_program_name = p.concurrent_program_name
LEFT   JOIN fnd_concurrent_requests r
       ON r.concurrent_program_id = cp.concurrent_program_id
      AND r.actual_start_date > SYSDATE - 30
WHERE  p.enabled_flag = 'Y'
GROUP  BY p.concurrent_program_name, p.max_absence_minutes
ORDER  BY 1;

Each run produces a Control Run ID that links every downstream phase, plus a report listing the anomalies.

Calibrating thresholds

Set max_absence_minutes from observed frequency, not from a guess. A practical rule is two to three times the average interval between runs over the last 30 days:

SELECT p.concurrent_program_name,
       COUNT(r.request_id)                        AS runs_30d,
       ROUND(AVG(gap_minutes))                    AS avg_gap_min,
       ROUND(MAX(gap_minutes))                    AS max_gap_min,
       ROUND(MAX(gap_minutes) * 2)                AS suggested_threshold
FROM   xmon_critical_programs p
JOIN   fnd_concurrent_programs cp
       ON cp.concurrent_program_name = p.concurrent_program_name
LEFT   JOIN (
         SELECT concurrent_program_id, request_id,
                (actual_start_date - LAG(actual_start_date)
                   OVER (PARTITION BY concurrent_program_id
                         ORDER BY actual_start_date)) * 24 * 60 AS gap_minutes
         FROM   fnd_concurrent_requests
         WHERE  actual_start_date > SYSDATE - 30
       ) r ON r.concurrent_program_id = cp.concurrent_program_id
GROUP  BY p.concurrent_program_name
ORDER  BY runs_30d DESC;

Programs with zero runs in 30 days should not be placed under absence monitoring until operations confirms they are still expected to run.

Phase 2 — Correction

Correction reads the anomalies of a given Control Run ID and resubmits eligible programs.

PROCEDURE run_targeted_correction(
    errbuf            OUT NOCOPY VARCHAR2,
    retcode           OUT NOCOPY VARCHAR2,
    p_control_run_id  IN VARCHAR2,
    p_simulation      IN VARCHAR2 DEFAULT 'Y'
);

A program is resubmitted only if all three conditions hold:

enabled_flag = 'Y'
allow_auto_resubmit_flag = 'Y'
its expected arguments are known (or it takes none)

If any gate fails, the program is reported but never touched a human decides:

The resubmission reuses the original identity via fnd_global.apps_initialize and fnd_request.submit_request. Note the pattern: resolve IDs into variables first — you cannot inline a SELECT as an argument in a PL/SQL call.

DECLARE
  l_user_id      NUMBER;
  l_resp_id      NUMBER;
  l_resp_appl_id NUMBER;
  l_request_id   NUMBER;
BEGIN
  SELECT user_id INTO l_user_id
  FROM   fnd_user
  WHERE  user_name = 'BATCH_USER';               -- expected_user_name

  SELECT responsibility_id, application_id
  INTO   l_resp_id, l_resp_appl_id
  FROM   fnd_responsibility
  WHERE  responsibility_key = 'SYSTEM_ADMINISTRATOR'   -- expected_resp_key
  AND    ROWNUM = 1;

  fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

  l_request_id := fnd_request.submit_request(
    application => 'XXAPP',                      -- application_short_name
    program     => 'XMON_SAMPLE_PROGRAM',        -- concurrent_program_name
    description => NULL,
    start_time  => NULL,
    sub_request => FALSE);

  IF l_request_id = 0 THEN
    DBMS_OUTPUT.PUT_LINE('SUBMIT FAILED: ' || fnd_message.get);
  ELSE
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Request ID = ' || l_request_id);
  END IF;
END;
/

Every action is written to XMON_CORRECTION_AUDIT with a simulation_flag. Running with p_simulation = 'Y' records what would happen without submitting anything — always do this before the real run.

Safety lock in practice. allow_auto_resubmit_flag defaults to N. Only programs you have explicitly reviewed as safe are set to Y. This keeps automatic remediation narrow and auditable.

Phase 3 — Re-validation

After correction, the Final Check re-runs an analysis over the same window, tied to the parent Control Run ID, and confirms the corrected programs are back to normal.

PROCEDURE run_final_check(
    errbuf                  OUT NOCOPY VARCHAR2,
    retcode                 OUT NOCOPY VARCHAR2,
    p_parent_control_run_id IN VARCHAR2,
    p_date_from             IN VARCHAR2,
    p_date_to               IN VARCHAR2,
    p_include_normal        IN VARCHAR2 DEFAULT 'Y'
);

When the final check reports the previously failing program as NORMAL, the cycle is closed: the framework has detected, corrected, and verified on its own.

Running the full cycle from SQL

You can drive the whole chain from an anonymous block for testing. In production the same three programs are launched from the Submit Request screen.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_errbuf  VARCHAR2(4000);
  l_retcode VARCHAR2(100);
BEGIN
  xmon_monitor_pkg.run_global_control(
     errbuf                => l_errbuf,
     retcode               => l_retcode,
     p_date_from           => TO_CHAR(SYSDATE - 1, 'YYYY/MM/DD HH24:MI:SS'),
     p_date_to             => TO_CHAR(SYSDATE,     'YYYY/MM/DD HH24:MI:SS'),
     p_include_normal      => 'N',
     p_auto_correct        => 'N',
     p_simulation          => 'Y',
     p_operation_type      => 'POST_PATCH',
     p_operation_reference => 'CYCLE_DEMO');
  DBMS_OUTPUT.PUT_LINE('Global control: retcode=' || l_retcode);
END;
/

Read only the evaluated lines (filtering out the raw-collection rows):

SELECT critical_program_id, concurrent_program_name,
       phase_code, status_code, severity_code, correction_required_flag
FROM   xmon_control_results
WHERE  control_run_id = (SELECT MAX(control_run_id) FROM xmon_control_runs
                         WHERE operation_reference = 'CYCLE_DEMO')
AND    critical_program_id IS NOT NULL
ORDER  BY critical_program_id;

Registering the programs and making them visible

Registration uses the fnd_program API. The parameters must match what the packages expect, in order.

BEGIN
  fnd_program.register(
    program                => 'Global Control of Concurrent Programs',
    application            => 'XXAPP',
    enabled                => 'Y',
    short_name             => 'XMON_GLOBAL_CONTROL',
    executable_short_name  => 'XMON_GLOBAL_CONTROL_EXE',
    executable_application => 'XXAPP');
  COMMIT;
END;
/

To make a program launchable from a responsibility's Submit Request screen, add it to the request group that the responsibility already uses do not replace the group, or you lose its existing programs:

BEGIN
  fnd_program.add_to_group(
    program_short_name  => 'XMON_GLOBAL_CONTROL',
    program_application => 'XXAPP',
    request_group       => 'System Administrator Reports',
    group_application   => 'FND');
  COMMIT;
END;
/

Verify the attachment:

SELECT cp.concurrent_program_name
FROM   fnd_responsibility r
JOIN   fnd_request_group_units rgu ON rgu.request_group_id = r.request_group_id
JOIN   fnd_concurrent_programs cp  ON cp.concurrent_program_id = rgu.request_unit_id
WHERE  r.responsibility_key = 'SYSTEM_ADMINISTRATOR'
AND    rgu.request_unit_type = 'P'
AND    cp.concurrent_program_name LIKE 'XMON%';

Tip. A responsibility caches its request group at login. After adding programs, log out and back in, or the new programs will not appear in the LOV.

Lessons learned (the practical traps)

These are the issues that actually cost time during implementation. They generalize to most EBS customizations.

Verify the application short name empirically on each instance. Assumptions are often wrong; a wrong value silently breaks FND lookups. Query fnd_application rather than trusting a convention.
SQLERRM cannot go directly into a SQL VALUES clause. Capture it into a variable first, then insert.
Some FND columns are version-dependent. RIGHT_JUSTIFY_ZERO_FILL_FLAG and the active-date columns are absent on some instances — detect columns dynamically instead of hard-coding them.
Reserved words cannot be PL/SQL identifiers. For example, COLUMNS will fail in a FND_PROGRAM.REGISTER call.
Date parameter defaults can truncate. A default returning YYYY/MM/DD HH24:MI:SS (19 chars) against a short display size raises ORA-01406 when the parameter window opens. Keep defaults in the format the value set expects, or leave them empty and let the package apply its fallback window.
On R12.2 (online patching), create value sets through the Application Developer screens rather than inserting directly into FND_FLEX_VALUE_SETS, to avoid edition-column errors (ORA-01400).
Never re-run creation scripts once successful. Use a dedicated repair script for targeted recompilation only.
Static analysis pays off. A pre-install review caught blocking bugs — missing parameters, missing value sets, an off-by-one in argument parsing, an uninitialized collection, and a missing request group — before any object was created.

Why this approach holds up

A native, API-based control has three properties that an interface robot does not:

Resilience. It depends on tables and submission APIs, not on screens — so patches, pop-ups, and dynamic fields do not break it.
Speed and clarity. It reads state directly in the database; the logic stays readable and maintainable by any Oracle EBS team.
No added surface. No extra license, no new expertise to hire, nothing more to re-test at every upgrade.

It does not replace enterprise automation platforms — it complements them where low-level, native, post-operation control is the right tool: as close as possible to the Oracle concurrent-processing engine.

Written by **WASSIM BEN SAIDA. If this was useful, follow for more hands-on Oracle EBS and PL/SQL write-ups. Feedback and questions welcome in the comments.