OraFAQ Forum: SQL & PL/SQL » DBMS

Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_JOB [message #217646]

Sat, 03 February 2007 23:10

oracle_guy
Messages: 13
Registered: February 2007

Junior Member

I need some help in DBMS_JOB. I have 2 tables A and B. Both have the same structure and have 350 columns each. Now table B holds some % of data (5% or 10% or 20%) of table A selected in a random manner.

The senior management asked me to use DBMS_RANDOM and DBMS_JOB.

I have created a procedure that will insert data into b. please see below

create or replace procedure insert_data
is
begin
insert into b (select *
from (select * from A
order by dbms_random.random)
where rownum<=(select (.05 * count(*))
from A));
end;
/

I am using Oracle 10gR2

This procedure runs fine and inserts the required data selected in a random fashion.

Now i hav never used dbms_job earlier. The requirement is that the data should be refreshed into table B every weekend.

Can any guide me in how to write a dbms_job program that will call the above procedure.

Kindly help?

[Updated on: Sat, 03 February 2007 23:12]

Report message to a moderator

Re: DBMS_JOB [message #217647 is a reply to message #217646]

Sat, 03 February 2007 23:36

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
if your on 10g. then use dbms_scheduler instead of dbms_job.
go through below link with practicl example.
http://www.oradev.com/dbms_scheduler.jsp

regards
Taj

Report message to a moderator

Re: DBMS_JOB [message #217648 is a reply to message #217646]

Sun, 04 February 2007 00:31

Frank
Messages: 7901
Registered: March 2000

Senior Member

Don't forget to delete the contents of b prior to filling it..
Also, did you consider using sample instead of using an exact percentage?
As for your 'senior management': since when do they decide what your query should look like? If they know so well what you should use, why don't they do it?
Or could it be that you are a junior and got an assignment that you should figure out yourself? Then it is not very wise to ask it here; you'd better try and find out yourself.
Asking here looks like an easy way out, but if somebody would provide you the exact way to do it, it will teach you nothing. You probably will not be able to do it yourself next time.

Report message to a moderator

Re: DBMS_JOB [message #217653 is a reply to message #217648]

Sun, 04 February 2007 00:55

oracle_guy
Messages: 13
Registered: February 2007

Junior Member

Frank,
I am nit a junior. But een though i have some experience i have my seniors. This message came from the client side team.

And this is not an assignment. I have to deliver it by wednesday.

Any help.

Report message to a moderator

Re: DBMS_JOB [message #217655 is a reply to message #217646]

Sun, 04 February 2007 00:59

oracle_guy
Messages: 13
Registered: February 2007

Junior Member

i have seen many examples of job scheduling. in all the examples i find that the block is basically an anonymous block.

whats the reason behind it and whats the exact advantage of it?

I may be worng, but since i am a newbie i find this quite different?

Report message to a moderator

Re: DBMS_JOB [message #217659 is a reply to message #217655]

Sun, 04 February 2007 03:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So did you schedule this job or not? Of course, you can not test it in real time (as you don't have many weekends till Wednesday), but you could practice scheduling the job (for example) every 20 minutes, so that you'll know HOW it is to be done.

There's no reason why one should schedule an anonymous PL/SQL block - you will schedule your procedure. Just terminate WHAT with a semicolon.

Show us what you've already did to schedule a job - i.e. how your DBMS_JOB (or DBMS_SCHEDULER) command looks like and say what prevents it from working, so that someone might help you.

Report message to a moderator

Re: DBMS_JOB [message #217902 is a reply to message #217659]

Tue, 06 February 2007 01:04

oracle_guy
Messages: 13
Registered: February 2007

Junior Member

Little foot,

What i meant is that when we execute DBMS_JOB or DBMS_SCHEDULER, why we do that inside a anonymous block.

In most of the example i find that the DBMS_SCHEDULER have been used inside and anonymous block to execute it.

Why this is do. Cant we call DBMS_SCHEDULER inside a stored procedure? Any particular reason for doing do?

Report message to a moderator

Re: DBMS_JOB [message #217914 is a reply to message #217902]

Tue, 06 February 2007 01:30

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Hi,
Procedures and Functions have come into picture for the purpose of re-usability and modularity.

Why do you want to put the DBMS_JOB or DBMS_SCHEDULER in a procedure?
You don't call them frequently. I think you will call them mostly once. Right?

Edit: Seems I have misunderstood the problem.
By
Vamsi

[Updated on: Tue, 06 February 2007 07:00]

Report message to a moderator

Re: DBMS_JOB [message #218012 is a reply to message #217914]

Tue, 06 February 2007 06:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

@Oracle_guy, do you refer to something like this?

     DECLARE
       jobno NUMBER;
     BEGIN
       dbms_job.submit(jobno, 
                       what      => 'prc_del_kon;',
                       next_date => TRUNC(SYSDATE) + 16/24,
                       INTERVAL  => 'sysdate + 1'
                      );
       commit;
     END;

This really is an anonymous PL/SQL block.

Toggle Spoiler

Why do we need it? Because we have to declare a numeric variable ('jobno' in this example) which is used as OUT parameter of the DBMS_JOB.SUBMIT procedure. It returns unique job identifier. I believe that, if there wasn't this parameter, we could start it just with

EXECUTE dbms_job.submit(what      => 'prc_del_kon;', 
                        next_date => TRUNC(SYSDATE) + 16/24,
                        INTERVAL  => 'sysdate + 1');

I might be wrong about it; let's hear another opinion.

Report message to a moderator

Previous Topic:	Query on namespace and dbms_session.set_context
Next Topic:	Difference between VARCHAR2(80) and VARCHAR2(80 BYTE)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 12:57:17 CST 2024