Oracle DBMS_ISCHED
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 OPEN_WINDOW
ADD_OUTPUT_BLOB DROP_CREDENTIAL PARSE_EMAIL_ADDRESSES
ADD_WINDOW_GROUP_MEMBER DROP_DATABASE_DESTINATION PRE_ALTER_CHAIN
ALTER_CHAIN DROP_FILE_WATCHER PRE_CREATE_CHAIN
ALTER_CHAIN_STEP DROP_GROUP PRE_DROP_CHAIN
ALTER_RUNNING_CHAIN DROP_JOB PURGE_LOG
AUDIT_SYS_PRIV DROP_JOB_CLASS PUT_FILE
BATCH_JOB_OPS DROP_PROGRAM RAISE_ORACLE_ERROR
CHAIN_END DROP_PROGRAM_ARGUMENT RAISE_SCHLIM_EVT
CHAIN_EVAL DROP_SCHEDULE RECORD_RESEND_REQUEST
CHAIN_EVAL_UPDATE_STEP_STATE DROP_SCHEDULER_ATTRIBUTE REGISTER_CALLBACK
CHAIN_KILL DROP_SCHEDULER_ATT_INT REMOTE_KILL
CHAIN_LOG DROP_WINDOW REMOVE_EVENT_QUEUE_SUBSCRIBER
CHAIN_PARSE_STRING DROP_WINDOW_GROUP REMOVE_GROUP_MEMBER
CHAIN_START ENABLE REMOVE_JOB_EMAIL_NOTIFICATION
CHAIN_STOP ENQ_END_CHAIN_JOB REMOVE_WINDOW_GROUP_MEMBER
CHECK_AQ_CBK_PRIVS EVALUATE_RULESET RESET_JOB_ARGUMENT_VALUE
CHECK_COMPAT EVALUATE_RUNNING_CHAIN RESOLVE_NAME
CHECK_CREDENTIAL EVENT_COND_FILTER RESOLVE_IF_NAMED_DEST
CHECK_LOCAL_CREDENTIAL EXEC_JOB_RUN_LSA SEND_EVENT_EMAIL
CONVERT_DBMS_JOB GET_AGENT_INFO 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
 
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;
/
 
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;
 
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
 
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
DBMS_SCHEDULER
Packages

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-2014 Daniel A. Morgan All Rights Reserved