REM AlertSend.sql
REM Chapter 12, Oracle9i PL/SQL Programming by Scott Urman
REM This block illustrates sending an alert through DBMS_ALERT.

set serveroutput on

DECLARE
  v_AlertName VARCHAR2(30) := 'MyAlert';
BEGIN
  -- An alert is sent by the SIGNAL procedure.
  DBMS_ALERT.SIGNAL(v_AlertName, 'Alert!  Alert!  Alert!');

  -- It is not actually sent until we commit.
  COMMIT;
END;
/

REM AlertReceive.sql
REM Chapter 12, Oracle9i PL/SQL Programming by Scott Urman
REM This block illustrates receiving an alert using DBMS_ALERT.

set serveroutput on

DECLARE
  v_AlertName VARCHAR2(30) := 'MyAlert';
  v_Message VARCHAR2(100);
  v_Status INTEGER;
BEGIN
  -- In order to receive an alert, we must first register interest
  -- in it.
  DBMS_ALERT.REGISTER(v_AlertName);

  -- Now that we have registered, we can wait.
  DBMS_ALERT.WAITONE(v_AlertName, v_Message, v_Status);

  IF v_Status = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Received: ' || v_Message);
  ELSE
    DBMS_OUTPUT.PUT_LINE('WAITONE timed out');
  END IF;
END;
/

REM simple.sql
REM Chapter 3, Oracle9i PL/SQL Programming by Scott Urman
REM This is an example of a simple loop.

DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    -- Insert a row into temp_table with the current value of the
    -- loop counter.
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Exit condition - when the loop counter > 50 we will
    -- break out of the loop.
    IF v_Counter > 50 THEN
      EXIT;
    END IF;
  END LOOP;
END;
/

Exit to : Oracle Hints and Tips