How I Built a Smart Task Tracker in Oracle APEX Without Opening Each Row

Updated 14 October 2026: This article keeps the workflow-table example from my original project and corrects its report rendering, AJAX setup and data-integrity guidance. It is an integration pattern, not a complete application export. The revised examples were checked against Oracle documentation but were not executed against the original application for this update.

In the project-management app behind this article, completing a workflow task meant opening a dialog, changing its status and closing the dialog again. Doing that repeatedly for a list of tasks was unnecessary work.

The useful part of the design was simple: put a completion action in the report, save the change on the server, then refresh the report region. The whole page does not need to reload. Here is how the pieces fit together, including the checks the original short example left out.

What this example includes, and what your application must supply

The names below come from the original project excerpt:

• WORKFLOW_CATEGORIES, WORKFLOWS, WORKFLOW_STEPS and WORKFLOW_TASKS define the task list.
• TASK_TRACKER stores task status against a business record; TRACKER_SEQ supplies its primary key.
• APP_USERS maps the authenticated application identity to a local user.
• P101_RECORD_ID identifies the selected project; P101_TASK_ID carries the clicked task ID.

Your schema needs those objects or deliberate equivalents. This article does not supply the original table DDL, project-membership model or authorization package. Do not create guessed versions of those relationships just to make the SQL compile. Work in a development copy with sample data, and replace the page-item prefix if your page is not 101.

The example assumes one current, non-deleted tracker row per project/task. A task can have STATUS = 'YES' for done, 'NO' for not done, or no tracker row yet. If your application has per-person completion, workflow versions or repeated task cycles, its business key needs those dimensions too.

1. Return data from SQL and render the button with an HTML Expression

Create a Classic Report. Set its HTML DOM ID to task_report_region. This field was called Static ID in older APEX releases; Oracle renamed it in APEX 26.1. Set the report’s Page Items to Submit to P101_RECORD_ID.

The following is the rendering query for an already authorized project context. Apply your application’s server-side project and task visibility rules before exposing it to users. The category filter alone does not prove that a task belongs to the selected project’s workflow.

with visible_tasks as (
    select wt.task_id,
           wt.task_name,
           wt.position_number
      from workflow_categories wc
      join workflows w
        on w.category_id = wc.category_id
      join workflow_steps ws
        on ws.workflow_id = w.workflow_id
      join workflow_tasks wt
        on wt.step_id = ws.step_id
     where wt.is_deleted = 'NO'
       and wc.category_name = 'PROJECT_TASKS'
       -- Add the application's authorized project/workflow predicate.
), completed_tasks as (
    select tt.task_id,
           tt.status
      from task_tracker tt
     where tt.is_deleted = 'NO'
       and tt.table_name = 'PROJECTS'
       and tt.record_id = :P101_RECORD_ID
)
select wt.task_id,
       wt.position_number as pos,
       wt.task_name,
       case ct.status
           when 'YES' then 'Done'
           when 'NO'  then 'Not done'
           else 'Not started'
       end as status_label,
       case ct.status
           when 'YES' then 't-Button--success'
           when 'NO'  then 't-Button--danger'
           else 't-Button--primary'
       end as button_class,
       cast(null as varchar2(1)) as mark_done_btn
  from visible_tasks wt
  left join completed_tasks ct
    on ct.task_id = wt.task_id
 where :P101_RECORD_ID is not null
 order by wt.position_number, wt.task_id;

The exact TABLE_NAME and RECORD_ID predicates are important. Treating NULL values as a match for every project can mix global or unrelated tracker rows into the result. The one-active-row rule must also be enforced; duplicate tracker rows would duplicate report rows.

Configure the columns as follows:

• TASK_ID and BUTTON_CLASS: Hidden.
• POS, TASK_NAME and STATUS_LABEL: Plain Text; keep escaping enabled.
• MARK_DONE_BTN: Plain Text, with the following HTML Expression. Keep escaping enabled and use the explicit context-sensitive substitutions shown below.

<button type="button"
        class="t-Button t-Button--small #BUTTON_CLASS!ATTR# mark-done-btn"
        data-task-id="#TASK_ID!ATTR#"
        title="Mark done: #TASK_NAME!ATTR#"
        aria-label="Mark done: #TASK_NAME!ATTR#. Current status: #STATUS_LABEL!ATTR#">
  <span class="fa fa-check" aria-hidden="true"></span>
  <span>Mark done</span>
</button>

BUTTON_CLASS now actually appears in the button’s class attribute. In the original query, the status-based color column was calculated but the button still had a hardcoded success class.

The button has visible text and an accessible name; the separate status column means users do not have to infer status from color. All dynamic attribute values use !ATTR. Do not insert unescaped task names into raw markup or inline JavaScript. Oracle documents HTML Expression substitution syntax and report escaping.

Correction: Percent Graph is for percentage data, not a general-purpose HTML renderer. Use the column’s HTML Expression for this button. See Oracle’s Percent Graph example.

2. Configure one click action that survives a report refresh

Create P101_TASK_ID as a Hidden item if it does not already exist. For this new development input, set Settings → Value Protected → No, because the click action deliberately changes it in the browser. Keep its session-state data type as VARCHAR2; the server converts and validates the identifier. Keep P101_RECORD_ID connected to your actual project-selection flow. Setting an item in JavaScript changes its browser value; the server needs that value submitted with the AJAX request.

This design treats both submitted IDs as untrusted inputs. The item security settings must support their intended AJAX submission. A client-set hidden item cannot simultaneously require its original value to remain unchanged. Do not disable application-wide protection or weaken an existing protected project item just to copy this example; adapt the request to your application’s approved input and server-side authorization design. See Oracle’s Hidden item documentation and the field-level help in your APEX version.

Create a Dynamic Action named Complete workflow task:

• Event: Click
• Selection Type: jQuery Selector
• jQuery Selector: #task_report_region .mark-done-btn
• Event Scope: Dynamic
• Static Container: #task_report_region, provided that outer region element survives the refresh
• Client-side Condition: None
• Fire on Initialization: No for each action

Dynamic scope keeps the handler working when the report rows are replaced by a partial refresh. If your custom region template replaces the outer wrapper too, choose a higher stable container. Oracle explains event scope and action conditions.

True action 10: Execute JavaScript Code

const taskId = this.triggeringElement.getAttribute("data-task-id");
apex.item("P101_TASK_ID").setValue(taskId);

Read the attribute as a string; there is no need to coerce a database identifier into a JavaScript number. The server must still validate it.

True action 20: Execute Server-side Code

• Language: PL/SQL
• Items to Submit: P101_TASK_ID,P101_RECORD_ID, without colons
• Items to Return: leave empty for this design
• Wait for Result: Yes
• Stop Execution on Error: Yes

Use the guarded integration template in Step 4 only after reviewing the database rule in Step 3. The report also needs P101_RECORD_ID in its own Page Items to Submit, independently of the server action. Oracle’s submit/return guidance explains these settings.

True action 30: Refresh

• Affected Elements, Selection Type: Region
• Region: your task report
• Fire When Event Result Is: True

Wait for Result orders the save and refresh. Stop Execution on Error prevents the remaining actions from proceeding when the server action reports an error. Do not catch and discard a PL/SQL error, because that can turn a failed write into an apparently successful AJAX response. See Oracle’s dynamic-action execution documentation.

Correction: Fire When Event Result Is selects the True or False branch of the dynamic action’s client-side condition. It does not mean that the database write succeeded. With no condition, use True actions.

This sequence refreshes the report region, including its rows and buttons. It does not update only the clicked button. Waiting for one request also does not serialize separate clicks or browser sessions.

3. Define the database rule before claiming concurrency safety

An update followed by an insert can still race: two sessions may both find no existing row. A matching database uniqueness rule is required. Silently ignoring DUP_VAL_ON_INDEX does not establish that the intended task was completed.

For the specific business rule here, at most one row with IS_DELETED = 'NO' and TABLE_NAME = 'PROJECTS' may exist for a given RECORD_ID,TASK_ID. First inspect existing data in a development copy:

select record_id, task_id, count(*) as active_rows
  from task_tracker
 where is_deleted = 'NO'
   and table_name = 'PROJECTS'
 group by record_id, task_id
having count(*) > 1;

select tracker_id, record_id, task_id
  from task_tracker
 where is_deleted = 'NO'
   and table_name = 'PROJECTS'
   and (record_id is null or task_id is null);

