Archive for the ‘Packages’ Category

Scheduling Job using DBMS_JOBS

Create a Job (run it every day)

VARIABLE v_jobnum NUMBER;
BEGIN
dbms_job.submit(:v_jobnum, ‘MY_STORED_PROCEDURE;’, sysdate,’sysdate+1′);
END;

print v_jobnum

BEGIN
DBMS_JOB.SUBMIT(JOB => :jobnumber,
WHAT => ‘DBMS_STATS.GATHER_DATABASE_STATS(options => ”GATHER AUTO”);’,
NEXT_DATE => to_date(’11:30 01/23/06′,’HH24:MI MM/DD/YY’),
INTERVAL => ‘SYSDATE + 1′);
COMMIT;
END;

Running Existing Job

While the point is to automate jobs, occasionally you may find you need to run a job manually. To do this you can call the RUN procedure. This is especially useful if you need to run a job which has been marked as broken.

BEGIN
DBMS_JOB.RUN(JOB => 21);
END;

exec dbms_job.run(job_number);

How to tell if a job has failed

When a database job fails an error will be written to the Oracle alert log with the error number of ORA-12012 and will include the job number which failed.

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => TRUE);
END;

The following will mark job 21 as not broken and have it execute at 11:00pm on January 23.

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => FALSE, NEXT_DATE => TO_DATE(’23:00 01/23/06′, ‘HH24:MI MM/DD/YY’));
END;

Checking out Failed Jobs

select job, log_user, last_date, next_date, broken, interval,failures, WHAT from dba_jobs;

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => TRUE);
END;

Changing a Job

You can update a job with the DBMS_JOB.CHANGE procedure. This procedure takes job number, code, next date and interval, in that order, as conditions. Whatever you don’t want to change can be passed as NULL.

With that in mind, the command to change just the interval would look something like this:

BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => ‘SYSDATE + 7′);
END;

Remove a Job from the Job Queue

exec dbms_job.remove(job => 21);

 

 

 

First and Last Day of Quarter

select TRUNC(sysdate, ‘Q’),TRUNC(ADD_MONTHS(sysdate, +3), ‘Q’)-1 from dual ;

Oracle Supplied Packages

DBMS_SQL

Using DBMS_SQL, one can write stored procedures that use dynamic SQL. It can issue data definition language statements in PLSQL

 

CREATE OR REPLACE PROCEDURE delete_all_rows
  (tab_name IN VARCHAR2, rows_del OUT NUMBER)
  IS
  cursor_name : = DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE ( cursor_name, ‘DELETE FROM ‘ ||tab_name,    DBMS_SQL.NATIVE);
  rows_del := DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

 

DBMS_DDL

DBMS_DDL provides access to some SQL DDL statements from stored procedures. It includes ALTER_COMPILE and ANALYZE_OBJECT procedures. 

DBMS_DDL.ALERT_COMPILE  (‘PROCEDURE’ ,’USER’, ‘OBJECT_NAME’ );   #DDL

DBMS_DDL.ANALYZE_OBJECT(‘ OBJECT_TYPE’, ‘USER’, ‘NAME’ , ‘COMPUTE’);    

DBMS_JOBS and DBMS_SCHEDULER

It enables scheduling and execution of PL/SQL programs:

Submitting jobs
Executing jobs
Removing and Suspending jobs

begin
--job:=101;   --job created to execute for every minute.
sys.dbms_job.submit(job => job,
what => 'del_test_info_p;',
next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
interval => 'TRUNC(SYSDATE,''MI'')+1/(24/60));

commit;
end;

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => ‘dummy_job’,

    job_type        => ‘PLSQL_BLOCK’,

    job_action      => ‘BEGIN NULL; /* Do Nothing */ END;’,

    start_date      => SYSTIMESTAMP,

    repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);

END;

DBMS_OUTPUTLINE

DBMS_OUTPUT.PUT_LINE(‘I got here:’||:new.col||’ is the new value’);

UTL_FILE

The UTL_FILE package provides text file I/O  from within PL/SQL. In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIRCREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

    

declare      
       outfile         UTL_FILE.file_type;

      locdataline   VARCHAR2 (150);

BEGIN

     outfile := UTL_FILE.fopen (‘/u99/sas_data’, ‘VehData.dat’, ‘w’);

      FOR v1 IN veh_data

      LOOP

         BEGIN

            locdataline :=    v1.a  || ‘|’   || v1.b || ‘|’   || v1.c || ‘|’   || v1.d   || ‘|’;

            UTL_FILE.put_line (outfile, locdataline);

         END;

      END LOOP;

      UTL_FILE.fclose (outfile);

END

UTL_HTTP

The UTL_HTTP package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. You can use it to access data on the Internet over HTTP.

UTL_TCP

With the UTL_TCP package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols and e-mail.

DBMS_STATS

Below is a sample execution of dbms_stats with the options clause.

exec dbms_stats.gather_schema_stats( –

ownname          => ‘SCOTT’, –

options          => ‘GATHER AUTO’, –

estimate_percent => dbms_stats.auto_sample_size, –

method_opt       => ‘for all columns size repeat’, –

cascade          => true, –

degree           => 15 –

)

Here is a representative example of invoking dbms_stats in 10g:

DBMS_STATS.gather_schema_stats(

ownname=>’<schema>’,

estimate_percent=>dbms_stats.auto_sample_size

cascade=>TRUE,

method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

 

OTHER USEFUL PACKAGES

DBMS_ALERT

DBMS_DESCRIBE

DBMS_LOCK

DBMS_SESSION

DBMS_APPLICATION_INFO

DBMS_TRANSACTION

DBMS_SHARED_POOL

DBMS_UTILITY

DBMS_LOGMNR

DBMS_PROFILER

DBMS_REPAIR

DBMS_SPACE

DBMS_STATS