René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

December 3, 2006: On parametrizing views with contexts

Suppose I'm the owner of a shop where you can order daily deliveries of certain items.
The orderable items are stored in item:
create table item (
  id_item     number primary key,
  name        varchar2(10)
);
To make things easy for this example, only three items can be ordered:
insert into item values (1, 'Bread');
insert into item values (2, 'Milk' );
insert into item values (3, 'Eggs' );
Of course, the prices of the items might vary over time, so I store the prices in a seperate table, item_price. The columns valid_from and valid_till indicate the time range the price is valid.
create table item_price (
  id_item    not null references item,
  price      number(5,3) not null,
  valid_from date        not null,
  valid_till date        not null
);
I am going to fill item_price_tab. I don't want to use the to_date conversion all the time, so I alter the session to specifiy dates in english and in the 08-Mar-1999 format for March 8th 1999:
alter session set nls_date_format='dd-Mon-yyyy' 
                  nls_language   = english;
Here are the prices:
insert into item_price values (1, 1.89, '01-Jan-1998', '31-Mar-2002'); 
insert into item_price values (1, 1.99, '01-Apr-2002', '31-Dec-9999'); 

insert into item_price values (2, 0.69, '01-Jan-1998', '31-Jan-1999');
insert into item_price values (2, 0.75, '01-Feb-1999', '31-Dec-9999');

insert into item_price values (3, 0.15, '01-Jan-1999', '31-Jul-2004');
insert into item_price values (3, 0.19, '01-Aug-2004', '31-Dec-9999');
The special date '31-Dec-9999' means unknown until when. In a real system, I'd update the record to the current date - 1 when the price changes and insert a new record with the new price, setting valid_from the current date and valid_till to 31-Dec-9999.
Of course, I have clients:
create table client (
  id_client   number primary key,
  name        varchar2(10)
);
Again, to make things simple, I only store two clients:
insert into client values (1, 'J. Bloom');
insert into client values (2, 'S. Johns');
To make delivery, it's imporant to know the clients' addresses. The addresses of the clients might change over time. I want the history, again by using the column pair valid_from and valid_till.
create table client_address (
  id_client               not null references client,
  street     varchar2(15) not null,
  city       varchar2(15) not null,
  valid_from date         not null,
  valid_till date         not null
);
Here are the addresses of the clients:
insert into client_address values (1, '8 Main St'  , 'Blue Springs', '10-Apr-1998', '13-Jun-2003');
insert into client_address values (1, '2 Milky Way', 'Dusty Winds' , '14-Jun-2003', '31-Dec-9999');

insert into client_address values (2, '5 Lion Gate', 'St. George'  , '01-Jan-2000', '31-Aug-2005');
insert into client_address values (2, '3 School Av', 'Herlen'      , '01-Sep-2005', '31-Dec-9999');
Now, the client_order table. It connects clients with items. The pair valid_from / valid_till specify the time range when a certain item must be delivered to a client:
create table client_order (
  id_client         not null references client,
  id_item           not null references item,
  quantity   number not null,
  valid_from date   not null,
  valid_till date   not null
);
Here's what the clients ordered. (Since milk is delivered in units of liters, I can store a fraction):
insert into client_order values (1, 2, 0.5, '10-Apr-1998', '31-Dec-9999') /* Client 1, Milk  */;
insert into client_order values (1, 1, 1  , '28-Aug-2002', '14-Dec-2004') /* Client 1, Bread */;

insert into client_order values (2, 3, 4  , '28-Aug-2002', '17-Oct-2004') /* Client 2, Eggs  */;
insert into client_order values (2, 2, 0.8, '07-May-2003', '08-Jul-2005') /* Client 2, Milk  */;
I want a view that allows me to select all orders for a given day:
create view orders_view as 
  select
    cl.name                client_name,
    ad.street              client_street,
    ad.city                client_city,
    it.name                ordered_item,
    co.quantity            ordered_quantity,
    co.quantity * ip.price total_price,
    ad.valid_from          address_valid_from,
    ad.valid_till          address_valid_till,
    co.valid_from          order_valid_from,
    co.valid_till          order_valid_till,
    ip.valid_from          price_valid_from,
    ip.valid_till          price_valid_till
  from
    client         cl                   join
    client_address ad using (id_client) join
    client_order   co using (id_client) join
    item           it using (id_item  ) join
    item_price     ip using (id_item  );
Now, querying this view for items that were to be delivered on August 28th 2002:
set lines 82


select
  client_name,
  client_street,
  client_city,
  ordered_item,
  ordered_quantity,
  total_price
from
  orders_view
where
  '28-Aug-2002' between address_valid_from and address_valid_till and
  '28-Aug-2002' between   order_valid_from and   order_valid_till and
  '28-Aug-2002' between   price_valid_from and   price_valid_till;
CLIENT_NAM CLIENT_STREET   CLIENT_CITY     ORDERED_IT ORDERED_QUANTITY TOTAL_PRICE
---------- --------------- --------------- ---------- ---------------- -----------
J. Bloom   8 Main St       Blue Springs    Bread                     1        1.99
J. Bloom   8 Main St       Blue Springs    Milk                     .5        .375
S. Johns   5 Lion Gate     St. George      Eggs                      4          .6
This view does not completely satisfy me. I hate repetitive and redundant work and here I have such a biest: I have to specify the date three times in the select statement.
So, I am going to write another view that uses sys_context to query orders_view:
create view orders_ctx_view as
select
  client_name,
  client_street,
  client_city,
  ordered_item,
  ordered_quantity,
  total_price
from
  orders_view
where
  sys_context('orders_ctx', 'date') between address_valid_from and address_valid_till and
  sys_context('orders_ctx', 'date') between   order_valid_from and   order_valid_till and
  sys_context('orders_ctx', 'date') between   price_valid_from and   price_valid_till;
Now, this view will then work when I can set the context pair orders_ctx/date to the desired date.
First, a context namespace (orders_ctx) needs be created:
create context orders_ctx using orders_ctx_pck;
Then I also need the package that allows me to set the value for the attribute date in the orders_ctx namespace:
create package orders_ctx_pck as
  procedure set_date (dt in date);
end;
/
The package's body set the value for the attribute using dbms_session.set_context:
create package body orders_ctx_pck as

    procedure set_date(dt in date) is begin
        dbms_session.set_context('orders_ctx', 'date', dt);
    end set_date;

end orders_ctx_pck;
/
I am now ready to set the value for the attribute date in the orders_ctx context.
exec orders_ctx_pck.set_date('28-Aug-2002');
This time, I can query the orders_ctx_view without even specifying the desired date because the date is set in the context:
select * from orders_ctx_view;
CLIENT_NAM CLIENT_STREET   CLIENT_CITY     ORDERED_IT ORDERED_QUANTITY TOTAL_PRICE
---------- --------------- --------------- ---------- ---------------- -----------
J. Bloom   8 Main St       Blue Springs    Bread                     1        1.99
J. Bloom   8 Main St       Blue Springs    Milk                     .5        .375
S. Johns   5 Lion Gate     St. George      Eggs                      4          .6

Links

I should mention that Oracle's workspace manager is perfectly suitable to store data that is varying over time. I have already written about it in On travelling back in time with Oracle's Workspace Manager.

Thanks

Thanks to Andreas Przygienda who notified me of a type on this page.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: No such file or directory in /home/adpgmbh/public_html/blog/2006/12/03.php on line 521

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/adpgmbh/public_html/blog/2006/12/03.php on line 521