Submit scheduled jobs easily

PLVISION (refer to earlier post) has a neat PACKAGE that makes periodic dbms_job submission very easy.

Amongst OTHERS, you can submit a job USING THE unix cron-like parameters
eg:

plvJOB.submit (
'purge_plv_log', -- <----- name OF job
'purge_plv_log', -- <----- dbms_job.what
'0 0 * * *', -- <----- cron frequency parameter
plvJOB.schedule, -- <----- a CONSTANT to indicate cron pattern above
SYSDATE,
'purge plv log');

WITH THE above line, my purge_plv_job job will run every DAY at 0000
hours (IN 24 HOUR notation).

Links

• Subscribe to this feed

free and good pl/sql resources

This is a gem !
Check out http://www.revealnet.com/pipelines/dba/index.asp for PL/Vision Code Library. The PL/Vision Code Library provides over 1,000 production-ready PL/SQL functions and procedures that extend the capabilities of the PL/SQL language.

Have been using it extensively and it saves a lot of coding.

I particularly like the :
1. plvlst package - I use this as a memory (eg if an item exists in the list, it is in memory, otherwise add it to the list)
2. plvtmr package - profile the speed of my programs
3. plvrand package - random number generator

There are a lot of other utility packages that would help everyone.

----------------------------------------------------------
Here's another
Check out http://www.smdi.com/employee/johnf/orastuff.htm.
John Flack has kindly contributed several utility packages.

I use his LIST package to sort lists.

I also modified the cmp function in the package so that it would sort numbers as numbers instead of text.

-- excerpt of cmp function------------------------
FUNCTION cmp (
first_item IN VARCHAR2,
second_item IN VARCHAR2,
cmpfnc IN VARCHAR2 DEFAULT '=') RETURN INTEGER IS
return_value INTEGER;
v_first_item plv.DBMAXVC2;
v_second_item plv.DBMAXVC2;
v_length PLS_INTEGER;
BEGIN
v_first_item := first_item;
v_second_item := second_item;

IF cnvt.to_number_dft (first_item) IS NOT NULL AND
cnvt.to_number_dft (second_item) IS NOT NULL THEN
v_length := GREATEST (LENGTH (v_first_item), LENGTH
(v_second_item));
v_first_item := LPAD (v_first_item, v_length,'0');
v_second_item := LPAD (v_second_item, v_length, '0');
END IF;
IF cmpfnc = '>' THEN
IF v_first_item < v_second_item THEN
return_value := -1;
ELSIF v_first_item = v_second_item THEN
return_value := 0;
ELSIF v_first_item > v_second_item THEN
return_value := 1;
END IF;
ELSIF cmpfnc = '<' THEN
IF v_first_item > v_second_item THEN
return_value := -1;
ELSIF v_first_item = v_second_item THEN
return_value := 0;
ELSIF v_first_item < v_second_item THEN
return_value := 1;
END IF;
-- ELSE -- commented this out for Oracle 8 environment
-- EXECUTE IMMEDIATE 'BEGIN :I := 'cmpfnc'(:A,:B); END;'
-- USING OUT return_value, IN first_item, IN second_item;
END IF;
RETURN return_value;
END cmp;
----------------------------------------------------------------

Links

• Subscribe to this feed