Both queries should return no rows before adding the proposed rule. Resolve existing conflicts with the data owner; do not delete them automatically. Verify foreign keys, status/deletion constraints, the TRACKER_ID primary key and sequence behavior against your actual DDL.

The following is a proposed migration only for the stated business rule. It changes schema behavior and can fail on existing data. Review it with the schema owner before applying it, and do not add a duplicate index if an equivalent rule already exists.

alter table task_tracker add constraint tt_project_key_ck
check (
    case
        when is_deleted = 'NO' and table_name = 'PROJECTS'
        then case
                 when record_id is not null and task_id is not null
                 then 1 else 0
             end
        else 1
    end = 1
);

create unique index tt_active_project_task_uq
    on task_tracker (
        case when is_deleted = 'NO' and table_name = 'PROJECTS'
             then record_id end,
        case when is_deleted = 'NO' and table_name = 'PROJECTS'
             then task_id end
    );

The conditional index leaves deleted and non-project rows outside this particular uniqueness rule. Additional rules for other record types remain your application’s responsibility. Oracle documents the mechanism under function-based conditional uniqueness.

4. Authorize the exact operation, then write

Before any insert or update, the server must establish all of the following from trusted database records:

1. The request belongs to an authenticated, active application user. Resolve that user from the server’s APP_USER, not a submitted user ID.
2. The selected project exists, is active and belongs to the correct tenant or organization.
3. That user may complete tasks for that particular project. Page access or a generic contributor role alone may be too broad.
4. The selected task is active and belongs to the workflow assigned to that project; enforce prerequisites or workflow state if the business process requires them.
5. The transition to completed is allowed now. Recheck or lock relevant records as needed if permissions or workflow state can change concurrently.

Apply corresponding visibility rules to the report as well. Hidden items, an invisible button, escaped attributes and a project ID in session state do not establish row-level permission. Use APEX authorization schemes for application and component access, plus the project-specific checks in the server operation. Oracle’s application-security guidance covers the distinction between identity and permission.

Deliberately blocked template: the block below contains an unconditional authorization guard because the original excerpt did not include the project-access schema. As written, it raises an error before any write. Replace that guard only with real server-side checks for all the requirements above. Removing it without implementing those checks is unsafe.

declare
    l_task_id    task_tracker.task_id%type;
    l_record_id  task_tracker.record_id%type;
    l_user_id    app_users.user_id%type;
    l_task_count pls_integer;
    l_rows       pls_integer;
begin
    -- This excerpt assumes numeric project and task identifiers.
    begin
        l_task_id   := to_number(:P101_TASK_ID);
        l_record_id := to_number(:P101_RECORD_ID);
    exception
        when value_error or invalid_number then
            raise_application_error(-20001, 'Invalid task request.');
    end;

    if l_task_id is null or l_record_id is null then
        raise_application_error(-20001, 'Invalid task request.');
    end if;

    -- REQUIRED INTEGRATION POINT: replace this failing guard with
    -- real authenticated-user, tenant, project, task and transition
    -- authorization. The original article supplied no such schema.
    raise_application_error(
        -20002, 'Project/task authorization must be implemented.'
    );

    -- Use this mapping only if APP_USER really is an email address
    -- in your authentication scheme. Prefer a stable identity key.
    begin
        select user_id
          into l_user_id
          from app_users
         where lower(user_email) = lower(:APP_USER);
    exception
        when no_data_found or too_many_rows then
            raise_application_error(-20003, 'User mapping is invalid.');
    end;

    -- This validates the excerpt's category membership only.
    -- The authorization code above must also validate project scope.
    select count(*)
      into l_task_count
      from workflow_categories wc
      join workflows w on w.category_id = wc.category_id
      join workflow_steps ws on ws.workflow_id = w.workflow_id
      join workflow_tasks wt on wt.step_id = ws.step_id
     where wt.task_id = l_task_id
       and wt.is_deleted = 'NO'
       and wc.category_name = 'PROJECT_TASKS';

    if l_task_count <> 1 then
        raise_application_error(-20004, 'Task is not available.');
    end if;

    update task_tracker
       set status       = 'YES',
           user_id      = l_user_id,
           updated_date = sysdate,
           updated_by   = :APP_USER
     where task_id   = l_task_id
       and record_id = l_record_id
       and table_name = 'PROJECTS'
       and is_deleted = 'NO';

    l_rows := sql%rowcount;
    if l_rows > 1 then
        raise_application_error(-20005, 'Duplicate active task rows.');
    elsif l_rows = 0 then
        insert into task_tracker (
            tracker_id, task_id, status, user_id,
            table_name, record_id, is_deleted,
            created_date, created_by, updated_date, updated_by
        ) values (
            tracker_seq.nextval, l_task_id, 'YES', l_user_id,
            'PROJECTS', l_record_id, 'NO',
            sysdate, :APP_USER, sysdate, :APP_USER
        );
    end if;
    -- No blanket exception handler and no manual COMMIT here.
