Shulou(Shulou.com)05/31 Report--

This article focuses on "program analysis in Oracle scheduling Schedule features". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "Oracle scheduling Schedule features in the program analysis" it!

The concept of program to procedure is a bit like "programs" or "applications" at the operating system level. But program is set by dba, and program is an object, an executable object.

The actual operation of program can be divided into three types:

PL/SQL BLOCK standard PL/SQL code block

STORED PROCEDURE compiled PL/SQL stored procedure, JAVA stored procedure or external subroutine

Applications other than EXECUTEABLEoracle databases, such as operating system commands

1. Use the DBMS_SCHEDULER.CREATE_PROGRAM process to create a Programs

For example:

Begin

Dbms_scheduler.create_program (program_name = > 'myprogram1'

Program_action = >'/ bin/date'

Program_type = > 'EXECUTABLE'

Enabled = > TRUE)

End

/

Program_name specifies the name of a program

Program_type specifies the type of program, the three types described above

The actions actually performed by program_action should be used in association with the previous PROGRAM_TYPE parameter. For example, if you specified PROGRAM_TYPE as "PLSQL_BLOCK", the action to be executed here should be a standard piece of pl/sql code.

If you previously specified PROGRAM_TYPE as "STORED_PROCEDURE", then the action to be executed here should be the stored procedure defined in ORACLE (including Java stored procedure)

If you specified PROGRAM_TYPE as "EXECUTABLE" earlier, you should specify command line information (including path information) for external commands here.

NUMBER_OF_ARGUMENTS specifies the number of parameters supported. The default value is 0, which means there are no parameters. Each program can support up to 255parameters. Note that if PROGRAM_TYPE is set to PLSQL_BLOCK, this parameter is automatically ignored.

ENABLED specifies whether to set the created program to a valid state, which is false by default.

COMMENTS comment information.

Management of Program

After the program is created, it's about how the program executes. When I introduced the parameters of the CREATE_PROGRAM procedure, I mentioned that each program supports a maximum of 255parameters. To add parameters to the program, you can use the DEFINE_PROGRAM_ARGUMENT procedure.

However, before adding parameters to it, pay attention to the number of NUMBER_OF_ARGUMENTS specified by program. If the value is 0, an error will be reported when adding parameters to it.

To query the information of the created program, you can use the USER_SCHEDULER_PROGRAMS view, for example:

SQL > select program_name,program_type,program_action,number_of_arguments,enabled from user_scheduler_programs

PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABL

MYPROGRAM1 EXECUTABLE / bin/date 0 FALSE

The value of NUMBER_OF_ARGUMENTS was not specified when program was created earlier, so we need to modify the value to a non-zero value first, as follows

Begin

Dbms_scheduler.set_attribute ('myprogram1','NUMBER_OF_ARGUMENTS',1)

End

The operation is still done using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure. In addition, it should be noted that the NUMBER_OF_ARGUMENTS parameter of program can be changed if you want to. Normally, this processing must be confirmed before program is in enabled.

Otherwise, an ORA-27465 error will be triggered, so before you modify the parameters of program, you must first make sure that the enabled state of the program is changed to false.

So how do you modify the state properties of a program that is already in enabled state? It's actually very simple. Do you remember the DBMS_SCHEDULER.DISABLE process you used to operate jobs before? This procedure works just as well for program, and is called exactly the same way, for example:

Begin

Dbms_scheduler.disable ('myprogram1')

End

In addition, if you want to set the program to the enabled state, you can simply execute the DBMS_SCHEDULER.ENABLE process, which is not illustrated here.

Next, you can add path parameters to the my_program1 you just created, as follows:

Begin

Dbms_scheduler.DEFINE_PROGRAM_ARGUMENT (program_name = > 'myprogram1'

Argument_position = > 1

Argument_name = > 'dirpath'

Argument_type = > 'VARCHAR2'

Default_value = >'/ home/oracle')

End

/

Query the parameters defined for program through the USER_SCHEDULER_PROGRAM_ARGS view

Select program_name,argument_name,argument_position,argument_type default_value from user_scheduler_program_args

PROGRAM_NAME ARGUMENT_NAME ARGUMENT_POSITION DEFAULT_VALUE

-

MYPROGRAM1 DIRPATH 1 VARCHAR2

2. It is also very simple to delete the argument of program, using the DROP_PROGRAM_ARGUMENT process, for example:

Begin

Dbms_scheduler.drop_program_argument ('myprogram1','dirpath')

End

The first parameter of the procedure specifies the program name, and the second parameter specifies the defined argument name, but you can also specify the location of the argument here, that is, the ARGUMENT_POSITION column value in the result returned by the previous example view.

It is even easier to delete program by using the DROP_PROGRAM process, for example:

Begin

Dbms_scheduler.drop_program ('myprogram1')

End

Of course, when you delete a program, all arguments corresponding to that program will also be deleted.

In fact, when you create a job in SCHEDULER, you can also specify an external program to execute. Job in SCHEDULER is more like JOBS inherited from previous versions, except that the JOBS managed by SCHEDULER in 10g is more powerful.

At this point, I believe you have a deeper understanding of "program analysis in Oracle scheduling Schedule features". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.