asynchronous datasource refresh

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