The Daveno Blog

• Google AdSense
• FeedBurner Advertising Network CPM ads
• Text Link Ads

In Oracle’s DBMS_SCHEDULER, Chains are a real useful feature for managing dependencies in job execution. The following example shows how to set up a basic dependent flow.

-- Privileges required to create chains and rules:
-- CREATE RULE
-- CREATE RULESET
-- CREATE EVAL CONTEXT

CREATE OR REPLACE PROCEDURE sched_test (
                            p_run_code          IN  varchar2
                  )
AS
BEGIN

    logger.log_it(p_process_name => 'sched_test',
                  p_module_name  => p_run_code,
                  p_log_type     => 'INFO',
                  p_log_desc     => 'started');

    dbms_lock.sleep(10);

END sched_test;
/

BEGIN

  DBMS_SCHEDULER.create_program (
    program_name   => 'sched_test_1',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         SCHED_TEST(''1'');
                       END;',
    enabled        => TRUE,
    comments       => 'Program for first link in the chain');

  DBMS_SCHEDULER.create_program (
    program_name   => 'sched_test_2a',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         SCHED_TEST(''2a'');
                       END;',
    enabled        => TRUE,
    comments       => 'Program for second link in the chain');

  DBMS_SCHEDULER.create_program (
    program_name   => 'sched_test_2b',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         SCHED_TEST(''2b'');
                       END;',
    enabled        => TRUE,
    comments       => 'Program for third link in the chain');

END;
/

BEGIN
  DBMS_SCHEDULER.create_chain (
    chain_name          => 'sched_test_chain',
    rule_set_name       => NULL,
    evaluation_interval => NULL,
    comments            => 'A chain to test scheduler chains');
END;
/

BEGIN

  DBMS_SCHEDULER.define_chain_step (
    chain_name   => 'sched_test_chain',
    step_name    => 'step_1',
    program_name => 'sched_test_1');

  DBMS_SCHEDULER.define_chain_step (
    chain_name   => 'sched_test_chain',
    step_name    => 'step_2a',
    program_name => 'sched_test_2a');

  DBMS_SCHEDULER.define_chain_step (
    chain_name   => 'sched_test_chain',
    step_name    => 'step_2b',
    program_name => 'sched_test_2b');

END;
/

BEGIN

  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'sched_test_chain',
    condition  => 'TRUE',
    action     => 'START step_1',
    rule_name  => 'chain_rule_1',
    comments   => 'First link in the chain');

  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'sched_test_chain',
    condition  => 'step_1 SUCCEEDED',
    action     => 'START step_2a',
    rule_name  => 'chain_rule_2a',
    comments   => 'Second link in the chain');

  DBMS_SCHEDULER.define_chain_rule (
    chain_name => 'sched_test_chain',
    condition  => 'step_1 SUCCEEDED',
    action     => 'START step_2b',
    rule_name  => 'chain_rule_2b',
    comments   => 'Third link in the chain');

END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'sched_test_job',
    job_type        => 'CHAIN',
    job_action      => 'sched_test_chain',
    repeat_interval => 'freq=daily',
    start_date      => SYSTIMESTAMP,
    enabled         => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.enable ('sched_test_chain');
END;
/

The Elton John song Harmony is a brilliant bit of songwriting. The song is made much more effective by its use of harmonic transitions. The main verse is in D minor (a very reflective key), which at the end smoothly transitions to D major (a very bright key), then transitions to a chorus in E minor.

There are 5 chords in the main verse (Dm, C, Gm, F(6), and A7), in this pattern: 1/7/4/3/1/5 (dominant 7). The dominant 7 chord (A7) is a key to transitioning from D minor to D major, since they both share the same dominant 7 chord. Once the transition to D major has taken place, a simple 1/4/5 in D rounds out the verse.

Transitioning to the chorus is done from the A chord (5 of D) straight to Em (which key is closely related to D major). The chorus uses the chords Em, A, A7, D, Bm in this pattern: 1/4/7/5/1/4(7)/7 . . . .

This some employs very effective use of key changes and modes to make the song communicate very effectively.

Here is a hint that can be used to transition between different keys in a major key composition. With major keys you can change the color (or feel) of the music emotionally by using key changes. For instance, going from the key of C to the key of A brightens or uplifts the feel a bit, which, combined with the right lyrics and other elements is highly effective.

I hate to use a Smiths song as an example, but I’ve always like “Paint a Vulgar Picture” and the way it moves harmonically. Part of that song’s effectiveness comes from the key changes with alternating verses in C and A, which are keys that are not closely related (they share no common harmonies).

One critical point to making the key changes sound natural is to transition between them smoothly. Here is how it is done in that song. The last harmony of the C major section is the sub-dominant (G), which is the chord closest to the key of A. Then, there is a transition chord (E) which is the sub-dominant of A. Both sections use identical chord changes (1/4/5, 1/4/5, 6/4/5, 6/4/5) but the sections sound completely different and fit the mood of the composition.

By default, materialized views in Oracle are built with “rowdependencies”, which means each row has the hidden column ora_rowscn. This column contains the system change number of the change that committed the row when it was inserted or updated. Using that information, it is possible to inspect the contents of a materialized view to determine what rows were inserted or updated during a period of time. This can be useful when a fast refresh takes an exceptionally long amount of time and you want to find out why.

Here is some SQL that demonstrates how to do it:

SELECT *
  FROM
(
SELECT distinct
       device_id,
       count(distinct(account_id)) over (partition by device_id) apd
  FROM account_rel_evid_mv
 WHERE (case when ora_rowscn > (select min(scn) from sys.smon_scn_time where time_dp > to_date('12/17/2009-11:30','mm/dd/yyyy-hh24:mi'))
             then SCN_TO_TIMESTAMP(ora_rowscn)
             else sysdate end) between to_date('12/17/2009-11:30','mm/dd/yyyy-hh24:mi')
                                   and to_date('12/17/2009-11:55','mm/dd/yyyy-hh24:mi')
)
where apd > 10
and device_id is not null
order by apd desc

It is necessary to use the sys.smon_scn_time data because that contains the SCN-to-timestamp conversions that Oracle knows about. It is a revolving window, so Oracle only knows about SCN-to-timestamp for a recent window of time. Failure to check that can result in an ORA-08181 error.

Using the SAMPLE SQL clause is a handy way to get a random sample from a query. Here is an example:

select pd,
       result_code,
       country_code,
       jlang,
       tzon,
       jbros,
       jbrnm,
       isp,
       isp_organization
from transactions sample(3)
where tran_timestamp >= to_date('11/10/2009','mm/dd/yyyy')
  and tran_timestamp <  to_date('11/12/2009','mm/dd/yyyy')
  and result_code > 0 ;

Here is PL/SQL block for cleanly dropping a table that may or may not exist:

PRAGMA EXCEPTION_INIT(table_not_found, -942);
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE table_name PURGE';
EXCEPTION
    WHEN table_not_found THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
END;

Oracle pipleline functions are an awfully handy way to create a result set (think: view) from data returned by a function. Examples I have used are “calendar” views that show days and hours of the year. The view doesn’t read any tables (other than maybe dual), but loops through logic that returns values that are then exposes via a view.

Another example I did recently was to parse data out of column and create a view to make it look normalized so it could then be joined, etc. to data as if it were truly relational.

This page is a good reference for pipeline functions.


CREATE OR REPLACE TYPE work_item_dependency
AS OBJECT
(
work_item_id number(15,0),
dep_work_item_id number(15,0)
);
CREATE OR REPLACE TYPE work_item_dep_table
AS TABLE OF work_item_dependency ;
CREATE OR REPLACE PACKAGE work_item_dep_pkg
AS
FUNCTION dep_func RETURN work_item_dep_table PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY work_item_dep_pkg AS
FUNCTION dep_func RETURN work_item_dep_table PIPELINED IS
CURSOR dependent_items IS
SELECT work_item_id,
dependencies
FROM work_items
WHERE dependencies IS NOT NULL;
l_dep_work_item_id VARCHAR2(10) := NULL;
l_dep_on BOOLEAN := FALSE;
BEGIN
FOR dependent_rec IN dependent_items
LOOP
l_dep_work_item_id := NULL;
l_dep_on := FALSE;
FOR i in 1 .. length(dependent_rec.dependencies)
LOOP
IF SUBSTR(dependent_rec.dependencies,i,1) = ‘)’ THEN
l_dep_on := FALSE;
IF (TO_NUMBER(l_dep_work_item_id) > 0) THEN
PIPE ROW (work_item_dependency(dependent_rec.work_item_id,
l_dep_work_item_id));
l_dep_work_item_id := NULL;
END IF;
END IF;
IF (l_dep_on) THEN
l_dep_work_item_id := l_dep_work_item_id || SUBSTR(dependent_rec.dependencies,i,1);
END IF;
IF SUBSTR(dependent_rec.dependencies,i,1) = ‘(’ THEN
l_dep_on := TRUE;
END IF;
END LOOP;
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END dep_func;
END;
create or replace view work_item_dependencies
as
SELECT dep.work_item_id,
dep.dep_work_item_id,
wi1.short_desc work_item_title,
wi2.short_desc dep_work_item_title
FROM TABLE(work_item_dep_pkg.dep_func()) dep
JOIN work_items wi1 on dep.work_item_id = wi1.work_item_id
JOIN work_items wi2 on dep.dep_work_item_id = wi2.work_item_id;
select * from work_item_dependencies
order by work_item_id


This article is here.

I found a good reference for HTML special characters.  Example: