PL/SQL Documentation script

This documentation contains info on how to manipulate scheduler scripts with PL/SQL.
The procedures and functions needed for creating and changing scheduler scripts are located in the SO_SCRIPT package.
These procedures are the same used by the scheduler client.

Back to top 

Create script

Before creating a script with the procedure SO_SCRIPT.CREATE_OR_REPLACE the procedure SO_SCRIPT.INIT_CREATE must be called.

If the script already exists the old script is replaced.
procedure init_create;

procedure create_or_replace(
    p_script_name               in varchar2,
    p_script_description        in varchar2 default null,
    p_script_public             in boolean default false,
    p_script_public_read        in boolean default false,
    p_script_disabled           in boolean default false,
    p_script_blocked            in boolean default false,
    p_plsql_script_procedure    in varchar2 default null,
    p_plsql_check_procedure     in varchar2 default null,
    p_plsql_check_runtime       in boolean default true,
    p_plsql_create_procedure    in boolean default false,
    p_queue_name                in varchar2 default null,
    p_queue_updatable           in boolean default true,
    p_priority                  in integer default 50,
    p_priority_updatable        in boolean default true,
    p_keep_days                 in integer default 365,
    p_keep_days_updatable       in boolean default true,
    p_run_exclusive             in boolean default false,
    p_run_exclusive_updatable   in boolean default true,
    p_hold_exclusive            in boolean default false,
    p_hold_exclusive_updatable  in boolean default true,
    p_load                      in integer default 1,
    p_load_updatable            in boolean default true,
    p_childtask_never           in boolean default false,
    p_childtask_always          in boolean default false,
    p_childtask_queue           in boolean default false);
  
Parameter Description
p_script_name The scheduler script name.
The name is not case sensitive.
p_script_description The script description.
p_script_public If set all scheduler users are able to create, edit or delete scheduler tasks for this scheduler script.
p_script_public_read If set all scheduler tasks created with this scheduler script can be viewed by all scheduler users.
p_script_disabled If set the scheduler script is disabled.
p_script_blocked If set the scheduler script is blocked.
p_plsql_script_procedure The name of the procedure to be called when the scheduler script is executed.
The name of the procedure is not case sensitive.
p_plsql_check_procedure Contains the procedure to be executed for validation before a task is scheduled (or changed).
p_plsql_check_runtime If set the check procedure is again executed just before the scheduler task is activated.
p_plsql_create_procedure If set a PL/SQL procedure is created in the database with the same name and parameters as the scheduler script.
p_queue_name The scheduler queue name.
The name is not case sensitive.
p_queue_updatable If set another scheduler queue can be assigned to the scheduler task.
p_priority The priority of a scheduler script. This priority is used as default when a new scheduler task is created.
The minimum is zero and the maximum is 99.
p_priority_updatable If set a priority different than the priority of the scheduler script can be assigned to a scheduler task.
p_keep_days Specifies the number of days after which a ready scheduler task is deleted.
p_keep_days_updatable If set a scheduler task can overrule the number of days after which it is deleted.
p_run_exclusive If set the scheduler script runs exclusive in the scheduler queue.
p_run_exclusive_updatable If set a scheduler task can overrule the script's run exclusive setting.
p_hold_exclusive If set and a scheduler task must run exclusive but cannot be activated (because scheduler tasks are still running) other new scheduler tasks ready to be activated must wait.
p_hold_exclusive_updatable If set a scheduler task can overrule the scheduler script hold exclusive setting.
p_load The load of the scheduler script.
The minimum load is zero and the maximum load is 99.
p_load_updatable If set a load different than the load of the scheduler script can be assigned to a scheduler task.
p_childtask_never If set and the scheduler script is submitted from within another running scheduler task the running task will not be the parent for the new task.
p_childtask_always If set the scheduler script can only be started from within another running scheduler task.
p_childtask_queue If set a scheduler task with a parent task must be added to the number of running tasks in the scheduler queue.
Error Description
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE
OSC-10039
SCRIPT NAME "%1" TOO LONG
OSC-10040
QUEUE NAME "%1" TOO LONG
OSC-10041
SCRIPT PROCEDURE "%1" TOO LONG
OSC-10042
SCRIPT CHECK PROCEDURE "%1" TOO LONG
OSC-10043
SCRIPT CREATE OR REPLACE NOT CORRECT INITIALIZED
OSC-10044
SCRIPT NAME IS NULL
OSC-10045
CANNOT CREATE PROCEDURE WHEN SCRIPT NAME EQUALS PROCEDURE NAME
OSC-10060
COULD NOT CREATE DATABASE PROCEDURE SCRIPT "%1"."%2" "%3"
OSC-10083
SCRIPT NAME "%1" CONTAINS ILLEGAL CHARACTERS "%2"

