Hello folks,
is it possible to somehow refresh datasources in application ASYNCHRONOUSLY ?
Many of our datasources could be refreshed in background, so they will be ready, once user will use it (mostly datasources which have lots of data and takes a long to be populated). This should have a nice effect on app. performance.
My idea is (a simplified scenario):
1. create a package procedure, which will refresh datasources which needs to be refreshed.
2. create a job, which will be run manually only and asynchronously
3. this job will be run after a successful user login (for example)
Here is a piece of my code:
package procedure
procedure refreshDatasources(p_fs_application_id number, p_section varchar2 default 'ALL') is
cursor c is
select das.name_tx datasource_name, count(1)
from formspider.t_bdf_dbobjectaccess das
, formspider.t_bdf_paneldtl pdl
, formspider.t_bdf_panel pnl
where pdl.bdf_panel_oid = pnl.bdf_panel_oid
and das.bdf_dbobjectaccess_oid = pdl.bdf_dbobjectaccessusg_oid
and pnl.bdf_application_oid = p_fs_application_id --formspider.bdf_support.getcurrentapplication
and upper(das.name_tx) like 'PJ%' -- some conditions to restrict all datasources
group by das.name_tx
order by 2 desc;
begin
api_debug.log('FS_APP.refreshDatasources.START section='''||p_section||'''');
for x in c loop
API_DATASOURCE.executeQuery(x.datasource_name);
end loop;
api_debug.log('FS_APP.refreshDatasources.END section='''||p_section||'''');
end;
JOB DEFINITION
BEGIN
-- Stored Procedure with Arguments.
DBMS_SCHEDULER.create_program(
program_name => 'RefreshDatasources_PROGRAM',
program_type => 'STORED_PROCEDURE',
program_action => 'fs_app.refreshDatasources',
number_of_arguments => 2,
enabled => FALSE,
comments => 'Program pre refresh datasourcov v aplikacii');
DBMS_SCHEDULER.define_program_argument (
program_name => 'RefreshDatasources_PROGRAM',
argument_name => 'p_fs_application_id',
argument_position => 1,
argument_type => 'NUMBER',
default_value => '');
DBMS_SCHEDULER.define_program_argument (
program_name => 'RefreshDatasources_PROGRAM',
argument_name => 'p_section',
argument_position => 2,
argument_type => 'VARCHAR2',
default_value => 'ALL');
DBMS_SCHEDULER.enable (name => 'RefreshDatasources_PROGRAM');
END;
/
-- create job
BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'RefreshDatasources_JOB',
program_name => 'RefreshDatasources_PROGRAM',
start_date => dbms_scheduler.stime,
repeat_interval => NULL, --'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=6; BYMINUTE=20',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Datasource refresh - spúšta sa manuálne v pozadí aplikácie');
END;
/
-- enable the program
exec DBMS_SCHEDULER.enable('RefreshDatasources_PROGRAM');
-- enable the job
exec DBMS_SCHEDULER.enable('RefreshDatasources_JOB');
Run it from application, after logon.
....
if logon = ok then
...
-- set job program parameter
dbms_scheduler.set_job_argument_value(job_name => 'RefreshDatasources_JOB'
,argument_position => 1
,argument_value => formspider.bdf_support.getcurrentapplication);
-- run job async.
dbms_scheduler.run_job('RefreshDatasources_JOB', FALSE);
end if;
....
debuging info:
-- Get information to job
select * from user_scheduler_job_log
where job_name = 'REFRESHDATASOURCES_JOB'
order by log_date desc;
-- Show details on job run
select *
from user_scheduler_job_run_details
where job_name ='REFRESHDATASOURCES_JOB'
order by 2 desc;
error:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "FORMSPIDER.API_DATASOURCE", line 15
ORA-06512: at "FORMSPIDER.API_DATASOURCE", line 431
ORA-06512: at "DEV.FS_APP", line 2193
ORA-06512: at line 1
Any idea?
Kindest regards,
Tomas