Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose
Undocumented
AUTHID
DEFINER
Data Types
TYPE boolean_list ....;
TYPE name_array ....;
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_ISCHED' UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_ISCHED' ORDER BY 1;
-- 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_cloud_credential(
credential_name IN VARCHAR2,
username IN VARCHAR2,
password IN VARCHAR2,
database_role IN VARCHAR2,
windows_domain IN VARCHAR2,
comments IN VARCHAR2,
key IN VARCHAR2,
enabled IN BOOLEAN,
connect_identifier IN VARCHAR2);
dbms_isched.create_credential_ccall(
credential_name IN VARCHAR2,
username IN VARCHAR2,
password IN VARCHAR2,
database_role IN VARCHAR2,
windows_domain IN VARCHAR2,
comments IN VARCHAR2,
enabled IN BOOLEAN,
invoker IN VARCHAR2,
sys_privs IN BINARY_INTEGER,
sch_privs IN BINARY_INTEGER,
cur_schema IN VARCHAR2,
key IN VARCHAR2,
connect_identifier IN VARCHAR2,
is_cloud_credential IN BOOLEAN);
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
*
ORA-27494: operation not permitted on lightweight and in-memory jobs
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;
/
*
ORA-24098: invalid value damorgan_yahoo.com for EMAIL_ADDRESS
-- 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
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
}
}
}
}
/
