tailieunhanh - Oracle Built−in Packages- P120

Oracle Built−in Packages- P120: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk The procedure The WHAT procedure changes the PL SQL call that comprises the job s PL SQL definition. Here s the header for this program PROCEDURE job IN BINARY_INTEGER what IN VARCHAR2 Parameters are summarized in the following table. Parameter Description job Unique identifier of the job what PL SQL code to execute as a job Restrictions The WHAT procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section section. Example In this example job 100 is modified to execute a procedure called . When the job is run by the job queue it will run in a session that has NLS_DATE_FORMAT set as in the ALTER SESSION command. SQL ALTER SESSION SET NLS_DATE_FORMAT YYYY MM DD HH24 MI SS SQL execute 100 When the what parameter is changed to modify the actual job to execute the user s current session NLS settings are also recorded and become part of the job s execution environment. The what parameter must be enclosed in single quotes and the PL SQL call must be terminated with a semicolon. To embed literal strings in the PL SQL call use two single quotes around the literal. Removing Jobs and Changing Job Execution Status The REMOVE BROKEN and RUN procedures let you remove jobs from the job queue and change the execution status of jobs. The procedure The REMOVE procedure removes an existing job from the job queue. If the job is currently executing it will run to normal completion but will not be rescheduled. The header for this procedure is PROCEDURE job IN BINARY_INTEGER where job is the unique identifier of the job. This program does not raise any package exceptions. Restrictions The REMOVE procedure can be executed only for .