Oracle DBMS_ISCHED
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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.
AUTHID DEFINER
Dependencies
ALL_EVALUATION_CONTEXT_VARS DBMS_SCHEDULER SCHEDULER$_EVENT_LOG
ALL_RULE_SETS DBMS_SCHEDULER_LIB SCHEDULER$_FILEWATCHER_RESEND
ALL_RULE_SET_RULES DBMS_SCHED_MAIN_EXPORT SCHEDULER$_GLOBAL_ATTRIBUTE
ALL_SCHEDULER_GLOBAL_ATTRIBUTE DBMS_SNAPSHOT SCHEDULER$_INSTANCE_S
ANYDATA DBMS_STANDARD SCHEDULER$_JOB
AQ$_AGENT DBMS_SYSTEM SCHEDULER$_JOB_EVENT_HANDLER
AQ$_REG_INFO DBMS_SYS_ERROR SCHEDULER$_JOB_OUTPUT
AQ$_REG_INFO_LIST DBMS_TRANSACTION SCHEDULER$_JOB_RUN_DETAILS
DBA_DIRECTORIES DBMS_UTILITY SCHEDULER$_JOB_STEP_TYPE
DBA_RULES DUAL SCHEDULER$_LWJOB_OBJ
DBA_RULE_SETS GLOBAL_NAME SCHEDULER$_NOTIFICATION
DBA_SCHEDULER_CHAIN_RULES GV$INSTANCE SCHEDULER$_STEP
DBA_SCHEDULER_CHAIN_STEPS JOB$ SCHEDULER$_STEP_STATE
DBA_SCHEDULER_DB_DESTS JOBATTR_ARRAY SCHEDULER$_STEP_TYPE
DBA_SCHEDULER_EXTERNAL_DESTS JOB_DEFINITION SCHEDULER$_STEP_TYPE_LIST
DBA_SCHEDULER_FILE_WATCHERS JOB_DEFINITION_ARRAY SCHEDULER$_VARIABLE_VALUE
DBA_SCHEDULER_JOBS OBJ$ SCHEDULER$_VAR_VALUE_LIST
DBA_SCHEDULER_RUNNING_CHAINS ODCIVARCHAR2LIST SCHEDULER$_WINDOW_DETAILS
DBMSOUTPUT_LINESARRAY PLITBLM SCHEDULER_FILEWATCHER_HISTORY
DBMS_AQ RE$ATTRIBUTE_VALUE_LIST SCHEDULER_FILEWATCHER_HST_LIST
DBMS_AQADM RE$COLUMN_VALUE_LIST SCHEDULER_FILEWATCHER_REQUEST
DBMS_AQADM_SYS RE$NV_ARRAY SCHEDULER_FILEWATCHER_REQ_LIST
DBMS_CREDENTIAL RE$NV_LIST SCHEDULER_FILEWATCHER_RESULT
DBMS_CRYPTO RE$NV_NODE SCHEDULER_FILEWATCHER_RES_LIST
DBMS_DEBUG_JDWP RE$RULE_HIT SYS_STUB_FOR_PURITY_ANALYSIS
DBMS_IJOB RE$RULE_HIT_LIST USER$
DBMS_ISCHED_CHAIN_CONDITION RE$TABLE_VALUE_LIST UTL_ENCODE
DBMS_ISCHED_REMDB_JOB RE$VARIABLE_TYPE UTL_FILE
DBMS_I_INDEX_UTL RE$VARIABLE_TYPE_LIST UTL_HTTP
DBMS_JOB RE$VARIABLE_VALUE UTL_I18N
DBMS_LOB SCHEDULER$_BATCHERR_ARRAY UTL_IDENT
DBMS_LOCK SCHEDULER$_CHAIN UTL_INADDR
DBMS_OUTPUT SCHEDULER$_CHAIN_LINK UTL_RAW
DBMS_PRVTAQIP SCHEDULER$_CHAIN_LINK_LIST UTL_SMTP
DBMS_RULEADM_INTERNAL SCHEDULER$_CLASS UTL_TCP
DBMS_RULE_ADM SCHEDULER$_CREDENTIAL UTL_URL
DBMS_RULE_INTERNAL SCHEDULER$_DESTINATIONS V$PARAMETER
Documented No
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvthsch.plb
Subprograms
ADD_AGENT_CERT DROP_AGENT_DESTINATION LOG_LOCAL_EXTERNAL_OUTPUT
ADD_EVENT_QUEUE_SUBSCRIBER DROP_CHAIN NEW_LOG_ID
ADD_GROUP_MEMBER DROP_CHAIN_RULE NORMALIZE_HOST_NAME
ADD_JOB_EMAIL_NOTIFICATION DROP_CHAIN_STEP OBFUSCATE_CREDENTIAL_PASSWORD
ADD_OUTPUT_BLOB DROP_CREDENTIAL OPEN_WINDOW
ADD_WINDOW_GROUP_MEMBER DROP_DATABASE_DESTINATION PARSE_EMAIL_ADDRESSES
AGENT_INSTALL_POST_STEPS DROP_FILE_WATCHER PRE_ALTER_CHAIN
AGENT_INSTALL_PRE_STEPS DROP_GROUP PRE_CREATE_CHAIN
ALTER_CHAIN DROP_JOB PRE_DROP_CHAIN
ALTER_CHAIN_STEP DROP_JOB_CLASS PURGE_LOG
ALTER_RUNNING_CHAIN DROP_PROGRAM PUT_FILE
AUDIT_SYS_PRIV DROP_PROGRAM_ARGUMENT RAISE_ORACLE_ERROR
BATCH_JOB_OPS DROP_SCHEDULE RAISE_SCHLIM_EVT
CHAIN_END DROP_SCHEDULER_ATTRIBUTE RECORD_RESEND_REQUEST
CHAIN_EVAL DROP_SCHEDULER_ATT_INT REGISTER_CALLBACK
CHAIN_EVAL_UPDATE_STEP_STATE DROP_WINDOW REMOTE_KILL
CHAIN_KILL DROP_WINDOW_GROUP REMOVE_EVENT_QUEUE_SUBSCRIBER
CHAIN_LOG ENABLE REMOVE_GROUP_MEMBER
CHAIN_PARSE_STRING ENQ_END_CHAIN_JOB REMOVE_JOB_EMAIL_NOTIFICATION
CHAIN_START EVALUATE_RULESET REMOVE_WINDOW_GROUP_MEMBER
CHAIN_STOP EVALUATE_RUNNING_CHAIN RESET_JOBSUFFIX_SEQ
CHECK_AQ_CBK_PRIVS EVENT_COND_FILTER RESET_JOB_ARGUMENT_VALUE
CHECK_COMPAT EXEC_JOB_RUN_LSA RESOLVE3_NAME
CHECK_CREDENTIAL EXPAND_FILENAME RESOLVE_IF_NAMED_DEST
CHECK_LOCAL_CREDENTIAL FILE_TRANSFER RESOLVE_NAME
CHECK_OBJECT_PRIVS FILE_WATCH_FILTER RETRIEVE_JOB_OUTPUT
CHECK_REQUEST_PRIVS FILE_WATCH_JOB RUN_CHAIN
CLOSE_WINDOW GENERATE_OBJECT_NAME RUN_JOB
COMPLETE_JOB_RUN GET_AGENT_INFO SEND_EVENT_EMAIL
CONVERT_DBMS_JOB GET_AGENT_PASS_VERIFIER SET_AGENT_REGISTRATION_PASS
COPY_JOB GET_AGENT_VERSION SET_BOOL_ATTRIBUTE
CREATE_AGENT_DESTINATION GET_AGENT_WALLET_LOCATION SET_CHAR_ATTRIBUTE
CREATE_CHAIN GET_BOOL_ATTRIBUTE SET_DATE_ATTRIBUTE
CREATE_CHAIN_STEP GET_CHAIN_EVAL_LOCK SET_EVTMSG_ARG
CREATE_CREDENTIAL GET_CHAIN_RULESET SET_INTERVAL_ATTRIBUTE
CREATE_DATABASE_DESTINATION GET_CHAR_ATTRIBUTE SET_INT_ATTRIBUTE
CREATE_FILE_WATCHER GET_CREDENTIAL_PASSWORD SET_JOB_ARGUMENT_VALUE
CREATE_GROUP GET_DATE_ATTRIBUTE SET_JOB_ATTRIBUTES
CREATE_JOB (2) GET_FILE SET_LAST_RUN_TIME
CREATE_JOBS GET_GLOBAL_DB_NAME SET_LIST_ATTRIBUTE
CREATE_JOB_CLASS GET_INTERVAL_ATTRIBUTE SET_SCHEDULER_ATTRIBUTE
CREATE_LOG_DIR GET_INT_ATTRIBUTE SET_SECURITY_HEADERS
CREATE_PROGRAM GET_LAST_RUN_TIME SHOW_ERRORS
CREATE_SCHEDULE GET_NOTIFICATIONS STIME
CREATE_WINDOW GET_RULE_LINKS STOP_JOB
CREATE_WINDOW_GROUP GET_SCHEDULER_ATTRIBUTE SUBMIT_REMOTE_EXTERNAL_JOB
DEFINE_CHAIN_RULE GET_STEP_STATE SUBMIT_REMOTE_FILE_WATCH
DEFINE_CHAIN_STEP GET_STEP_STATE_CF TRACE_EMAIL
DEFINE_METADATA_ARGUMENT GET_SYS_TIME_ZONE_NAME VALIDATE_DEST
DEFINE_PROGRAM_ARGUMENT GET_TNS_NVPAIR VALIDATE_EMAIL_ADDRESSES
DISABLE IS_SCHEDULER_CREATED_AGENT WATCH_FOR_FILES
DISABLE1_CALENDAR_CHECK LOG_DBMS_OUTPUT WRITE_FILE_WATCH_TRACE
 
