Oracle APEX Dynamic Actions locks page

Question

I have a dynamic action in Oracle APEX PAGE, that executes PL/SQL CODE (a stored procedure that creates a BLOB)

So when the users clicks on it, the javascript behind (dynamic actions, 'runs' the PL and the page is 'locked' until everything finishes)

How can I make something that doesn't lock the browser entirely until this process is finished?

Thank you

Unknown Unknown · Accepted Answer · 2017-02-13T09:17:32

One straight-forward way, if you haven't moved to version 5 yet is to use the APEX_PLSQL_JOB package.

Define the functionality you need inside the database, in a function or procedure in a package.

Then, start the function in the background using the API.

For example, if you have defined a procedure upload_pending inside a package PROJECTX_IO, then you can start it with:

declare
  p_job number
begin
p_job := apex_plsql_job.submit_process(
    p_sql => 'BEGIN PROJECTX_IO.upload_pending; END;',
    p_status => 'Uploading');
end;

You are currently not doing anything with the job id returned in p_job. What works, if you know you only have a single background job running at any time, is to add it to a one-row table in the database. This simply goes to the bottom of the code snippet above (before the end;):

delete from t_job;
insert into t_job values ( p_job );

Then, inside the background process, you have access to the job id of the process. You could change the job status at the end with:

select job into p_job from t_job;
apex_plsql_job.update_job_status(p_job,
    'Upload complete');

How to deal with multiple background jobs?

Oracle APEX Dynamic Actions locks page

1 Answers