I want to rewrite the following process probably using dbms_scheduler:
----
In our current forms application a long running process is started using a select statement:
select package.long_running_process(p1,p2,p3) from dual;
The forms process hangs for the duration of the process and finally receives a value indicating the result status of the process.
The process can return a value very quickly when pre-run validations fail
-1 user not authorised
-2 ...
-3 a lot more reasons why it won't start
Otherwise the process can take a very long time to run, depending on the amount of data to process.
It will then return with another number, either good >0 (transaction ID) or bad < 0 (something wrong with the data itself).
---
I found an example how to start a job:
BEGIN
-- Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => 'test_full_job_definition',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
Does the job_name have to be unique? How to provide a unique name from Apex when any user can submit a job?
How to get a return value?
Is this the way to go?
Create an ID, use this ID in the job name, store result in a table under this ID?