Home » SQL & PL/SQL » SQL & PL/SQL » Please tell me use of DBMS_SCHEDULER
Please tell me use of DBMS_SCHEDULER [message #624460] |
Tue, 23 September 2014 01:33 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
Hi Guys,
I have study more about DBMS_SCHEDULER but still could not get what is use of DBMS_SCHEDULER, suppose I have a procedure then how will use DBMS_SCHEDULER. Now I am using oracle 10g.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 900, 10);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1800, 20);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1450, 20);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 3975, 30);
SQL> CREATE PROCEDURE update_salary(p_id IN emp.empno%TYPE,p_factor IN NUMBER) AS
2 v_count INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO v_count FROM emp WHERE empno = p_id;
5
6 IF v_count = 1 THEN
7 UPDATE emp SET sal = sal * p_factor WHERE empno = p_id;
8 COMMIT;
9 END IF;
10 EXCEPTION
11 WHEN OTHERS THEN
12 ROLLBACK;
13 END update_salary;
14 /
Procedure created.
SQL>
|
|
|
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624469 is a reply to message #624468] |
Tue, 23 September 2014 02:14 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
aaditya321 wrote on Tue, 23 September 2014 08:04Hello Sir,
I have study more from documentation but unable to understand it, please give any simple procedure and tell me how to use DBMS_SCHEDULER in any procedure.
<snip>
You don't (generally) use the Scheduler IN a procedure, you use it to CALL a procedure. There are many simple examples in the Administrator's Guide, have you tried any yet?
|
|
|
|
|
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624505 is a reply to message #624501] |
Tue, 23 September 2014 05:27 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
Suppose I have created a program like
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_plsql_block_prog',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''HR''); END;',
enabled => TRUE,
comments => 'Program to gather HR''s statistics using a PL/SQL block.');
END;
/
My Procedure is like:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 900, 10);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1800, 20);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1450, 20);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 3975, 30);
SQL> CREATE PROCEDURE update_salary(p_id IN emp.empno%TYPE,p_factor IN NUMBER) AS
2 v_count INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO v_count FROM emp WHERE empno = p_id;
5
6 IF v_count = 1 THEN
7 UPDATE emp SET sal = sal * p_factor WHERE empno = p_id;
8 COMMIT;
9 END IF;
10 EXCEPTION
11 WHEN OTHERS THEN
12 ROLLBACK;
13 END update_salary;
14 /
Procedure created.
SQL>
Then how will use DBMS_SCHEDULER here.
|
|
|
|
|
|
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624517 is a reply to message #624514] |
Tue, 23 September 2014 05:58 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
aaditya321 wrote on Tue, 23 September 2014 11:46John Watson wrote on Tue, 23 September 2014 05:41I think you are trolling. No-one can be this dumb.
Goodbye.
Really I am not trolling, but I am avid to learn it, please once explain it with example rather sharing any link. Well, MC reckons the problem may be brains, not trolling. OK. Here are two examples you can work on:
We need to define a job that is launched by a schedule. It should run every ten minutes, sending a mail to OP reminding him to switch on his brain.
Then we need a job that is launched by an event. Whenever OP makes a post, it should search the doc index and return a list of references to him.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624562 is a reply to message #624558] |
Tue, 23 September 2014 08:34 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
aaditya321 wrote on Tue, 23 September 2014 18:54 I want to use DBMS_SCHEDULER in my procedure, how to execute procedure through DBMS_SCHEDULER.
You requested my opinion. Ok, so my opinion is that your requirement is absurd and the above quoted sentence of your's is simply contradictory. You said, you want to use the scheduler in your procedure, and you want to execute your procedure through scheduler. Simply, contradictory.
|
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624638 is a reply to message #624567] |
Wed, 24 September 2014 07:17 |
|
sss111ind
Messages: 634 Registered: April 2012 Location: India
|
Senior Member |
|
|
--create dummy table
create table emp4 as select * from emp where 1=2;
--create procedure which will be scheduled
create or replace procedure emp_test
is
begin
--logic what you want to do
insert into emp4 select * from emp;
commit;
end;
BEGIN
dbms_scheduler.create_program (
program_name => 'emp_test_program',
program_type => 'STORED_PROCEDURE',--procdure type
program_action => 'emp_test', --procedure name
enabled => true,
comments => 'Program to insert ');
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'emp_test_schedule',
start_date => sysdate,
repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
end_date => null,
comments => 'Repeats every 5 minutes');
dbms_scheduler.create_job (
job_name => 'emp_test_job',
program_name => 'emp_test_program',
schedule_name => 'emp_test_schedule',
enabled => true,
comments => 'Job defined to insert emp4 table');
end;
begin
dbms_scheduler.drop_job('emp_test_job');
dbms_scheduler.drop_schedule('emp_test_schedule');
dbms_scheduler.drop_program('emp_test_program');
end;
select * from emp4;
select * from user_scheduler_jobs ;
select * from user_scheduler_job_run_details ;
http://www.oracle-base.com/articles/10g/scheduler-10g.php
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHHBGGI
[Updated on: Wed, 24 September 2014 07:18] Report message to a moderator
|
|
|
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624670 is a reply to message #624558] |
Wed, 24 September 2014 09:50 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
aaditya321 wrote on Tue, 23 September 2014 09:24My problems is here that I want to use DBMS_SCHEDULER in my procedure, how to execute procedure through DBMS_SCHEDULER.
And you have been told, you DO NOT call DBMS_SCHEDULER "in" a procedure. You use DBMS_SCHEDULER to "call" a procedure. The most basic of examples is in the documentation.
|
|
|
Re: Please tell me use of DBMS_SCHEDULER [message #624812 is a reply to message #624670] |
Fri, 26 September 2014 01:17 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
I got help from sss111ind and try it, sss111ind is telling correct as I want.
Select * from employee;
ID Employee's Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
create table emp4 as select * from employee where 1=2;
create or replace procedure emp_test
is
begin
insert into emp4 select * from employee;
commit;
end;
/
BEGIN
dbms_scheduler.create_program (
program_name => 'emp_test_program',
program_type => 'STORED_PROCEDURE',--procdure type
program_action => 'emp_test', --procedure name
enabled => true,
comments => 'Program to insert ');
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'emp_test_schedule',
start_date => sysdate,
repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
end_date => null,
comments => 'Repeats every 5 minutes');
dbms_scheduler.create_job (
job_name => 'emp_test_job',
program_name => 'emp_test_program',
schedule_name => 'emp_test_schedule',
enabled => true,
comments => 'Job defined to insert emp4 table');
end;
begin
dbms_scheduler.drop_job('emp_test_job');
dbms_scheduler.drop_schedule('emp_test_schedule');
dbms_scheduler.drop_program('emp_test_program');
end;
select * from emp4;
ID Employee's Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
ID Employee's Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
ID Employee's Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
ID Employee's Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
ID Employee's Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
I think output is not correct here, please tell me meaning of freq=minutely; interval=5; bysecond=0;
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 11:44:30 CDT 2024
|