AGENT_INSTALL_POST_STEPS (new 12.2)
Undocumented dbms_isched.agent_install_post_steps;
exec dbms_isched.agent_install_post_steps;
 
AGENT_INSTALL_PRE_STEPS (new 12.2)
Undocumented dbms_isched.agent_install_pre_steps;
exec dbms_isched.agent_install_pre_steps;
 
CHECK_COMPAT
Undocumented dbms_isched.check_compat;
exec dbms_isched.check_compat;
 
CONVERT_DBMS_JOB
Convert a dbms_job api created job into a scheduler job the dbms_job equivalent job will be removed.

As this is officially undocumented I am leaving this as it is until I learn more.

Relocated from DBMS_SCHEDULER ... I think ... but when?
dbms_isched.convert_dbms_job(job_name IN VARCHAR2);
Step 1: Create a job

CREATE OR REPLACE PROCEDURE test_job IS
BEGIN
  NULL;
END test_job;
/

DECLARE
 JobNo user_jobs.job%TYPE;
BEGIN
  dbms_job.submit(JobNo, 'begin test_job; end;', SYSDATE, 'SYSDATE + 36/86400');
  COMMIT;
END;
/

SELECT job, schema_user
FROM dba_jobs;


Step 2: Convert it

-- 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.
 
CREATE_AGENT_DESTINATION
Code from execsch.sql in /RDBMS/ADMIN dbms_isched.create_agent(
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;
/
 
CREATE_LOG_DIR
Creates the scheduler logging directory: I think. dbms_isched.check_compat;
--source code from {ORACLE_HOME}/rdbms/admin/execsch.sql
BEGIN
  dbms_isched.create_log_dir;
END;
/
 
EXPAND_FILENAME
The name is self-explanatory but I can not determine what, specifically, it does at this time dbms_isched.expand_filename(source_file IN VARCHAR2) RETURN VARCHAR2;
TBD
 
FILE_WATCH_JOB
Undocumented dbms_isched.file_watch_job;
exec dbms_isched.file_watch_job;
 
GET_AGENT_PASS_VERIFIER
Not sure for what it is intended but the output could lend itself to a number of security-related usages dbms_isched.get_agent_pass_verifier(
password IN RAW,
iter     OUT BINARY_INTEGER,
salt     OUT RAW,
hash     OUT RAW);
DECLARE
 pwd  RAW(128) := utl_raw.cast_to_raw('Morgan');
 iter BINARY_INTEGER;
 salt RAW(128);
 hash RAW(128);
BEGIN
  dbms_isched.get_agent_pass_verifier(pwd, iter, salt, hash);
  dbms_output.put_line(pwd);
  dbms_output.put_line(TO_CHAR(iter));
  dbms_output.put_line(salt);
  dbms_output.put_line(hash);
END;
/
 
GET_GLOBAL_DB_NAME
Returns the global database name dbms_isched.check_compat;
SELECT dbms_isched.get_global_db_name
FROM dual;
 
GET_LAST_RUN_TIME
Undocumented dbms_isched.get_last_run_time RETURN TIMESTAMP WITH TIME ZONE;
SELECT dbms_isched.get_last_run_time
FROM dual;
 
GET_SYS_TIME_ZONE_NAME
Returns the servers current time zone name dbms_isched.get_sys_time_zone_name(tzname OUT VARCHAR2);
DECLARE
  tz VARCHAR2(20);
BEGIN
  dbms_isched.get_time_zone_name(tz);
  dbms_output.put_line(tz);
END;
/
dbms_isched.check_compat;
 
GET_TNS_NVPAIR
Returns the TNSNAMES.ORA connction string dbms_isched.get_tns_nvpair(tns_entry IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_isched.get_tns_nvpair('PDBDEV')
FROM dual;
 
NORMALIZE_HOST_NAME
Returns fully qualified host name dbms_isched.normalize_host_name(hostname IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_isched.normalize_host_name('PERRITO4')
FROM dual;
 
OBFUSCATE_CREDENTIAL_PASSWORD
Every time this procedure is executed it generates a different password obfuscating the input string dbms_isched.obfuscate_credential_password(
credential_name IN  VARCHAR2,
password        IN  VARCHAR2,
opass           OUT VARCHAR2,
cur_schema      IN  VARCHAR2);
DECLARE
 opassVal VARCHAR2(120);
BEGIN
  dbms_isched.obfuscate_credential_password('UWCred', 'ZZYZX', opassVal, USER);
  dbms_output.put_line(opassVal);
END;
/
 
PARSE_EMAIL_ADDRESSES
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,mark@orcl.com,safra@orcl.com', retTab)
  FOR i IN 1 .. 3 LOOP
    dbms_output.put_line(retTab(i));
  END LOOP;
END;
/
 
REMOTE_KILL
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);
TBD
 
RESET_JOBSUFFIX_SEQ (new 12.2)
Undocumented dbms_isched.reset_jobsuffix_seq;
exec dbms_isched.reset_jobsuffix_seq;
 
RESOLVE_NAME
Either separates owner and object name from a fully qualified object name or, if owner is not supplied provides a substitute. dbms_isched.raise_oracle_error(
full_name     IN  VARCHAR2,
object_name   OUT VARCHAR2,
owner_name    OUT VARCHAR2,
default_owner IN  VARCHAR2);
DECLARE
 objName VARCHAR2(30);
 ownName VARCHAR2(30);
BEGIN
  dbms_isched.resolve_name('UWCLASS.SERVERS', objName, ownName, 'UNK');
  dbms_output.put_line('Object: ' || objName);
  dbms_output.put_line('Owner: ' || ownName);
END;
/

DECLARE
 objName VARCHAR2(30);
 ownName VARCHAR2(30);
BEGIN
  dbms_isched.resolve_name('SERVERS', objName, ownName, 'UNK');
  dbms_output.put_line('Object: ' || objName);
  dbms_output.put_line('Owner: ' || ownName);
END;
/
 
SEND_EVENT_EMAIL
Undocumented dbms_isched.raise_oracle_error(
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);
TBD
 
VALIDATE_EMAIL_ADDRESSES
Undocumented 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;
/

DECLARE
 addrList dbms_utility.lname_array;
BEGIN
  addrList(1) := 'a@b.com';
  addrList(2) := 'b@c.com';
  addrList(3) := 'c@d.com';
  dbms_isched.validate_email_addresses(NULL, addrList);
END;
/
 
WRITE_FILE_WATCH_TRACE
Undocumented but writes a file watcher trace

-- demo from initscfw.sql
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

import oracle.scheduler.agent.fileWatchTrace;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.Connection;

public class dbFWTrace implements fileWatchTrace
{
  public void writeTrace(String do_trc, String trc_string)
  {
    if (do_trc.equals("Y"))
    {
      try
      {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:default:connection");
        Connection conn = ods.getConnection();
        OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall(
            "{call dbms_isched.write_file_watch_trace(?, ?)}");
        ocs.setString(1, do_trc);
        ocs.setString(2, trc_string);
        ocs.executeUpdate();
        ocs.close();
      }
      catch (java.sql.SQLException sqlexception)
      {
        // ignore for now
      }
    }
  }
}
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_ISCHEDFW
DBMS_ISCHED_AGENT
DBMS_SCHEDULER
DBMS_SCHED_ARGUMENT_IMPORT
DBMS_SCHED_ATTRIBUTE_EXPORT
DBMS_SCHED_FILE_WATCHER_EXPORT
What's New In 12cR2
What's New In 18cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved