Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 0
Jul 142012
 
Od Oracle 9i byla k dispozici funkcinalita Continuous Query Notification (CQN) a to ve formě Object Change Notification (OCN), tedy možnost navěsit se na sledování DDL změn objektu a odbírat tyto změny. Hodně by se dala tato funkce nahradit klasickým triggerem – je to zajímavé, ale podstatně zajímavější je druhá část CQN, která je dostupná až od Oracle 10g a to je Query Result Change Notification (QRCN), tedy možnost registrovat si select a navěsit se na změny dat pod tímto selectem, což by se v některých případech řešilo velice obtížně triggerama – můžeme například projoinovat několik tabulek a odbírat změny dat ve výsledku tohoto selectu. Dobrý, ne?

Důležitá poznámka k funkcinalitě QRCN – selecty lze registrovat ve dvou módech – jeden je garantovaný, kdy je notifikace vygenerována vždy, když se změní data a best-effort, kdy může být vygenerován i  falešná notfikace, což se například může udít pokud se změní data, avšak pro výsledek selectu to nemá efekt žádný (například sum(salary) a odečtení a zároven přičtení stejné částky v jedné transakci). Bohužel ne všechny selecty lze registrovat v obou módech. Vlastně je to pro best-effort hodně omezené, bohužel.

Další důležitá věc, mohou nastat události a okolnosti, které automaticky select z QRCN deregistrují k čemuž může dojít z několika důvodů – ALTER TABLE, TIMEOUT nebo ztráta opravnění. Deregistrace však vyvolá také notifikaci, tentokrát o deregistraci.

Notifikace od QRCN obahuje následující: Jméno změněné tabulky, typ (INSERT, DELETE, UPDATE..) a ROWIDs změnených dat (pokud se vejde). Mimo DML i QRCN odesílá notifikaci na ALTER TABLE, TRUNCATE TABLE, DROP TABLE a FLASHBACK TABLE.

Takže hurá na první test s QRCN:

1) Nejprve založení logovací tabulky

create table tbl_log(
id number primary key,
log_text varchar2(4000)
);

create sequence seq_log_id;

Tabulka, kam bude notifikační handler vkládat informace o přijaté notifikaci. Tabulka je pro test nejednodušší a nejrychlejší, ale v praxi by to mohlo například posílat email. A sekvence pro generování ID.

2) Procedura handleru pro přijetí notifikace

CREATE OR REPLACE PROCEDURE prc_qn_handler(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)
IS
log_text varchar2(4000);
BEGIN
log_text:='Registration ID='||ntfnds.registration_id;
log_text:=log_text || ', Transaction ID='||ntfnds.transaction_id;
log_text:=log_text || ', Dnbame='||ntfnds.dbname;
INSERT INTO tbl_log VALUES(seq_log_id.nextval,log_text);
commit;
END;

Procedura je spuštěna pokaždé, když background job Oracle pošle na základě události (změna dat, ALTER TABLE ..) notifikaci. Procedura musí mít jako jediný paramatr CQ_NOTIFICATION$_DESCRIPTOR, tedy notifikaci. A je třeba jí zaregistrovat, viz dále. Tělo je jen na ukázku – nic světoborného, vybere data z přijmutého parametru a insertne je do našeho logu TBL_LOG. Přijatá notifikace typu CQ_NOTIFICATION$_DESCRIPTOR obsahuje více informací než je v mém handleru, například ROWID změněných řádků či tabulku

2) Testovací tabulky s datama

create table tbl_orders
(
id number primary key,
order_type varchar2(30),
ordered date default sysdate
);
create table tbl_orders_items
(
id number primary key,
order_id number,
order_item varchar2(30),
CONSTRAINT fk_for_key FOREIGN KEY (order_id) REFERENCING tbl_orders(id)
);
insert into tbl_orders values (1,'order_1',sysdate);
insert into tbl_orders_items values (1,1,'order_1_item_1');
insert into tbl_orders_items values (2,1,'order_1_item_2');
insert into tbl_orders_items values (3,1,'order_1_item_3');
insert into tbl_orders values (2,'order_2',sysdate);
insert into tbl_orders_items values (4,2,'order_2_item_1');
insert into tbl_orders_items values (5,2,'order_2_item_2');
insert into tbl_orders values (3,'order_3',sysdate);
insert into tbl_orders values (4,'order_4',sysdate);
insert into tbl_orders_items values (6,4,'order_4_item_1');
commit;

K tomu asi není co dodat, čistě tabulky a data nad kterými pak bude zaregistrovaný select pro QRCN. A pro představu jak data vypadají:

Data pro QRCN

Data pro QRCN

3) Select, který zaregistrujeme pro QRCN

select ord.id,order_item from tbl_orders ord,tbl_orders_items items where ord.id=items.order_id and ord.id=1;

Select, který budeme registrovat pro query

Select, který budeme registrovat pro query

4) Zaregistrování query jako QRCN

Máme hotový log, připravená data, připravený select i handler a tedy zbývá poslední krok k tomu, aby to celé fungovalo – zaregistrování selectu jako QRCN.

declare
v_cn_addr CQ_NOTIFICATION$_REG_INFO;
id_number number;
cur_reg sys_refcursor;
begin
v_cn_addr:=CQ_NOTIFICATION$_REG_INFO('azor.prc_qn_handler',
dbms_change_notification.QOS_ROWIDS+dbms_change_notification.QOS_BEST_EFFORT,
0);
id_number:=DBMS_CQ_NOTIFICATION.NEW_REG_START(v_cn_addr);
open cur_reg for
select id,order_item,dbms_cq_notification.CQ_NOTIFICATION_QUERYID from tbl_orders ord,tbl_orders_items items
where ord.id=items.order_id and ord.id=1;
close cur_reg;
DBMS_CQ_NOTIFICATION.REG_END;
dbms_output.put_line('DBMS_CQ'||id_number);
end;

Kroky jsou následující – vytvořit objekt CQ_NOTIFICATION$_REG_INFO, kterému předat jako parametr handler funkci (v mém případě prc_qn_handler), po té zavolat DBMS_CQ_NOTIFICATION.NEW_REG_START, které se předá vytvožený objekt (v mém případě v_cn_addr) a po té následujie otvirání kurzorů, jejihž selecty chceme registrovat do daného QRCN a po té zavolání DBMS_CQ_NOTIFICATION.REG_END.

5) Update dat

Fajn, všehno je hotovo a nyní by to chtělo vyzkoušet, tedy dva různé updaty:

update tbl_orders_items set order_item='updated' where order_id=1;
commit;
update tbl_orders_items set order_item='updated2' where order_id=2;
commit;

6) Select do logu

Select do logu notifikaci

Super, ne? Updatem číslo 1, který byl s QRCN registrovaným pod ID =3 byla vyvolána notfikace, ta předána mému handleru prc_qn_handler jež zapsal do logu TBL_LOG.
Na škole nám vždycky vloukali do hlavy větu “A k čemu je to dobré?” (ačkoliv většina toho, co nás učili nebyla nikdy k ničemu dobrá). Já myslim, že si použití najde každý sám a ačkoliv se to dá použít i pro reporting/monitoring systému, tak primárně Oracle míří na takové featury jako je nacachování dat na aplikační server a registorvání QRCN, které v případě změny odešle aplikačnímu serveru notifikaci a invaliduje cache/donutí k opětovénému fatche dat.


 Posted by at 11:38

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>