end;

The update now targets exactly the active project/task record. The insert explicitly sets IS_DELETED, rather than relying on an undocumented default. Errors propagate to the server action. This minimal version intentionally does not swallow uniqueness errors: if concurrent first-time inserts collide, one request can fail, and the UI must not claim success for that failed request.

For a production service that should treat a repeated completion as a success, implement and test a bounded retry or idempotent operation against the same authorized business key. Confirm the stored state after the conflict, and keep unrelated constraint errors visible. Do not assume a generic update/insert or MERGE eliminates every race.

This block records the most recent successful completion request in USER_ID, UPDATED_BY and UPDATED_DATE. Clicking again updates that metadata. The created fields retain row-creation details. If you need the first completion time or an immutable history of changes, model those explicitly; these columns alone are not a complete audit log.

The email comparison is conditional on your identity design. Lowercasing values is useful only when the login-to-user mapping is intentionally case-insensitive and uniquely enforced. It cannot fix duplicate users or an APP_USER value that is a username or identity-provider subject instead of an email address.

5. Verify behavior with sample data

These are tests to run in your own environment after implementing the missing authorization and reviewing the schema migration. They are expected outcomes, not a record of tests performed for this article.

1. Rendering: seed done, not-done and unstarted cases. Verify the status text and button class agree. Put quotes, an ampersand and literal angle brackets in a sample task name; they should remain text and should not create markup or script.
2. Persistence: complete an authorized task. Check TASK_TRACKER for the exact project/task key and active flag; reload the page to confirm the status persists.
3. Partial refresh: complete another task after the first report refresh. The click handler should still run once per click.
4. Missing items: use browser/network inspection and APEX Debug in development to confirm both IDs were submitted and the refreshed report used the intended project. Do not publish debug logs containing real user or project data.
5. Denied requests: with test accounts, try a project the user cannot access, a task from another workflow, a deleted task and a nonexistent ID. Expect an error and no write. Test the server request directly as well as the visible UI.
6. Error handling: leave the deliberate guard in place for an initial negative test. Expect an error, no tracker change and no success refresh. A database error must not be presented as a completed task.
7. Concurrency: submit the same authorized first-time completion from two sessions. Confirm there is at most one active tracker row. This minimal handler may return a conflict to one session; verify that behavior and provide a useful error/retry flow before release.
8. Accessibility: reach the button by keyboard, activate it and check the refreshed status. Restore sensible focus after a refresh if the theme or custom template loses it.

Common symptoms and where to look

• HTML appears as text: check that the button markup is in the HTML Expression property of a Plain Text report column. Do not switch to Percent Graph or turn off escaping for task data.
• Every button stays green: inspect whether #BUTTON_CLASS!ATTR# is actually present in the expression and whether the query returns the expected status.
• First click works, later clicks do not: check Dynamic event scope and that its static container survives partial refresh.
• Wrong project or NULL ID: check the server action’s Items to Submit, the report’s Page Items to Submit and the project-selection flow. Still reject invalid or unauthorized values on the server.
• Duplicate report rows: investigate duplicate active tracker keys and the workflow join. Do not hide them with DISTINCT before understanding the data problem.
• Refresh happens too early: check action sequence and Wait for Result. Fire When Event Result Is is not a database-success setting.

The useful pattern

A report action can remove a lot of repetitive dialog work. Keep the SQL responsible for data, the HTML Expression responsible for presentation, the dynamic action responsible for request order, and the server responsible for permission and integrity. Test those pieces together before putting the action in front of real users.