?
Solved

setup to run dbms_scheduler job to run a sql every day

Posted on 2010-01-08
3
Medium Priority
?
1,192 Views
Last Modified: 2013-12-18
I have oracle 10 g and a 2 .sql to run every day on a purticular time in produciton., would like to know how I can use dbms_scheduler to do that automatically
here are some informaiton about scripts
1. scripts contain some ddl and dml statments - what happens if one dml or ddl statement fails?
2. second script has &month to take as input - how can this be done without modifying the sql?

will the process stops completely if one of the script fails?  need to know if this is too much to handle in dbms_scheduler?
0
Comment
Question by:mahjag
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 26212702
I assume you have 2 .sql files that you want to run.  I've never used "program_type        => 'EXECUTABLE'" but you might get it to call sqlplus.exe.

Typically you set up a stored procedure and schedule it to run.

1: It depends on how you set everything up.
2: Again, it depends.  If you go with a stored procedure you can pass the value for month into the procedure.

check out:
http://www.oracle-base.com/articles/10g/Scheduler10g.php
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 26212847
OP, I think you are asking some specific questions without providing specific information. In addition to what slightwv has already said, I will add the following:

1. A lot of things can happen. For example, if you try to create a table and for whatever reason that doesn't work, then you try to insert into that table, you're going to have a bunch of both DDL and DML errors to deal with.

Personally, I would try to store the sql files as a single PL/SQL package and attempt to run that way.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26213145
>>will the process stops completely if one of the script fails?
It deponds on how you are handling the errors. If you are using Begin---End for ddl, then answer is No
If you are using WHENEVER SQLERROR EXIT ROLLBACK, it will be rollbacked.

>>second script has &month to take as input - how can this be done without modifying the sql?
What type of modification you want. If its a column name, then you have to generate dynamic sql, if you are taking about value, then you can pass the parameter to sql file itself. Use &1, &2 etc as parameter.

>> scripts contain some ddl and dml statments - what happens if one dml or ddl statement fails?
If you are using execute immediate for DDL, and one DDL fails, it will not rollback the whole transaction.
Check with this example
create or replace procedure pr_temp_dev1
as
begin
  insert into temp_dev values (10,10,10);
  execute immediate('alter table temp_dev add three number');
  commit;
end;
/

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question