In the following example a script is created that will execute the database procedure BATCHRUN. The script itself is called BATCHSCRIPT (the example can be downloaded here).
First the PL/SQL procedure BATCHRUN is created.

create or replace procedure batchrun
is
begin
  dbms_output.put_line('This is procedure batchrun');
end;
/
  

Next we create the scheduler script BATCHSCRIPT.

begin
  so_script.init_create;
  so_script.create_or_replace(
      p_script_name => 'batchscript',
      p_script_description => 'A batchrun test',
      p_plsql_script_procedure => 'batchrun',
      p_plsql_create_procedure => true);
end;
/
  

Because the value for parameter create procedure is true a PL/SQL procedure is created with the name BATCHSCRIPT.
We now want to schedule the script after 1 hour from now in the scheduler queue BATCH. This queue must already exist otherwise an error is generated.
Because the script has not been created with a default queue each time a new scheduler task is created a scheduler queue must be specified.

begin
   so_task.schedule_create(
       p_queue_name => 'batch',
       p_schedule_date => sysdate + 1/24);
   batchscript;
   so_task.committasks;
end;
/
  
Back to top 

Script parameters

There are four datatypes available for a script parameter, other datatypes are not possible.
Each datatype has his own procedure.

Datatype Type Procedure
varchar2 V parameter_varchar2
number N parameter_number
date D parameter_date
boolean B parameter_boolean
procedure parameter_varchar2(
    p_parameter_name            in varchar2,
    p_parameter_description     in varchar2 default null,
    p_parameter_length          in integer  default null,
    p_parameter_mandatory       in boolean  default false,
    p_parameter_check_procedure in varchar2 default null,
    p_parameter_check_runtime   in boolean  default true,
    p_parameter_hidden          in boolean  default false,
    p_parameter_visible         in boolean  default true,
    p_parameter_nullable        in boolean  default false,
    p_parameter_default         in varchar2 default null,
    p_parameter_case            in varchar2 default null,
    p_parameter_trim_left       in boolean  default false,
    p_parameter_trim_right      in boolean  default false,
    p_values_db_view            in varchar2 default null,
    p_values_db_column          in varchar2 default null,
    p_values_db_display         in varchar2 default null,
    p_value_list_editable       in boolean  default false);

procedure parameter_number(
    p_parameter_name            in varchar2,
    p_parameter_description     in varchar2 default null,
    p_parameter_digits          in integer  default null,
    p_parameter_decimals        in integer  default null,
    p_parameter_mandatory       in boolean  default false,
    p_parameter_check_procedure in varchar2 default null,
    p_parameter_check_runtime   in boolean  default true,
    p_parameter_hidden          in boolean  default false,
    p_parameter_visible         in boolean  default true,
    p_parameter_nullable        in boolean  default false,
    p_parameter_default         in varchar2 default null,
    p_values_db_view            in varchar2 default null,
    p_values_db_column          in varchar2 default null,
    p_values_db_display         in varchar2 default null,
    p_value_list_editable       in boolean  default false);

