25.2.6.8.3 Scheduling Create New Account Job

Schedule the account creation job from SQL Developer Web so it runs as the workspace schema.

Scheduling a background job is simple, but if you run the one-line PL/SQL block below inside in the SQL Commands or SQL Scripts pages of SQL Workshop, DBMS Scheduler infers a JOB_CREATOR of APEX_PUBLIC_USER. That generic schema does not have privileges to call APEX_UTIL.CREATE_USER.

Instead, use SQL Workshop > SQL Developer Web if your APEX instance administrator has enabled this integration. This menu option opens SQL Developer Web in a new browser tab, automatically logged in as the workspace schema user WOODS (the Woods Clinic schema). Then, from the SQL Worksheet in SQL Developer Web, the DBMS Scheduler correctly infers the WOODS schema as the Job Creator. Notice that the JOB_ACTION parameter passes the PL/SQL block to handle the new users queue, and the REPEAT_INTERVAL indicates to run every minute.

--
-- NOTA BENE: Run this outside of App Builder so that the
-- ~~~~~~~~~  JOB_CREATOR ends up correctly reflecting WOODS
--            instead of the APEX_PUBLIC_USER proxy user.
--
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
      job_name            => 'APEX_CREATE_USER_JOB',
      job_type            => 'PLSQL_BLOCK',
      job_action          => 'begin frc_apex_user.handle_new_users_queue; end;',
      number_of_arguments => 0,
      repeat_interval     => 'FREQ=MINUTELY',
      end_date            => NULL,
      enabled             => TRUE,
      auto_drop           => TRUE,
      comments            => ''
      );
END;