DBMS ALERT

DBMS_ALERT

The DBMS_ALERT package informs and provides an alert to other users with the support of an interface form in regards to various database events.

The DBMS_ALERT package consists of the following procedures and functions.

Procedures/Fucntions	Description
REGISTER	Registers for an alert
REMOVE_EVENT	Removes a specific alert
REMOVEALL	Removes all the alerts
SET_DEFAULTS	Configures the standby time of an alert
SIGNAL	Delivers signals to alerts
WAITANY	Stands by for all the alerts
WAITONE	Awaits a certain alert

REGISTER

알람을 등록한다.

Syntax

DBMS_ALERT.REGISTER (name);

Parameter

Name	In/Output	Data Type	Description
name	IN	VARCHAR2(30)	The alert name

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_ALERT.REGISTER ('S1');

REMOVE_EVENT

This procedure removes a specific alert. The unregistered alert cannot be able to receive signals.

Syntax

DBMS_ALERT.REMOVE_EVENT( name );

Parameter

Name	In/Output	Data Type	Description
name	IN	VARCHAR2(30)	The alert name

Return Value

None

Exception

There is no exception.

Example

iSQL> EXEC DBMS_ALERT.REMOVE_EVENT ('S1');

REMOVEALL

This procedure removes all the alerts which have been already registered. The unregistered alerts cannot be able to receive signals.

Syntax

DBMS_ALERT.REMOVEALL();

Parameter

None

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

EXEC DBMS_ALERT.REMOVEALL ();

SET_DEFAULTS

This procedure sets the standby time for an alert.

Syntax

DBMS_ALERT.SET_DEFAULTS( poll_interval );

Parameter

Name	In/Output	Data Type	Description
poll_interval	IN	INTEGER	The standby time for an alert (Unit: seconds)

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

EXEC DBMS_ALERT.SET_DEFAULTS (5);

SIGNAL

This procedure sends a message included signal to an alert, and multiple signals can be sent; however, only the registered alerts can receive the signals.

Syntax

DBMS_ALERT.SIGNAL( name, message );

Parameters

Name	In/Output	Data Type	Description
name	IN	VARCHAR2(30)	An alert name
message	IN	VARCHAR2(1800)	Message

Return Value

None

Exception

There is no exception.

Example

EXEC DBMS_ALERT.SIGNAL ('S1', 'MESSAGE 001');

WAITANY

This procedure is called to await signals. Only the registered alerts are able to receive the signals, and the procedure is terminated after a certain time(timeout) has passed in a condition of not being received signals.

Syntax

DBMS_ALERT.WAITANY( name, message, status, timeout );

Parameters

Name	In/Output	Data Type	Description
name	OUT	VARCHAR2(30)	An alert name
message	OUT	VARCHAR2(1800)	Message
status	OUT	INTEGER	Status (Success: 0, Fail: 1)
timeout	IN	INTEGER	The standby time for an alert(Timeout) (Unit: seconds)

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

VAR MESSAGE VARCHAR (1800);
VAR STATUS INTEGER;
EXEC DBMS_ALERT.WAITANY ( :NAME, :MESSAGE, :STATUS, 5 );

WAITONE

This procedure awaits a certain alert, and only the registered alerts can receive signals.

Syntax

DBMS_ALERT.WAITONE( name, message, status, timeout );

Parameters

Name	In/Output	Data Type	Description
name	OUT	VARCHAR2(30)	An alert name
message	OUT	VARCHAR2(1800)	Message
status	OUT	INTEGER	Status (Success: 0, Fail: 1)
timeout	IN	INTEGER	The standby time for an alert(Timeout) (Unit: seconds)

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

VAR NAME VARCHAR (30);
VAR MESSAGE VARCHAR (1800);
VAR STATUS INTEGER;
EXEC :name := 'S1';
EXEC DBMS_ALERT.WAITONE ( :NAME, :MESSAGE, :STATUS, 5 );