procedure parameter_date(
    p_parameter_name            in varchar2,
    p_parameter_description     in varchar2 default null,
    p_parameter_mandatory       in boolean  default false,
    p_parameter_check_procedure in varchar2 default null,
    p_parameter_check_runtime   in boolean  default true,
    p_parameter_visible         in boolean  default true,
    p_parameter_nullable        in boolean  default false,
    p_parameter_default         in varchar2 default null,
    p_parameter_time_part       in boolean  default true,
    p_values_db_view            in varchar2 default null,
    p_values_db_column          in varchar2 default null,
    p_values_db_display         in varchar2 default null,
    p_value_list_editable       in boolean  default false);

procedure parameter_boolean(
    p_parameter_name            in varchar2,
    p_parameter_description     in varchar2 default null,
    p_parameter_mandatory       in boolean  default false,
    p_parameter_check_procedure in varchar2 default null,
    p_parameter_check_runtime   in boolean  default true,
    p_parameter_visible         in boolean  default true,
    p_parameter_nullable        in boolean  default false,
    p_parameter_default         in varchar2 default null,
    p_display_value_true        in varchar2 default 'true',
    p_display_value_false       in varchar2 default 'false',
    p_display_value_null        in varchar2 default 'null');
  

In the following table the parameters of the above four procedures are summarized, the type column V N D B indicaties for which procedure each parameter is used.

Parameter V N D B Description
p_parameter_name V N D B The name of the script parameter.
p_parameter_description V N D B A short description of the script parameter.
p_parameter_length V - - - Specifies the maximum length of the parameter value.
p_parameter_digits - N - - Specifies the maximum number of digits including the decimals.
p_parameter_decimals - N - - The maximum number of decimals.
p_parameter_mandatory V N D B If set a value must be specified for the script parameter when a scheduler task is created.
p_parameter_default V N D B A PL/SQL expression that returns a default value for the script parameter when a scheduler task is created and no value is given for this script parameter.
p_parameter_check_procedure V N D B Contains a validation procedure to be executed before a scheduler task is created.
p_parameter_check_runtime V N D B If set the parameter check procedure is again executed just before the scheduler task is activated.
p_parameter_hidden V N - - If set the parameter value is not visible; this can be usefull if the parameter contains sensitive information like passwords.
p_parameter_visible V N D B If set a value can be assigned to this parameter.
p_parameter_nullable V N D B If set the value NULL can be assigned as a parameter value.
p_parameter_trim_left V - - - If set all the spaces at the beginning of the parameter value are removed.
p_parameter_trim_right V - - - If set all the spaces at the end of the parameter value are removed.
p_parameter_case V - - - Indicates if the parameter value is converted to uppercase (U) or lowercase (L).
Default no casing is applied.
p_parameter_time_part - - D - If set the date parameter value can contain a time part.
p_display_value_true - - - B A display value for the boolean value TRUE.
p_display_value_false - - - B A display for the boolean value FALSE.
p_display_value_null - - - B A display value for the boolean value NULL.
p_values_db_view V N D - The name of the database view which contains a list of allowable values for the script parameter.
p_values_db_column V N D - An expression based on the database view containing the allowed value for the parameter.
p_values_db_display V N D - An expression used on the scheduler client for displaying the allowed value. If no expression is given the allowed value column is displayed.
p_value_list_editable V N D - If set the values for the scheduler script parameters are not restricted by the list of values returned by the database view and the list of values.
Error Description
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE
OSC-10046
SCRIPT PARAMETER NAME IS NULL
OSC-10047
SCRIPT PARAMETER NAME "%1" TOO LONG
OSC-10048
SCRIPT PARAMETER "%1" VARCHAR2 LENGTH "%2" INVALID
OSC-10050
SCRIPT PARAMETER "%1" NUMBER DECIMALS "%2" INVALID
OSC-10051
SCRIPT PARAMETER "%1" NUMBER DECIMALS "%2" GREATER THAN DIGITS "%3"
OSC-10052
SCRIPT PARAMETER "%1" MUST HAVE A DEFAULT EXPRESSION
OSC-10056
SCRIPT PARAMETER "%1" MUST BE VISIBLE
OSC-10057
SCRIPT PARAMETER "%1" DEFAULT "%2" TOO LONG
OSC-10084
SCRIPT PARAMETER NAME "%1" CONTAINS ILLEGAL CHARACTERS "%2"

In the next example (download the code here) we have a package BATCHPACKAGE with the procedure RUN. This procedure has one numeric parameter LINES.

create or replace package batchpackage
is
  procedure run(lines in integer);
end;
/

create or replace package body batchpackage
is
  procedure run(lines in integer)
  is
    i integer := 0;
  begin
    while (lines > i)
    loop
      dbms_output.put_line('==============');
      i := i + 1;
    end loop;
  end;
end;
/
  

We now create a scheduler script LINESSCRIPT with one parameter LINES.

Note that the script parameter has the same name and datatype as the PL/SQL procedure parameter.

Also a default scheduler queue is given so each time we create a scheduler task and we don'specify a queue the task will be scheduled in this queue.

begin
  so_script.init_create;
  so_script.parameter_number(
      p_parameter_name => 'lines',
      p_parameter_description => 'The number of lines',
      p_parameter_digits => 2,
      p_parameter_decimals => 0,
      p_parameter_default => '10');
  so_script.create_or_replace(
      p_script_name => 'linesscript',
      p_script_description => 'A lines test',
      p_plsql_script_procedure => 'batchpackage.run',
      p_plsql_create_procedure => true,
      p_queue_name => 'batch');
end;
/
  

We will schedule the script LINESSCRIPT twice, the first time without a parameter value so the default ten (lines) is used. Because we do not specify a schedule date both tasks are scheduled now and are activated immediately by the scheduler server process.

SQL> exec linesscript;

PL/SQL procedure successfully completed.

SQL> exec linesscript(90);

PL/SQL procedure successfully completed.

SQL> exec so_task.committasks;

PL/SQL procedure successfully completed.
  
Back to top 

Parameter list of values

For each parameter, except those with datatype boolean, a list of values can be given.
At this time the list of values is only used on the script submit window (parameters panel) for generating a drop down list.

procedure parameter_values_varchar2(
    p_value_varchar2   in varchar2);

procedure parameter_values_varchar2(
    p_value_varchar2   in varchar2,
    p_value_display    in varchar2);

procedure parameter_values_number(
    p_value_number     in number);

procedure parameter_values_number(
    p_value_number     in number,
    p_value_display    in number);

procedure parameter_values_number(
    p_value_number     in number,
    p_value_display    in varchar2);

procedure parameter_values_date(
    p_value_date       in date);

procedure parameter_values_date(
    p_value_date       in date,
    p_value_display    in varchar2);

procedure parameter_values_date(
    p_value_date       in date,
    p_value_display    in date);
  

Back to top 

Deleting scripts

A scheduler script can only be deleted by the scheduler script owner.
Also all not activated scheduler tasks that use the script must be deleted before the script itself is deleted.

procedure delete_script(
  p_script_name           in varchar2,
  p_delete_finished_tasks in boolean default false);
  
Parameter Description
p_script_name The scheduler script name.
The name is not case sensitive.
p_delete_finished_tasks If true then all the finished tasks scheduled with this script are also deleted.
Only main tasks will be deleted, not tasks that have a parent task.
Error Description
OSC-10008
SCRIPT "%1"."%2" DOES NOT EXIST
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10026
SCRIPT "%1"."%2" IS STILL IN USE
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE

In the following example the script is not deleted because one or more tasks exist.

SQL> execute so_script.delete_script('so_test_script5');
BEGIN so_script.delete_script('so_test_script5'); END;

*
ERROR at line 1:
ORA-20000: OSC-10026 SCRIPT "PLSQL5"."SO_TEST_SCRIPT5" IS STILL IN USE
ORA-06512: at "PLSQLSCH.SO_SCRIPT", line 689
ORA-06512: at line 1
Back to top 

Granting scripts

Scripts can be granted to other users by the scheduler script owner or by scheduler users with the administrator privilege.
If no value is assigned to the script owner parameter the current user that executes the command is assumed to be the owner.

procedure grant_script(
    p_user_name in varchar2,
    p_script_name in varchar2,
    p_script_owner in varchar2 default null);
  
Parameter Description
p_user_name The name of the scheduler user.
The name is not case sensitive.
p_script_name The scheduler script name.
The name is not case sensitive.
p_script_owner The scheduler user who created the scheduler script.
The value is not case sensitive.
Error Description
OSC-10008
SCRIPT "%1"."%2" DOES NOT EXIST
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10027
USER "%1" IS NOT ALLOWED TO GRANT SCRIPTS OWNED BY "%2"
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE
Back to top 

Revoking scripts

Scripts can be revoked from other users by the scheduler script owner or by scheduler users with the administrator privilege.
If no value is assigned to the script owner parameter the current user that executes the command is assumed to be the owner.

procedure revoke_script(
    p_user_name    in varchar2,
    p_script_name  in varchar2,
    p_script_owner in varchar2 default null);
  
Parameter Description
p_user_name The name of the scheduler user.
The name is not case sensitive.
p_script_name The scheduler script name.
The name is not case sensitive.
p_script_owner The scheduler user who created the scheduler script.
The value is not case sensitive.
Error Description
OSC-10008
SCRIPT "%1"."%2" DOES NOT EXIST
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10027
USER "%1" IS NOT ALLOWED TO GRANT SCRIPTS OWNED BY "%2"
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE
Back to top 

Block scripts

Scripts who are blocked can no longer be scheduled. Also existing scheduled tasks that use a blocked script cannot be changed.
Already scheduled tasks are still activated.
If no value is assigned to the script owner parameter the current user that executes the command is assumed to be the owner.

procedure block_script(
    p_script_name    in varchar2,
    p_script_owner   in varchar2 default null,
    p_script_blocked in boolean default true);
  
Parameter Description
p_script_name The scheduler script name.
The name is not case sensitive.
p_script_owner The scheduler user who created the scheduler script.
The value is not case sensitive.
p_script_blocked If set the scheduler script is blocked.
Error Description
OSC-10008
SCRIPT "%1"."%2" DOES NOT EXIST
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10027
USER "%1" IS NOT ALLOWED TO GRANT SCRIPTS OWNED BY "%2"
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE

In this example the script LINESSCRIPT is blocked by the scheduler script owner (because no script owner is given the user that executes the command must be the owner).

begin
  so_script.block_script(
      p_script_name => 'linesscript');
end;
/
  
Back to top 

Disable scripts

If a script is disabled scheduler tasks are no longer activated for this script.
It is however still possible to create, edit and delete scheduler tasks for a disabled script.
If no value is assigned to the script owner parameter the current user that executes the command is assumed to be the owner.

procedure disable_script(
    p_script_name     in varchar2,
    p_script_owner    in varchar2 default null,
    p_script_disabled in boolean default true);
  
Parameter Description
p_script_name The scheduler script name.
The name is not case sensitive.
p_script_owner The scheduler user who created the scheduler script.
The value is not case sensitive.
p_script_disabled If set the scheduler script is disabled.
Error Description
OSC-10008
SCRIPT "%1"."%2" DOES NOT EXIST
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10027
USER "%1" IS NOT ALLOWED TO GRANT SCRIPTS OWNED BY "%2"
OSC-10028
USER "%1" HAS NO DEVELOPER PRIVILEGE
Back to top