Home » SQL & PL/SQL » SQL & PL/SQL » dbms_jobs (oracle 10.2.0.4)
dbms_jobs [message #437219] Sat, 02 January 2010 00:24 Go to next message
ramsonline@rediffmail.com
Messages: 4
Registered: January 2010
Location: MUMBAI
Junior Member
we want to run multiple commands to process records at the same time. so we use dbms_scheduler to run 5 different jobs to compute data and write it to temporary tables (on commit delete rows). after completing the jobs we need to do the final processing and accordingly we wish to commit or rollback the whole transactions.
Now what happens is after execution of every job it automatically commits. How to avoid this.

In some article shows dbms_scheduler is autonomous transaction. It auto commits use dbms_jobs. Dbms_jobs also commiting the data.

Ex: Main procedure calling two jobs
First job is deleting the data
Second job is processing the data and inserting into global temporary table (on commit delete rows)
After that i cant get the temporary table data in main Procedure.
Re: dbms_jobs [message #437220 is a reply to message #437219] Sat, 02 January 2010 00:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
how can we reproduce your problem?
Re: dbms_jobs [message #437221 is a reply to message #437219] Sat, 02 January 2010 00:32 Go to previous messageGo to next message
ramsonline@rediffmail.com
Messages: 4
Registered: January 2010
Location: MUMBAI
Junior Member

Temporary Table : On Commit delete rows

Create global temporary table tblbrmast (
nFirmnumber number(10) not null /* Contains the Firmnumber */ ,
cBranchcode Char(4) not null /* Contains the Branch / H.O. */ ,
cBranchname Char(20) not null /* Contains the Branch Name */ ,
cRocode Char(1) not null ) on commit delete rows ;


Main Procedure : /* submitting 3 jobs */

create or replace procedure BBB is
lNjob NUMBER;
LCSTR VARCHAR2(500);
begin

LCSTR := 'BEGIN ldbo.AAAdel(''AAA1''); END;'; -- Calling AAAdel procedure
Dbms_Output.Put_Line('Start : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
DBMS_JOB.submit(job => lNjob, what => LCSTR);
DBMS_OUTPUT.put_line(lNjob);

LCSTR := 'BEGIN ldbo.AAA(''AAA2''); END;'; -- Calling AAA procedure
Dbms_Output.Put_Line('Start : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
DBMS_JOB.submit(job => lNjob, what => LCSTR);
DBMS_OUTPUT.put_line(lNjob);

LCSTR := 'BEGIN ldbo.AAA(''AAA3''); END;'; -- Calling AAA procedure
Dbms_Output.Put_Line('Start : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
DBMS_JOB.submit(job => lNjob, what => LCSTR);
DBMS_OUTPUT.put_line(lNjob);
Dbms_Output.Put_Line('End : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
end BBB;


CREATE OR REPLACE PROCEDURE Aaadel(Intime CHAR) IS
BEGIN
Dbms_Output.Put_Line('Start : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
delete from tblbrmast where cbranchcode=intime ;
dbms_output.put_line('Delete') ;
DBMS_LOCK.sleep(60);
Dbms_Output.Put_Line('End : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
END Aaadel;


CREATE OR REPLACE PROCEDURE Aaa(Intime CHAR) IS
BEGIN
Dbms_Output.Put_Line('Start : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
INSERT INTO Tblbrmast
(Nfirmnumber, Cbranchcode, Cbranchname, Crocode)
VALUES
(2, Intime, 'AAA6', 'R.O.');
DBMS_LOCK.sleep(60);
Dbms_Output.Put_Line('End : ' || To_Char(SYSDATE, 'hh24:mi:ss'));
END Aaa;



Note:

In all the three procedures i am not using any Commit or rollback ;

SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 700


In alert.log also i am not finding any errors.

If i use ordinary table,with the same procedure then it is working fine.
Re: dbms_jobs [message #437223 is a reply to message #437221] Sat, 02 January 2010 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each job runs in a different session and so can't see what others do in the temporary table and it is the same for the session that creates the job.

Regards
Michel
Re: dbms_jobs [message #437225 is a reply to message #437221] Sat, 02 January 2010 02:37 Go to previous messageGo to next message
ramsonline@rediffmail.com
Messages: 4
Registered: January 2010
Location: MUMBAI
Junior Member
Is there any solution ???

Regards
Rams
Re: dbms_jobs [message #437227 is a reply to message #437225] Sat, 02 January 2010 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not with this design and as we don't know what you trying to achieve (what is the business need) we can't help to change it.

Regards
Michel
Re: dbms_jobs [message #437229 is a reply to message #437227] Sat, 02 January 2010 03:13 Go to previous messageGo to next message
ramsonline@rediffmail.com
Messages: 4
Registered: January 2010
Location: MUMBAI
Junior Member
Dear Michel

My process contains

deletion
Manipulation
insert in temp table
and then finally insert the main table from temp table

My main table contains 4 Crore records.

All these process written in single procedure it will take lot of time.Thats why i am splitting the process in jobs.

deletion - First job
manipulation - Second job
insert in third job

Regards
Rams

[Updated on: Sat, 02 January 2010 03:49] by Moderator

Report message to a moderator

Re: dbms_jobs [message #437230 is a reply to message #437229] Sat, 02 January 2010 03:50 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a business requirement, this is how you want to implement it.
As the purpose is to find a more efficient way to implement, describing how you currently implement is not sufficient to know how to better implement.
What is needed is the business requirement or if you prefer why to write this.

Regards
Michel
Previous Topic: wildcard character
Next Topic: How to load the data from flat file? -- urgent please
Goto Forum:
  


Current Time: Sat Dec 10 09:25:47 CST 2016

Total time taken to generate the page: 0.05809 seconds