Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB
DBMS_JOB [message #217646] Sat, 03 February 2007 23:10 Go to next message
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 Go to previous messageGo to next message
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
Re: DBMS_JOB [message #217648 is a reply to message #217646] Sun, 04 February 2007 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: DBMS_JOB [message #217653 is a reply to message #217648] Sun, 04 February 2007 00:55 Go to previous messageGo to next message
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.
Re: DBMS_JOB [message #217655 is a reply to message #217646] Sun, 04 February 2007 00:59 Go to previous messageGo to next message
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?
Re: DBMS_JOB [message #217659 is a reply to message #217655] Sun, 04 February 2007 03:50 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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.
Re: DBMS_JOB [message #217902 is a reply to message #217659] Tue, 06 February 2007 01:04 Go to previous messageGo to next message
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?
Re: DBMS_JOB [message #217914 is a reply to message #217902] Tue, 06 February 2007 01:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 Go to previous message
Littlefoot
Messages: 20895
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.
Previous Topic: Query on namespace and dbms_session.set_context
Next Topic: Difference between VARCHAR2(80) and VARCHAR2(80 BYTE)
Goto Forum:
  


Current Time: Mon Dec 05 19:23:49 CST 2016

Total time taken to generate the page: 0.05794 seconds