Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

The previous section used the test_calendar_string.sql procedure to display the run schedule expected for a specific calendar string.  Before any comparisons between the possible scheduling methods can be done, a way to test the PL/SQL expressions that are used to schedule jobs using dates and timestamps is needed.  The test_date_string procedure listed below is similar to the test_calendar_string procedure, but it displays run dates defined by interval strings that might be used when scheduling jobs via the dbms_job package.

            test_date_string.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set serveroutput on;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
 
CREATE OR REPLACE PROCEDURE test_date_string(
  p_interval    IN  VARCHAR2,
  p_iterations  IN  NUMBER DEFAULT 5)
AS
  l_interval           VARCHAR2(1000) := p_interval;
  l_start_date         DATE := TO_DATE('01-JAN-2004 03:04:32',
                                       'DD-MON-YYYY HH24:MI:SS');

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA