setup to run dbms_scheduler job to run a sql every day

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?
mahjagAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
gatorvipCommented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.