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

Editor to share with you how to use Programs in ORACLE, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

2.1Create Programs

The Program object in Scheduler is not a "program" or "application" in the conventional sense, but an "object", defined by DBA, which is a special object that performs a function. The actions actually performed in Program can be divided into the following three types:

PL/SQL BLOCK: standard pl/sql code block

STORED PROCEDURE: compiled PL/SQL stored procedures, or Java stored procedures, and external c subroutines

EXECUTEABLE: applications other than ORACLE databases, such as operating system commands, etc.

The DBMS_SCHEDULER.CREATE_PROGRAM procedure is used to create a Programs, which supports the following parameters:

JSSWEB > desc dbms_scheduler.create_program

Parameter Type Mode Default?

PROGRAM_NAME VARCHAR2 IN

PROGRAM_TYPE VARCHAR2 IN

PROGRAM_ACTION VARCHAR2 IN

NUMBER_OF_ARGUMENTS BINARY_INTEGER IN Y

ENABLED BOOLEAN IN Y

OMMENTS VARCHAR2 IN Y

As shown above, the first three are required parameters, and the actual meaning of each parameter is as follows:

PROGRAM_NAME: specify a program name

PROGRAM_TYPE: the type of Program, as mentioned earlier, Program supports three types

PROGRAM_ACTION: the action actually performed 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 PROGRAM_TYPE is specified as "STORED_PROCEDURE", then the action to be executed here should be the stored procedure defined in ORACLE (including Java stored procedure). If PROGRAM_TYPE is specified as "EXECUTABLE", then the command line information (including path information) for external commands should be specified here.

NUMBER_OF_ARGUMENTS: specify the number of supported parameters. 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: you don't have to say that anymore, comment messages.

Let's take a look at the actual operation. There is no challenge for PL/SQL or PROCEDURE (which can be called directly in ORACLE). Let's create a program and directly call the ls command in the operating system, as follows:

SQL > BEGIN

2 DBMS_SCHEDULER.CREATE_PROGRAM (

3 program_name = > 'my_program1'

4 program_action = >'/ bin/date'

5 program_type = > 'EXECUTABLE'

6 enabled = > TRUE)

7 END

8 /

PL/SQL procedure successfully completed.

2.2 Managing Programs

As to how to execute the defined program, let's start with a relation. When we introduced the parameters of the CREATE_PROGRAM process, we mentioned that each program supports a maximum of 255parameters. To add parameters to the program, you can use the DEFINE_PROGRAM_ARGUMENT process. 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

--

MY_PROGRAM1 EXECUTABLE / bin/ls 1 FALSE

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

SQL > exec dbms_scheduler.set_attribute ('my_program1','NUMBER_OF_ARGUMENTS',1)

PL/SQL procedure successfully completed.

Yes, the operation still uses the DBMS_SCHEDULER.SET_ATTRIBUTE process. In addition, it should be noted that the NUMBER_OF_ARGUMENTS parameter of program can be changed if you want to. Normally, the processing must be confirmed before program is in enabled, otherwise an ORA-27465 error will be triggered. Therefore, before you modify the parameter of program, you must first make sure that the enabled status of 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? Yes, this procedure works just as well for program, and it is called exactly the same way, for example:

SQL > exec dbms_scheduler.disable ('my_program1')

PL/SQL procedure successfully completed.

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:

SQL > BEGIN

2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

3 program_name = > 'my_program1'

4 argument_position = > 1

5 argument_name = > 'dirpath'

6 argument_type = > 'VARCHAR2'

7 default_value = >'/ home/oracle')

8 END

9 /

PL/SQL procedure successfully completed.

Query the parameters defined for program through the USER_SCHEDULER_PROGRAM_ARGS view, for example:

SQL > select program_name,argument_name,argument_position,argument_type, default_value from user_scheduler_program_args

PROGRAM_NAME ARGUMENT_NAME ARGUMENT_POSITION DEFAULT_VALUE

--

MY_PROGRAM1 DIRPATH 1 VARCHAR2

It is also easy to delete the argument of program by using the DROP_PROGRAM_ARGUMENT process, for example:

SQL > exec dbms_scheduler.drop_program_argument ('my_program1','dirpath')

PL/SQL procedure successfully completed.

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:

SQL > exec dbms_scheduler.drop_program ('my_program1')

PL/SQL procedure successfully completed.

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. Programs differs from Jobs in that Jobs is a defined, regularly executed task, while Programs is a defined object waiting to be executed.

The above is all the content of the article "how to use Programs in ORACLE". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.