في حالة انقضاء مدة.....

[babor_kaz]

في حالة انقضاء مدة من الزمن .... اريد ان يقوم بتغيير قيم في الجداول
ماذا و اكتب و اين اكتب؟؟؟؟؟

[mustafa76]

اقراء عن dbms_job اتعب فتتعلم

لا اريد ان اراك هنا ابداً لا بعد ان تعرف ما هو ال dbms_job

والى الامام جميعاً

[babor_kaz]

شكرا يا اخوي يا مصطفى على هاي المعنويات اللي بدك تعطيني اياها مع اني متغرب عن بلدي بس والله حاسس عن جد انك اخوي.... لكن انا لازم اعمل نظام محاسبة خلال مدة زمنية معينة وانا والحمد لله بنيت الجداول و حللت النظام وبدأت اعمل VAILDATION لكني والله جديد على ORACLE ما صالي 3 شهور بعمل و فضل رب العالمين صار عندي خبرةلابأس فيها بس بدنا شويت دعم منكم.... على فكرة دورت على DBMS.JOB في ملفات المساعدة ما لقيت شي

[mustafa76]

شكرا يا اخوي يا مصطفى على هاي المعنويات اللي بدك تعطيني اياها مع اني متغرب عن بلدي

هل تحاول ان تسترق قلبي مهو الحال من بعضه اخوك مغترب كمان .

في الهليب لا يوجد شيء في اي محرك بحث على الانترنت وعلى سبيل المثال www.dogpile.com اكتب dbms_job ابحث سوف تجد مئات المواقع التي تشرح ذلك


والى الامام جميعاً

[mustafa76]

هيا معي هيا يا ببور

شيل يا معلم شيل الله يعينك

Overview
--------

This article contains information on how to use the DBMS_JOB package to
submit a job for execution at specific times. Examples are provided
for scheduling jobs for the following times:

o Monday through Friday
o Midnight every night
o Every day at the same time


Submit Procedure
----------------

The submit procedure submits a new job to the job queue. The following is
the header for the procedure:

PROCEDURE DBMS_JOB.SUBMIT
(job OUT BINARY_INTEGER,
what IN VARCHAR2
NEXT_DATE IN DATE DEFAULT SYSDATE,
Interval IN VARCHAR2 DEFAULT 'null',
No_parse IN BOOLEAN DEFAULT FALSE);

The parameters are summarized as follows:

Parameter Description

Job Unique identifier of the job
What PL/SQL code to execute as a job
Next_date Next execution date of the job
Interval Date expression to compute next execute of date
No_parse Flag indicating whether to parse job PL/SQL at time
of submission (FALSE) or execution (TRUE)


Monday through Friday Example
-----------------------------

The following example shows how to schedule a job to execute Monday through
Friday:

DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
NEXT_DAY(SYSDATE,''TUESDAY''),
NEXT_DAY(SYSDATE,''WEDNESDAY''),
NEXT_DAY(SYSDATE,''THURSDAY''),
NEXT_DAY(SYSDATE,''FRIDAY'') ))');

The following example shows how to schedule a job to execute Monday through
Friday at 6:00 pm:

DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
NEXT_DAY(SYSDATE,''TUESDAY''),
NEXT_DAY(SYSDATE,''WEDNESDAY''),
NEXT_DAY(SYSDATE,''THURSDAY''),
NEXT_DAY(SYSDATE,''FRIDAY'') )) + 18/24');

Notes:

** Two single quotes are used around the day of the week, not double quotes.

** Ensure that the 4th parameter is all on one line. Allow the line to wrap
if necessary. The 4th parameter only allows 200 characters. Several white
spaces can cause this limit to be reached quickly.


Midnight Example
----------------

The following example shows how to schedule a job to execute at midnight
every night:

DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate) + 1,
'sysdate + 1');

The 'trunc(sysdate) + 1' sets the time back to the current day, midnight,
and informs the scheduler to start the job at the following midnight.

The 'sysdate + 1' sets the interval to 1 day. This job would run the first
time at midnight, then every midnight afterwards.

To schedule the job at 8 a.m., specify the next_date parameter as
trunc(sysdate) + 1 + 8/24.


Daily Example
-------------

The following example shows how to schedule a job to execute every day at the
same time:

DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate)+?.??/24,
'trunc(sysdate+1)+?.??/24');

To run the job at the same time every day, make sure that you pass the same
value '?.??/24' to both the 'next_date' and 'interval' parameters. The value
returned by trunc(sysdate) is midnight today and the value returned by
trunc(sysdate + 1) is midnight tomorrow.


References
----------

"Oracle8 Server Administrator's Guide", Chapter 7, (A54641-01)
"Oracle8I Administrator's Guide", Chapter 8, (A67772-01)
"Oracle8I Supplied Packages Reference, Volume 1", Chapter 16, (A67840-01)

[mustafa76]

شيل شيل يا حمال الاسية

Using the DBMS_JOB Package

Introduction
------------

DBMS_JOB is an Oracle PL/SQL package provided to users. It is available with
PL/SQL 2.2 and higher. DBMS_JOB allows a user to schedule a job to run at a
specified time. A job is submitted to a job queue and runs at the specified
time. The user can also input a parameter that specifies how often the job
should run. A job can consist of any PL/SQL code.


Job Environment
---------------

Oracle7 records the following environment characteristics whenever a job
is submitted to the job queue or a job's definition is altered:

current user
user submitting or altering job
current schema
MAC privileges (if appropriate)

The following NLS parameters are also recorded by Oracle:

NLS_LANGUAGE
NLS_TERRITORY
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT

To change a job's environment, use the DBMS_SQL package and the ALTER SESSION
command. This only changes the current execution of the job.


SNP Background Processes
------------------------

SNP processes run in the background and implement database snapshots and job
queues. If an SNP process fails, Oracle7 restarts it without affecting the
rest of the database.

An SNP process can run one job at a time with a maximum of ten SNP processes
running simultaneously. The INIT.ORA initialization file contains three
parameters that control the behavior of the SNP processes:

Parameters Description

JOB_QUEUE_PROCESSES How many processes to start. If set to
zero, no jobs are executed.
Default is 0. Range is 0..10.

JOB_QUEUE_INTERVAL How long an interval the process will
sleep before checking for a new job.
Default is 60 sec. Range is 1..3600 sec.

JOB_QUEUE_KEEP_CONNECTIONS Controls whether an SNP process closes
any remote database connections.
Default is False. Range is True/False.


The DBMS_JOB Package
--------------------

Below are a list of procedures available with the DBMS_JOB package. Most
procedures in this package require a COMMIT before the changes are noticed by
the SNP processes.


Procedure SUBMIT
----------------

To submit a job to the job queue.

DBMS_JOB.SUBMIT(job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2,
no_parse IN BOOLEAN)


Parameters Description

Job When a job is created a job number is
assigned to it. This job number is
unique. You must use the job number
whenever you alter or remove the job.
As long as the job exists, the number
will remain the same.

What The PL/SQL code you wish to execute. It is
usually a call to a stored procedure, which
can have any number of parameters. Use two
single quotes around strings and a
semicolon at the end of the job definition.

Next_date The date when the job will execute. Default
is SYSDATE.

Interval A function that calculates the next time the
job is to execute. Default is NULL.

No_parse If TRUE, Oracle parses the code the first
time the job is executed. If FALSE,
the job is parsed when submitted. Default is
FALSE.


The following is an example of a DBMS_JOB.SUBMIT:

SQL> EXECUTE dbms_job.submit(:x,'procedure_name;',sysdate,'sysdate+1')
SQL> COMMIT;

When a job is submitted to the job queue, it runs automatically depending
on the NEXT_DATE and INTERVAL parameters. Jobs will not run automatically
when submitted on newly created databases that have not been bounced.

If a newly created database is bounced, then submitted jobs will run
automatically as expected. Also, when a database is started in restricted
mode, no jobs will be run.

Import/Export:

Jobs can be imported and exported. If a job is defined in one database, it can
be transferred to another database. This does not change the job number,
environment, or definition.

Job Owner:

The person who submits a job to the job queue is considered the job's owner.
The job's owner is the only one who can alter the job, force the job to run,
or remove the job from the queue. If you need to look up any information
regarding a submitted job, DBA_JOBS and USER_JOBS contain a lot of
information such as the job number, user, status, etc.

Below is a description of the DBA_JOBS table:

SQL> desc dba_jobs
Name Null? Type
------------------------------- -------- ----
JOB NOT NULL NUMBER
LOG_USER NOT NULL VARCHAR2(30)
PRIV_USER NOT NULL VARCHAR2(30)
SCHEMA_USER NOT NULL VARCHAR2(30)
LAST_DATE DATE
LAST_SEC VARCHAR2(8)
THIS_DATE DATE
THIS_SEC VARCHAR2(8)
NEXT_DATE NOT NULL DATE
NEXT_SEC VARCHAR2(8)
TOTAL_TIME NUMBER
BROKEN VARCHAR2(1)
INTERVAL NOT NULL VARCHAR2(200)
FAILURES NUMBER
WHAT VARCHAR2(2000)
CURRENT_SESSION_LABEL RAW MLSLABEL
CLEARANCE_HI RAW MLSLABEL
CLEARANCE_LO RAW MLSLABEL
NLS_ENV VARCHAR2(2000)
MISC_ENV RAW(32)

Job Definitions:

The WHAT parameter of the SUBMIT procedure specifies the job definition.
The WHAT parameter should be a string that calls the stored procedure to
be run. There can be any number of parameters. All parameters should be
IN parameters. The only exceptions to this rule are the special
identifiers next_date and broken. Job definitions should be enclosed in
single quotes and terminated with a semicolon.

DBMS_JOB.SUBMIT(1234,
'employee_stats(''PROGRAMMER'',''FULL_TIME'',''HIRE_DATE'');',
SYSDATE,
SYSDATE + 7);


Procedure REMOVE
----------------

Use the REMOVE procedure to remove a job from the job queue.

DBMS_JOB.REMOVE(job IN BINARY_INTEGER);

Parameters Description
Job Job number. As long as the job exists, the
number will remain the same.

The only parameter required is the job number. A job that is currently running
is not interrupted. When the job completes, then the job is removed. You can
only remove a job that you own. If you try to remove a job you do not own, you
receive a message stating the job does not exist.


Procedure CHANGE
----------------

To alter a job that has already been submitted to the job queue, use the
CHANGE procedure.

DBMS_JOB.CHANGE(job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2);

Parameters Description

Job Job number. As long as the job exists, the
number will remain the same.

What The PL/SQL code you want executed. It is
usually a call to a stored procedure,
which can have any number of parameters.
Use two single quotes around strings and a
semicolon at the end of the job definition.

Next_date The date when the job will execute.

Interval A function that calculates the next time the
job is to execute.

The CHANGE procedure is used to alter more than one job characteristic at
once. If you specify NULL for the WHAT, NEXT_DATE, or INTERVAL parameter,
the current value for that parameter is not changed.


Procedure WHAT
--------------

This procedure is used to alter the definition of a job.

DBMS_JOB.WHAT(job IN BINARY_INTEGER,
what IN VARCHAR2);

Parameters Description

Job Job number. As long as the job exists,
the number remains the same.

What The PL/SQL code you want executed. It is
usually a call to a stored procedure, which
can have any number of parameters. Use two
single quotes around strings and a semicolon
at the end of the job definition.

When you execute the WHAT procedure, Oracle records your current environment,
which then becomes the new environment for the job.


Procedure NEXT_DATE
-------------------

The NEXT_DATE procedure is used to alter the next date that Oracle executes
a job.

DBMS_JOB.NEXT_DATE(job IN BINARY_INTEGER,
next_date IN DATE);

Parameters Description

Job Job number. As long as the job exists, the
number will remain the same.

Next_date The date when the job will execute next.


Procedure INTERVAL
------------------

The INTERVAL procedure is used to alter the interval between executions for
a specified job.

DBMS_JOB.INTERVAL(job IN BINARY_INTEGER,
interval IN VARCHAR2);

Parameters Description

Job Job number. As long as the job exists, the
number will remain the same.

Interval A function that calculates the next time the
job is to execute.


Procedure BROKEN
----------------

Use the BROKEN procedure to mark a job as broken or not broken.

DBMS_JOB.BROKEN(job IN BINARY_INTEGER,
broken IN BOOLEAN,
next_date IN DATE);


Parameters Description

Job Job number. As long as the job exists, the
number remains the same.

Broken Status of the job. If TRUE, the job is
marked as broken. If FALSE, the job is
marked as not broken and is run at the
time specified by next_date.

Next_date The date when the job will execute.


A job is marked either broken or not broken. Oracle7 does not attempt to run
broken jobs. You can use the DBMS_JOB.RUN procedure to force a broken job to
run. If that job runs successfully, the job is marked as not broken.

There are two ways a job can break:

1. Oracle7 fails to successfully execute the job after sixteen attempts.

2. The job is marked as broken with the DBMS_JOB.BROKEN procedure.

Only jobs that you own may be marked as broken.


Procedure RUN
-------------

The DBMS_JOB.RUN procedure can be used to force a specific job to run
immediately.

DBMS_JOB.RUN(job IN BINARY_INTEGER);

Parameters Description

Job Job number. As long as the job exists, the
number remains the same.

When executing a job with the RUN procedure, Oracle7 recomputes the next
execution date starting on the date the job is RUN. When you use the RUN
procedure, the job is executed in your current session. Your session's
packages are also reinitialized when the job is run.

Only jobs that you own can be executed using the RUN procedure.

An implicit COMMIT is performed when procedure RUN is executed.
For this reason, you cannot rollback once you execute a job using
procedure RUN.


Viewing Jobs in the Data Dictionary
-----------------------------------

There are several data dictionary views that contain information about jobs.

DBA_JOBS and USER_JOBS contain information such as job number, next_date,
interval, broken status, and failures. You can also query the view
DBA_JOBS_RUNNING to see a list of all jobs in the database that are currently
executing.

Below is a describe on the DBA_JOBS_RUNNING view.

SQL> desc DBA_JOBS_RUNNING
Name Null? Type
------------------------------- -------- ----
SID NUMBER
JOB NUMBER
FAILURES NUMBER
LAST_DATE DATE
LAST_SEC VARCHAR2(8)
THIS_DATE DATE
THIS_SEC VARCHAR2(8)


ولا تنسانا في آخر الامر من صالح الدعاء

[babor_kaz]

اشكرك يا مصطفى لكن انا عندي احساس انه مو هذا اللي بدي اياه....
شوف .... انا عندي حقل في قاعدة البيانات اسمه TPT_CHRG و لنحكي انه فيه قيمة 300 وعندي حقل ثاني اسمه TPT_BAL في البداية رح يكون 0 لكن بعد انقضاء شهر من تاريخ تسجيل RECORD (اللي اصلا موجود حقل للتاريخ) بدي اياه يضيف على TPT_BAL قيمة 300 .... وهكذا .... يعني باللغات البرمجة العادية بدي اياه يصير هيك:TPT_BAL :=TPT_CHRG+TPT_BAL ارجو انها و ضحت الصورة...
و اذا DBMS.JOB يفي بالغرض ارجوا الشرخ خطوه خطوه اذا فيه مجال كيف ابدأ اعمل فيه

وروح يا اخوي يا مصطفى الله يفتحها بوجهك (بس ما يفتحها زي ما بقصدوها الزرقاويين (ضربة موس))

[mustafa76]

يا زلمة فغشت كل احاسيسي وكينوناتي يزلمه اقراهن وبعدين شغل احساسك المرهف

[أبو الروض]

 

هههههههههههههههههههههههههههههههههههههههههههههه

تعرفوا ماذا أتمنى الان ؟؟؟

 

أن يجتمع الأخ mustafa76 و babor_kaz الان للقتال

يمكن الاخ بابور إستفز مصطفى

 

لاتزعل أخ بابور سأساعدك في هذا الباكيج

خذ هذه الصفحة وإدعيلي ولأخيك في الله مصطفى

 

طبعا شكرا لك مصطفى على الموقع الرائع للبحث وهو

www.dogpile.com

 

والله يوفقكم جميعا

 

وإليك شرح عن dbms_job

dbms_job.rar