Home » SQL & PL/SQL » SQL & PL/SQL » Auto executing a sql or pl/sql script (oracle 11g)
Auto executing a sql or pl/sql script [message #436960] Tue, 29 December 2009 23:42 Go to next message
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 #436963 is a reply to message #436960] Tue, 29 December 2009 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
DBMS_SCHEDULER as documented at http://tahiti.oracle.com
Re: Auto executing a sql or pl/sql script [message #436964 is a reply to message #436963] Tue, 29 December 2009 23:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
or you can do it using Cron job in unix.



http://en.wikipedia.org/wiki/Cron
http://aplawrence.com/BDD/bbcronbasics.html
Re: Auto executing a sql or pl/sql script [message #436978 is a reply to message #436964] Wed, 30 December 2009 01:17 Go to previous messageGo to next message
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 #437038 is a reply to message #436978] Wed, 30 December 2009 07:57 Go to previous messageGo to next message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
dbms_scheduler would be the choice over dbms_job as it is much more flexible and powerful.
Re: Auto executing a sql or pl/sql script [message #437044 is a reply to message #437038] Wed, 30 December 2009 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I disagree, DBMS_SCHEDULER is a (great) complement to DBMS_JOB but does not replace it.

See DBMS_SCHEDULER and DBMS_JOB wiki pages.

Regards
Michel
Re: Auto executing a sql or pl/sql script [message #440103 is a reply to message #437044] Thu, 21 January 2010 02:00 Go to previous messageGo to next message
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 #440105 is a reply to message #440103] Thu, 21 January 2010 02:04 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Start date 2020? Are you writing this for the TARDIS?

[Updated on: Thu, 21 January 2010 02:05]

Report message to a moderator

Re: Auto executing a sql or pl/sql script [message #440106 is a reply to message #440105] Thu, 21 January 2010 02:08 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/449/0/

sriram
Re: Auto executing a sql or pl/sql script [message #487743 is a reply to message #440105] Thu, 30 December 2010 11:33 Go to previous message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
ThomasG wrote on Thu, 21 January 2010 00:04
Start 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.


Previous Topic: dblink
Next Topic: Trigger help
Goto Forum:
  


Current Time: Sun Sep 25 06:09:16 CDT 2016

Total time taken to generate the page: 0.10592 seconds