ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
An undocumented internal package containing 140 separate functions and procedures.
I have only documented one of them here so as not to lose work performed when this procedure was part of DBMS_SCHEDULER.
SELECT procedure_name
FROM dba_procedures
WHERE object_name = 'DBMS_ISCHED'
AND procedure_name IS NOT NULL
ORDER BY 1;
SELECT UNIQUE 'dbms_?.' || LOWER(procedure_name) || '('
FROM dba_procedures
WHERE object_name = 'DBMS_?'
AND procedure_name IS NOT NULL
ORDER BY 1;
SELECT LOWER(argument_name) || ' ' || in_out || ' ' || DECODE(data_type,
'PL/SQL BOOLEAN', 'BOOLEAN', data_type)
|| ',' AS ARGS, type_owner, type_name, type_subname
FROM dba_arguments
WHERE package_name = 'DBMS_ISCHED'
AND object_name = 'RAISE_ORACLE_ERROR'
ORDER BY position;
-- This might actually work if DBMS_JOB created jobs had VARCHAR2 names.
-- Unfortunately they do not so I can not puzzle this one out unless I
-- assume the information in the rdbms/admin file is incorrect.
dbms_isched.create_agent_destination(
destination_name IN VARCHAR2,
hostname IN VARCHAR2,
port IN BINARY_INTEGER,
comments IN VARCHAR2);
--create pseudo local db destination
BEGIN
dbms_scheduler.create_database_destination(
destination_name => 'sched$_local_pseudo_db',
agent => 'sched$_local_pseudo_agent',
tns_name => 'pseudo_inst',
comments => 'Place holder for synonym LOCAL_DB dest');
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -27477 THEN
NULL;
ELSE
RAISE;
END IF;
END;
/
dbms_isched.create_job(
job_name IN VARCHAR2,
job_style IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN BINARY_INTEGER,
schedule_type IN VARCHAR2,
schedule_expr IN VARCHAR2,
queue_spec IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE,
end_date IN TIMESTAMP WITH TIME ZONE,
job_class IN VARCHAR2,
comments IN VARCHAR2,
enabled IN BOOLEAN,
auto_drop IN BOOLEAN,
invoker IN VARCHAR2,
sys_privs IN BINARY_INTEGER,
aq_job IN BOOLEAN,
current_schema IN VARCHAR2);
TBD
Overload 2
dbms_isched.create_job(
job_name IN VARCHAR2,
job_style IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN BINARY_INTEGER,
schedule_type IN VARCHAR2,
schedule_expr IN VARCHAR2,
queue_spec IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE,
end_date IN TIMESTAMP WITH TIME ZONE,
job_class IN VARCHAR2,
comments IN VARCHAR2,
enabled IN BOOLEAN,
auto_drop IN BOOLEAN,
invoker IN VARCHAR2,
sys_privs IN BINARY_INTEGER,
aq_job IN BOOLEAN,
destination_name IN VARCHAR2,
credential IN VARCHAR2,
current_schema IN VARCHAR2);
Turns a comma delimited list of email addresses into a VARCHAR2 table
dbms_isched.parse_email_addresses(
comma_separated_list IN VARCHAR2,
addresses_list OUT dbms_utility.lname_array);
DECLARE
retTab dbms_utility.lname_array;
BEGIN
dbms_isched.parse_email_addresses('larry@orcl.com,safra@orcl.com', retTab);
FOR i IN 1 .. 2 LOOP
dbms_output.put_line(retTab(i));
END LOOP;
END;
/ larry@orcl.com
safra@orcl.com
dbms_isched.raise_oracle_error(
error_number IN BINARY_INTEGER,
params_table IN dbms_utility.lname_array);
DECLARE
paramArr dbms_utility.lname_array;
BEGIN
paramArr(1) := 'operation not permitted on lightweight and in-memory jobs';
dbms_output.put_line(paramArr(1));
dbms_isched.raise_oracle_error(27494, ParamArr);
END;
/ operation not permitted on lightweight and
in-memory jobs DECLARE
*
ERROR at line 1:
ORA-27494: operation not permitted on lightweight and in-memory jobs
ORA-06512: at "SYS.DBMS_ISCHED", line 3742
ORA-06512: at line 6
Undocumented but I really hope it proves to do what the name and parameters imply
dbms_isched.remote_kill(
job_name IN VARCHAR2,
job_subname IN VARCHAR2,
job_owner IN VARCHAR2,
credential_name IN VARCHAR2,
credential_owner IN VARCHAR2,
destination IN VARCHAR2,
hard_kill IN BINARY_INTEGER);
dbms_isched.send_event_email(
email_server_host IN VARCHAR2,
email_server_port IN BINARY_INTEGER,
send IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
body IN VARCHAR2,
wallet_path IN VARCHAR2,
email_server_cred IN VARCHAR2,
email_server_end IN VARCHAR2);
dbms_isched.validate_email_addresses(
single_address IN VARCHAR2,
addresses_list IN dbms_utility.lname_array);
-- note the missing "@" in the following demo DECLARE
addrList dbms_utility.lname_array;
BEGIN
dbms_isched.validate_email_addresses('damorgan_oracle.com', addrList);
END;
/
*
ERROR at line 1:
ORA-24098: invalid value damorgan_yahoo.com for EMAIL_ADDRESS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_ISCHED", line 8931
ORA-06512: at line 4
-- with the "@" in place
DECLARE
addrList dbms_utility.lname_array;
BEGIN
dbms_isched.validate_email_addresses('damorgan@oracle.com', addrList);
END;
/
dbms_isched.remote_kill(
job_name IN VARCHAR2,
job_subname IN VARCHAR2,
job_owner IN VARCHAR2,
credential_name IN VARCHAR2,
credential_owner IN VARCHAR2,
destination IN VARCHAR2,
hard_kill IN BINARY_INTEGER);
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "dbFWTrace" AS
