第61-62次：2015-06-06星期六

botang · 发表于 2015-5-29 09:57:14

eventbase11g.sql

select * from scheduler_test;
truncate table scheduler_test;
select scheduler_test_seq.currval from dual;
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg t_event_queue_payload;
BEGIN
l_queue_msg := t_event_queue_payload('give_me_a_pear');
DBMS_AQ.enqueue(queue_name => 'event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select * from scheduler_test;
-----
------
create or replace procedure proc_test( p_1 number , p_2 out number)
is
begin
update employees set salary=10000 where employee_id=p_1;
select salary into p_2 from employees where employee_id=p_1;
end;
select * from user_errors;
declare
v_salary number(8,2);
begin
proc_test(100,v_salary);
dbms_output.put_line(v_salary);
end;
----
----
create or replace procedure proc_test_io( p_1 in out number )
is
begin
p_1 := p_1*2;
end;
select * from user_errors;
------
---
declare
v_1 number;
begin
v_1 := 1;
proc_test_io( v_1);
dbms_output.put_line(v_1);
end;
-----
-----------------------------------------------------------
create or replace procedure procnew( p_1 t_event_queue_payload )
is
BEGIN
INSERT INTO scheduler_test (id, created_date)
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
COMMIT;
END;
select * from user_errors;
begin
dbms_scheduler.create_program (
program_name => 'programnew',
program_action=> 'procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end;
select * from user_scheduler_programs;
select * from user_scheduler_program_args;
begin
dbms_scheduler.define_metadata_argument (
program_name => 'programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
select * from user_scheduler_program_args;
begin
dbms_scheduler.enable ('programnew');
end;
---------------------------------
alter table scheduler_test add ( event_name varchar2(40)) ;
select * from scheduler_test;
create or replace procedure procnew( p_1 t_event_queue_payload )
is
BEGIN
INSERT INTO scheduler_test
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE, p_1.event_name );
COMMIT;
END;
select * from user_errors;
begin
dbms_scheduler.create_program (
program_name => 'programnew2',
program_action=> 'procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end;
begin
dbms_scheduler.define_metadata_argument (
program_name => 'programnew2',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
begin
dbms_scheduler.enable ('programnew2');
end;
select * from user_scheduler_program_args;
select * from scheduler_test;

复制代码

chain11g.sql：

BEGIN
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '1=1',
action => 'START chain_step_1',
rule_name => 'chain_rule_1',
comments => 'First link in the chain.');
end;
---
begin
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_1 completed',
action => 'START chain_step_2',
rule_name => 'chain_rule_2',
comments => 'Second link in the chain.');
end;
begin
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_2 completed',
action => 'START chain_step_3',
rule_name => 'chain_rule_3',
comments => '3rd link in the chain.');
end;
begin
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_3 completed',
action => 'START chain_step_4',
rule_name => 'chain_rule_4',
comments => '4th link in the chain.');
end;
begin
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_4 completed',
action => 'END',
rule_name => 'chain_rule_5',
comments => 'End of the chain.');
END;
BEGIN
DBMS_SCHEDULER.ENABLE ('test_chain_1');
END;
select * from user_scheduler_chains;
SELECT *
FROM user_scheduler_running_chains;
select * from scheduler_test;
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg t_event_queue_payload;
BEGIN
l_queue_msg := t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
---
truncate table scheduler_test;
select * from scheduler_test;

复制代码

database-remote.sql：

alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=6)';
select * from dba_users order by 1;
BEGIN
DBMS_XDB.SETHTTPPORT(8888);
END;
SQL> @?/rdbms/admin/prvtrsch.plb
BEGIN
DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
END;
SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
select * from dba_scheduler_db_dests;
select * from dba_scheduler_external_dests;
select * from dba_scheduler_external_dests;
select * from dba_scheduler_dests;
select * from dba_scheduler_db_dests;
begin
dbms_scheduler.create_database_destination(destination_name => 'DB_2',agent => 'agent_1',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
end;
begin
dbms_scheduler.create_database_destination(destination_name => 'DB_1',agent => 'agent_1',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
end;
select * from dba_scheduler_credentials;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB_ORCL"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_DB2',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_1' ,
enabled => true);
END;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB_UTFORCL"',
job_type => 'STORED_PROCEDURE',
job_action => 'HR.PROC1',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_DB2',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_2' ,
enabled => true);
END;
select * from dba_scheduler_groups;
select * from dba_scheduler_group_members;
BEGIN
DBMS_SCHEDULER.create_group(
group_name => 'hr.group1',
group_type => 'DB_DEST',member => 'LOCAL,DB_1,DB_2'
);
END;
select * from dba_scheduler_groups g where g.group_name='GROUP1';
select * from dba_scheduler_group_members gm where gm.group_name='GROUP1';
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB_GROUP"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=9999 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_GROUP',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'HR.GROUP1' ,
enabled => true);
END;
select * from dba_SCHEDULER_JOB_RUN_DETAILS;

复制代码

jobarry.sql：

create table t05317_lw( a number ) ;
alter table t05317_lw modify ( a date ) ;
create or replace procedure proc05317_lw
is
begin
insert into t05317_lw values(sysdate);
commit;
end;
--EM---
begin
dbms_scheduler.create_job(
job_name => 'lwjob',
program_name=>'proglw',
schedule_name=>'schedulelw',
job_style=>'LIGHTWEIGHT');
end;
----
DECLARE
newjob sys.job;
newjobarr sys.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array();
-- Allocate sufficient space in the array
newjobarr.extend(100);
-- Add definitions for jobs
FOR i IN 1..100 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'LWTJK' || to_char(i),
job_style => 'LIGHTWEIGHT',
job_template => 'proglw',
enabled => TRUE );
-- Add job to the array
newjobarr(i) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
select * from hr.t05317_lw;

复制代码

window_group.sql：

create or replace procedure hr.proc21
is
begin
update hr.employees set salary=salary+1 where employee_id=200;
commit;
end;
BEGIN
DBMS_SCHEDULER.create_group(
group_name => 'hr.group1',
group_type => 'DB_DEST',
member => 'LOCAL');
END;
SELECT group_name, member_name
FROM user_scheduler_group_members;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'hr.job21',
job_type => 'stored_procedure',
job_action => 'hr.proc21',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; bysecond=0',
end_date => SYSTIMESTAMP + 1/24,
credential_name => 'credential14',
destination_name => 'hr.group1',
enabled => TRUE);
END;

复制代码