в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:54Z

1 версия

Новая страница

== Call dbms_alert.signal in a loop==



<source lang="sql">
SQL>
SQL> begin
2 for i in 1 .. 10 loop
3 dbms_alert.signal( "MyAlert", "Message " || i );
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL></source>

== Call DBMS_ALERT.SIGNAL in a trigger==



<source lang="sql">
SQL>
SQL> CREATE TABLE s_inventory
2 (product_id NUMBER(7),
3 warehouse_id NUMBER(7),
4 amount_in_stock NUMBER(9),
5 reorder_point NUMBER(9),
6 max_in_stock NUMBER(9),
7 out_of_stock_explanation VARCHAR2(255),
8 restock_date DATE);
Table created.
SQL>
SQL> INSERT INTO s_inventory VALUES (10011, 101, 650, 625, 1100, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (10012, 101, 600, 560, 1000, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (10013, 101, 400, 400, 700, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (10021, 101, 500, 425, 740, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (10022, 101, 300, 200, 350, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (10023, 101, 400, 300, 525, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (20106, 101, 993, 625, 1000, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (20108, 101, 700, 700, 1225, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (20201, 101, 802, 800, 1400, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (20510, 101, 1389, 850, 1400, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (20512, 101, 850, 850, 1450, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (30321, 101, 2000, 1500, 2500, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (30326, 101, 2100, 2000, 3500, NULL, NULL);
1 row created.
SQL> INSERT INTO s_inventory VALUES (30421, 101, 1822, 1800, 3150, NULL, NULL);
1 row created.
SQL>
SQL> CREATE OR REPLACE TRIGGER au_inventory
2 AFTER UPDATE of amount_in_stock
3 ON s_inventory
4 FOR EACH ROW
5 WHEN (new.amount_in_stock < new.reorder_point)
6 BEGIN
7 DBMS_ALERT.SIGNAL("REORDER_THRESHOLD_ALERT", "Warehouse: " ||
8 :new.warehouse_id || " Product: " || :new.product_id ||
9 " Current Stock: " || :new.amount_in_stock ||
10 " Reorder Point: " || :new.reorder_point);
11 END au_inventory;
12 /

SQL>
SQL> show error
SQL>
SQL> drop table s_inventory;
Table dropped.
SQL>
SQL></source>

== DBMS_ALERT.REGISTER==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_register
2 IS
3 BEGIN
4 DBMS_ALERT.REGISTER("ALERTTEST");
5 END;
6 /
SQL>
SQL> drop procedure p_register ;
Procedure dropped.
SQL>
SQL>
SQL></source>

== dbms_alert.waitone==



<source lang="sql">
SQL>
SQL> set serveroutput on
SQL> declare
2 l_status number;
3 l_msg varchar2(1800);
4 begin
5 dbms_alert.waitone( name => "MyAlert",
6 message => l_msg,
7 status => l_status,
8 timeout => dbms_alert.maxwait );
9
10 if ( l_status = 0 )
11 then
12 dbms_output.put_line( "Msg from event is " || l_msg );
13 end if;
14 end;
15 /</source>

== Registers the alert for the session==



<source lang="sql">
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
lv_message_txt VARCHAR2(1800);
lv_status_num PLS_INTEGER;
BEGIN

DBMS_ALERT.REGISTER("REORDER_THRESHOLD_ALERT");
FOR lv_count_num IN 1..10 LOOP
DBMS_ALERT.WAITONE("REORDER_THRESHOLD_ALERT",lv_message_txt, lv_status_num);
IF lv_status_num = 0 THEN
DBMS_OUTPUT.PUT_LINE(lv_message_txt);
ELSE
DBMS_OUTPUT.PUT_LINE("Alert Failed.");
END IF;
END LOOP;
END;
/</source>

== Signal along with the entries in a table==



<source lang="sql">
SQL>
SQL>
SQL> create table job_table
2 ( job_id int primary key,
3 alert_name varchar2(30),
4 message varchar2(2000)
5 )
6 /
Table created.
SQL>
SQL> create or replace procedure background_alert( p_job in int )
2 as
3 l_rec job_table%rowtype;
4 begin
5 select * into l_rec from job_table where job_id = p_job;
6 dbms_alert.signal( l_rec.alert_name, l_rec.message );
7 delete from job_table where job_id = p_job;
8 commit;
9 end;
10 /

SQL>
SQL>
SQL> drop table job_table
2 /
Table dropped.
SQL></source>

== Singal with dbms_alert.signal==



<source lang="sql">
SQL>
SQL> set echo on
SQL>
SQL> exec dbms_alert.signal( "MyAlert", "Hello World" );
PL/SQL procedure successfully completed.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Packages/DBMS ALERT - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия