dba_jobs / auto-commit?

[Orca777]

ORacle 8.1.7.3.5 OS/390


Hello;
is it possible that PL/SQL-Jobs, queud with dba_jobs, which have no commit could be autocommited?
We have a job detected running every minute, without commit, but with changes in databse.

Orca

[jmodic]

Originally posted by Orca777
is it possible that PL/SQL-Jobs, queud with dba_jobs, which have no commit could be autocommited?

No.

[svk]

I guess when a session terminates, COMMIT happens by default unless things are explicitly rolled back.

[jmodic]

Originally posted by svk
I guess when a session terminates, COMMIT happens by default unless things are explicitly rolled back.

You guessed wrong!

When session teerminates ROLLBACK happens to any changes that were not commited previously.

[svk]

Orca777,

Can you post some more info @ the job itself ?
What is it suppose to do ? Is it a stored proc or just a pl/sql script ?

[svk]

A simple test :

create table t1 (d date);
truncate table t1;
create or replace procedure ins_t1 as
begin
insert into t1 values (sysdate);
end;
/
show error
variable x number;
execute DBMS_JOB.SUBMIT(:x,'ins_t1;',SYSDATE,'SYSDATE + 1/(24*60)');

So, the job is defined to run every minute. No COMMIT mentioned anywhere.

Now, check the contents of table t1 after every minute and you will see a row there every minute.....

[SANJAY_G]

The transactions will be commited if the session is closed with a exit command and will be rolledback if the session terminates abnormally.

Sanjay

[esreddy]

hi ,
I am trying to do the same job shedule in my system.The table T1 is created and Procedure also created, but no rows are inserted into Table T1.Why?

[nishant]

Hi,

It will not insert because you have to write commit in the procedure.

Take Care.

[SANJAY_G]

It works without commit also.
May be your job is not running try forcing the job to run and check the results.

Sanjay