Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Dynamic SQL and Trigger Example
Oracle Tips by Burleson
 

To bring together some of the topics outlined above, specifically triggers and dynamic SQL, I will present a way to reduce the maintenance overhead of DML on partitioned tables.  Rather than hard code each partition, I will use dynamic SQL to get the data into the correct partition.

If I look back at chapter 3 in the section on partitioned tables, I defined a partitioned table as:

CREATE TABLE base_table (
  pk_field   NUMBER NOT NULL PRIMARY KEY,
  dt_field   DATE,
  char_field VARCHAR2(100) ); 

CREATE TABLE base_table_part_200601
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060101 AND 20060131 ) )
  INHERITS (base_table); 

CREATE TABLE base_table_part_200602
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060201 AND 20060228 ) )
  INHERITS (base_table); 

CREATE TABLE base_table_part_200603
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060301 AND 20060331 ) )
  INHERITS (base_table);

The trigger I used on this table to insert records in the right partition has a hard coded insert wrapped in an IF statement.  Every time a new partition is added, the trigger must be modified to account for the new partition. 

I will rewrite the trigger to dynamically insert the rows into the correct partition.  One word of caution:  You must ensure that all of your partitions are pre-created or your trigger will fail.  This is not a by product of the dynamic SQL but the dynamic SQL does make it a little bit harder to debug.

The trigger logic is based on the fact that we have a naming standard for our partitions.   The standard is <table_name>_part_<date_YYYYMM>.  So I know that the partition for the table yada for the month of March in 2007 is named: yada_part_200703.  As long as we maintain standards, life becomes much easier for maintenance.

So what was a hard coded trigger now becomes:

CREATE OR REPLACE TRIGGER base_table_handler
  BEFORE INSERT OR UPDATE OR DELETE
  ON base_table
  FOR EACH ROW
BEGIN

  IF INSERTING THEN
    EXECUTE IMMEDIATE
       'INSERT INTO base_table_part_' || to_char(:new.dt_field, 'YYYYMM') ||
       '    (pk_field, dt_field, char_field)' ||
       'VALUES (:new_pk_field, :new_dt_field, :new_char_field)'
    USING :new.pk_field, :new.dt_field, :new.char_field;
  ELSIF UPDATING THEN
    -- Do the same for update
    NULL;

  ELSIF DELETING THEN
     -- Do the same for delete
    NULL;
 END IF;

  RETURN NULL;
END;
/

I can run my original insert statements:

-- Insert a row into the first partition

INSERT INTO base_table

  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060110', 'YYYYMMDD'), 'Record 1');
 

-- Insert a row into the second partition

INSERT INTO base_table

  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060210', 'YYYYMMDD'), 'Record 2');


-- Insert a row into the third partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060310', 'YYYYMMDD'), 'Record 3');

These should be inserted into the correct partitions.  To ensure that is so, I can rerun my queries:

edb=# -- Select from the base table

edb=# SELECT * FROM base_table;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-JAN-06 00:00:00 | Record 1
        1 | 10-FEB-06 00:00:00 | Record 2
        1 | 10-MAR-06 00:00:00 | Record 3

(3 rows)

edb=#
edb=# -- Select from the partition table 200601
edb=# SELECT * FROM base_table_part_200601;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-JAN-06 00:00:00 | Record 1

(1 row)

edb=#

edb=# -- Select from the partition table 200602

edb=# SELECT * FROM base_table_part_200602;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-FEB-06 00:00:00 | Record 2

(1 row)

edb=#

edb=# -- Select from the partition table 200603

edb=# SELECT * FROM base_table_part_200603;

 
pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-MAR-06 00:00:00 | Record 3

(1 row)

Voila!  The magic of triggers and dynamic SQL.

You can access the entire script to create this example from in the code depot at:  edb_chap4_part_tables.sql

* edb_chap4_part_tables.sql

CREATE TABLE base_table (
  pk_field   NUMBER NOT NULL PRIMARY KEY,
  dt_field   DATE,
  char_field VARCHAR2(100) ); 

CREATE TABLE base_table_part_200601
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060101 AND 20060131 ) )
  INHERITS (base_table);

CREATE TABLE base_table_part_200602
 
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060201 AND 20060228 ) )
  INHERITS (base_table);

CREATE TABLE base_table_part_200603
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060301 AND 20060331 ) )
  INHERITS (base_table); 

CREATE OR REPLACE TRIGGER base_table_handler
  BEFORE INSERT OR UPDATE OR DELETE
  ON base_table
  FOR EACH ROW
BEGIN

  IF INSERTING THEN
    EXECUTE IMMEDIATE
       'INSERT INTO base_table_part_' || to_char(:new.dt_field, 'YYYYMM') ||
       '    (pk_field, dt_field, char_field)' ||
       'VALUES (:new_pk_field, :new_dt_field, :new_char_field)'
   USING :new.pk_field, :new.dt_field, :new.char_field;
  ELSIF UPDATING THEN

    -- Do the same for update

    NULL;
  ELSIF DELETING THEN

     -- Do the same for delete

    NULL;
  END IF;
 

  RETURN NULL;
END;
/

-- Insert a row into the first partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060110', 'YYYYMMDD'), 'Record 1');

-- Insert a row into the second partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060210', 'YYYYMMDD'), 'Record 2');
 

-- Insert a row into the third partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060310', 'YYYYMMDD'), 'Record 3');

-- Select from the base table

SELECT * FROM base_table; 

-- Select from the partition table 200601

SELECT * FROM base_table_part_200601;

-- Select from the partition table 200602

SELECT * FROM base_table_part_200602;

-- Select from the partition table 200603

SELECT * FROM base_table_part_200603;

This ends the major topics in SPL.  I know from personal experience that many Oracle applications use XML.  The next section describes the current method of XML manipulation in EnterpriseDB Advanced Server.

        
     

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter