Instance of the job timed call stored procedure in Oracle

The process of extracting the specified data from the metadata table into the target table using the job simulation timing.

First, create a meta-data table

--create Table test_origianl

　　

CREATE TABLE Test_original_data (
ID Number (10,2) primary key,
Name VARCHAR2 (200),
Desccont varchar2 (100),
Datetype Char (5)

);

Inserting data into the metadata table

　　

INSERT into Test_original_data values (2.9, ' name1 ', ' desc1 ', ' int ');
INSERT into Test_original_data values (3.0, ' name2 ', ' desc2 ', ' long ');
INSERT into test_original_data values (2.8, ' name3 ', ' desc3 ', ' float ');
INSERT into Test_original_data values (2.6, ' name4 ', ' desc4 ', ' do ');
INSERT into Test_original_data values (2.1, ' name223 ', ' desc33 ', ' float ');
INSERT into test_original_data values (5.12, ' name22r34 ', ' desc3r34 ', ' float ');

III. Submission of services

Commit

Iv. Creating a target table

---CREATE TABLE

To maintain the same table structure as the metadata table, the records in the metadata table can be inserted directly into the target table without any modification, and the target table is created in a ctas manner.

　　

CREATE TABLE Test_destination_dat
As
SELECT * from Test_original_data where 1!=1;

Only the same table structure is created and records in the metadata table are not imported.

V. Submission of services

Commit

Vi. creating a stored procedure that is called periodically

----CREATE PROCEDURE

　　

Create or replace procedure Job_test_pro
Authid Current_User
As
Begin

--First TRUNCATE table to clear the original data, re-import the symbol condition data (here use truncate in the case of large data volume dramatic increase in efficiency)
Execute immediate ' TRUNCATE TABLE Test_destination_dat ';
INSERT INTO Test_destination_dat (SELECT * from Test_original_data where datetype= ' float ');
exception
When others then
Dbms_output.put_line (' ErrorMsg ' | | sqlerrm| | ' ErrorCode ' | | SQLCODE);
End Job_test_pro;

Note: 1, in the stored procedure is generally no CREATE TABLE, TRUNCATE TABLE permissions. If you use the statement directly in the stored procedure TRUNCATE TABLE Test_destination_dat will error, you cannot execute the statement. In a stored procedure, it is generally not possible to use the Create Table,truncate table-like statement directly, if you want to use these statements must be implemented using Excute Immediate + SQL statement to execute.

2, Authid Current_User:

This statement is important if we do not add this statement when creating the stored procedure execution of the stored procedure will not succeed because the default stored procedure does not have permissions such as create Table,truncate table, even if the current user has DBA authority. If there are operations in the stored procedure that create the table and truncate the table, there are two ways to resolve the problem.

(1) The grant CREATE table to user is displayed with the permissions given to the user create table.
(2) Use the Authid current_user in the stored procedure to identify the use of the current user's permissions.

VII. Create Job

1. Define Job Name

Variable job2014 Number---Defines the value of the field job in the data dictionary that automatically generates the job ordinal data type in Oracle corresponding to Dba_jobs. The job value is now 41 for the name JOB2014 data type is number

2. Register the job to the Dba_jobs table to start the job call stored procedure periodically

Begin

Dbms_job.submit (: job2014, ' Job_test_pro; ', sysdate, ' sysdate+1/1440 ');

End

/

Commit

Note: the Dbms_job.submit () method is defined as follows

Sys.dbms_job.submit (Job =: Jobno,
What ' myproc;myprocs;myprocss ' (' Test '); ', '///use a semicolon to separate multiple stored procedures, and have parameters to write directly
Next_date = To_date (' 07-04-2011 22:00:00 ', ' dd-mm-yyyy hh24:mi:ss '),
Interval = ' sysdate+1 ');
1.It is important to note that the job variable must be defined before the Submit method.
2, the second argument of the Submit method is the name of a stored procedure, remember to add the ";" Number in the back
3, in Next_date is a time-type variable instead of a string, so be careful not to use it as a string, do not need to quote the argument
4, the last parameter interval is a string type, remember to add quotation marks
5, the first parameter format is: Jobno, the purpose is to bind the job and stored procedures.
6. The most common errors are as follows: Ora-01008:not all variables bound is the meaning of no variable defined. Be sure to remember to define the JOBNO variable when you use the Submit method.
7, the following is often the method of setting interval:
Daily fixed-time operation, such as 8 in the morning: Trunc (sysdate+1) + 8/24
Daily: Trunc (sysdate+1)
Weekly: Trunc (sysdate+7)
Per month: trunc (sysdate+30)
Each Sunday: Next_day (trunc (sysdate), ' SUNDAY ')
6 points per day: trunc (sysdate+1) +6/24
Half an hour: sysdate+30/1440
8. Declaring variables and binding job variables to stored procedures and registering them in the Dba_jobs table must be performed under Sqlplus and not run successfully in Plsqldevloper.
EightJob Exception Handling
Job cannot run condition processing
1. First to understand the job parameter description: Job-related parameters One is job_queue_processes, this is the number of processes running the job, of course, the system inside the job is greater than this value, there will be queued, the minimum is 0, indicating that the job is not running, The maximum value is 36, when the corresponding process on the OS is snpn,9i after the process of managing the job on the OS is called CJQN. You can use the following SQL to determine how many snp/c JQ are currently running.
SELECT * from V$bgprocess, this paddr non-empty SNP/CJQ process is the currently idle process, and some indicates a working process.
The other is job_queue_interval, in the range between 1--3600, the unit is the second, this is the wake-up job process, because every time the SNP run he rested, need to wake him regularly, this value can not be too small, too small can affect the performance of the database.
2. Diagnosis: First determine whether the above two parameters are set correctly, especially the first parameter, set to 0, all jobs will not run, confirm the error, we continue down.
3. Use the following SQL to view the job's broken,last_date and Next_date,last_date is the end time of the most recent job run, Next_date is the next execution time based on the frequency of the setup, According to this information can determine whether the job last normal, but also to determine the next time is right, SQL is as follows:
SELECT * FROM Dba_jobs
Sometimes we find his next_date is January 1, 4000, indicating that the job is either in the running, or the state is break (broken=y), if the broken value of the job is found to be Y, to find the user to understand, Determine whether the job can be broken, if not broken, then change the broken value to N, modify and then use the above SQL to see his last_date has changed, the job can run normally, modify the broken status of SQL as follows:
Declare
BEGIN
Dbms_job. Broken (<job_id>,false);
END;
4. Use the following SQL query to see if the job is still running
SELECT * FROM Dba_jobs_running
If you find that the job has run for a long time and is not over, check the reason. General job running lock related resources, you can look at the v$access and v$locked_object the two V iew, if other processes are found to lock the job-related object, including pkg/function/ Procedure/table and other resources, then it is necessary to remove the other processes, the need to remove the job process, and then rerun to see the results.
5. What if the above is normal, but the job is not run? Then we have to consider restarting the job process to prevent the SNP process from dying and causing the job not to run, the instructions are as follows:
Alter system set job_queue_processes=0--shuts down the job process, waiting for 5--10 seconds
Alter system set job_quene_processes=5--restore the original value
Nine, manually run the job instructions
Begin
Dbms_job.run (: job2014);
End
/
X. Delete Job
Begin
Dbms_job.remove (: job2014);
End
/
Xi. View job-related views and annotations for each field
 　　 

Select job,last_date,last_sec,broken,failures,interval, what from dba_jobs

a few more important fields in Dba_jobs

Job: Refers to the job ID number. Like the above .

Failures: Thenumber of failures that the job executes, if more than 15 times, then the broken column will be labeled Y, and the job will not be run in the future

Broken: The default is N, and if Y means that the job! is no longer executed

Interval: the interval between the time the job was executed.

What: The job 's actual work.

Summary: Simulate timing to extract the specified type of data from the metadata table to the target table, calling the stored procedure with a timed job.

　　

Instance of the job timed call stored procedure in Oracle