| Auto executing a sql or pl/sql script [message #436960] |
Tue, 29 December 2009 23:42  |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
|
I need to run a script daily at a specific time or at start up time. How i can do this. This is very important for me, please if any one can give me quick trusted solution.
|
|
|
|
|
|
|
|
| Re: Auto executing a sql or pl/sql script [message #436978 is a reply to message #436964] |
Wed, 30 December 2009 01:17  |
bellouch
Messages: 5
Registered: December 2009
|
Junior Member |
|
|
This code allows auto execution of plsqql daily at 6:00PM:
DECLARE
jobno number;
code Varchar2(32000) := 'DECLARE BEGIN --SOME PLSQL CODE END;';
begin
dbms_job.submit(jobno, CODE,trunc(sysdate) + 1 + 18/24, ' trunc(sysdate) + 1 + 18/24');
commit ;
end ;
/
|
|
|
|
|
|
|
|
| Re: Auto executing a sql or pl/sql script [message #440103 is a reply to message #437044] |
Thu, 21 January 2010 02:00 |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
i have written following code to get this procedure ipcards.ipcards_data executed daily at 12:40:35 PM, but it is not doing so, what is missing............
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,start_date => TO_TIMESTAMP_TZ('2020/01/21 12:40:35.000000 +05:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=DAILY'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN ipcards.ipcards_data; END; '
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA'
,attribute => 'AUTO_DROP'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'IPCARDS.AVAILABLE_IP_CARDS_DATA');
END;
/
|
|
|
|
|
|
|
|
| Re: Auto executing a sql or pl/sql script [message #487743 is a reply to message #440105] |
Thu, 30 December 2010 11:33 |
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
|
Junior Member |
|
|
ThomasG wrote on Thu, 21 January 2010 00:04Start date 2020? Are you writing this for the TARDIS?
ThomasG,
With no disrespect to Malhi, a Dr Who reference is a bit of humor I needed today. As day-end, month-end, quarter-end, year-end for 2010 looks to eat my holiday's lunch.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
