Oracle | DBMS_NOTIFICATIONS

Oracle DBMS_NOTIFICATIONS Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Should be used by DBAs to set, update, and delete notifications in CDB$ROOT.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Metadata
version	VARCHAR2(16)	'1'
Notification Types
TYPE_MAINTENANCE	VARCHAR2(32)	'Maintenance'
TYPE_CUSTOMER_ACTION	VARCHAR2(32)	'CustomerAction'
MAINTENANCE_NOTIF Types
EXPECTED_START_DATE	VARCHAR2(32)	'expectedStartDate'
EXPECTED_END_DATE	VARCHAR2(32)	'expectedEndDate'
ACTUAL_START_DATE	VARCHAR2(32)	'actualStartDate'
ACTUAL_END_DATE	VARCHAR2(32)	'actualEndDate'
PRODUCT	VARCHAR2(32)	'product'
STATUS	VARCHAR2(32)	'status'
OP_MODE	VARCHAR2(32)	'operationMode'
CUSTOMER_ACTION	VARCHAR2(32)	'customerAction'
DATABASE_IMPACT	VARCHAR2(32)	'databaseImpact'
DESCRIPTION	VARCHAR2(32)	'description'
PATCH_ID	VARCHAR2(32)	'patchID'
DB_UNIQUE_NAME	VARCHAR2(32)	'dbUniqueName'
DBID	VARCHAR2(32)	'DBID'
ADDITIONAL_DATA	VARCHAR2(32)	'additionalData'
Notification Additional Data Sub-Parameters
INSTANCES	VARCHAR2(32)	'instances'
COMPLETED_INSTANCES	VARCHAR2(32)	'completedInstances'
INPROGRESS_INSTANCES	VARCHAR2(32)	'inProgressInstances'
BATCHES	VARCHAR2(32)	'batches'
COMPLETED_BATCHES	VARCHAR2(32)	'completedBatches'
FROM_VERSION	VARCHAR2(32)	'fromVersion'
TO_VERSION	VARCHAR2(32)	'toVersion'
DRAIN_TIME	VARCHAR2(32)	'drainTime'
PDBS	VARCHAR2(32)	'pdbs'
CON_DBID	VARCHAR2(32)	'con_dbid'
Notification JSON Doc
JSON_DOC	VARCHAR2(32)	'JsonDoc'
Notification UTIL Parameter
DATETIME_FORMAT_DEFAULT	VARCHAR2(32)	'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
NOTIF_TYPE	VARCHAR2(32)	'type'
NOTIF_TIME	VARCHAR2(32)	'time'

Dependencies

DBMS_OUTPUT	JSON_KEY_LIST	NOTIFICATIONID
DBMS_SYS_ERROR	JSON_OBJECT_T	NOTIFICATION_SEQ
JSON_ELEMENT_T	NOTIFICATION	PLITBLM

Documented No

First Available 23.1

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role.

Two new Oracle maintained roles, NOTIFICATIONS_ADMIN and NOTIFICATIONS_USER are used for management.
In addition the MAINTPLAN_APP role is likely relevant to notifications

Source {ORACLE_HOME}/rdbms/admin/dbmsnotifications.sql
{ORACLE_HOME}/rdbms/admin/prvtnotifications.plb

catmaintplanv.sql - creates MAINTPLAN_APP role.

Subprograms

DELETE_NOTIF	INSERT_NOTIF	UPDATE_NOTIF
GET_NOTIF	INSERT_NOTIF_IMPL

DELETE_NOTIF (new 23c)

Delete notification based on notification id or notification id with notification as selector dbms_notifications.delete_notif(id IN notificationid); PRAGMA SUPPLEMENTAL_LOG_DATA(delete_notif, AUTO_WITH_COMMIT);

TBD

GET_NOTIF (new 23c)

Return specific notification record based on notification id or notification id with notification selector

Overload 1 dbms_notifications.get_notif( id IN notificationid, notif OUT notification); PRAGMA SUPPLEMENTAL_LOG_DATA(get_notif, READ_ONLY);

TBD

Return the cursor of specific notification record based on notification_id

Overload 2 dbms_notifications.get_notif( id IN notificationid, notifcursor OUT SYS_REFCURSOR); PRAGMA SUPPLEMENTAL_LOG_DATA(get_notif, READ_ONLY);

TBD

INSERT_NOTIF (new 23c)

Insert notification with details into the notification table dbms_notifications.insert_notif( notif IN notification, id OUT notificationid); PRAGMA SUPPLEMENTAL_LOG_DATA(insert_notif, NONE);

TBD

INSERT_NOTIF_IMPL (new 23c)

Implement inserting a notification with notification_id dbms_notifications.insert_notif_impl( notif IN notification, id# IN NUMBER); PRAGMA SUPPLEMENTAL_LOG_DATA(insert_notif_impl, AUTO_WITH_COMMIT);

TBD

UPDATE_NOTIF (new 23c)

Update a notification with detail, notification_id cannot be NULL dbms_notifications.update_notif( id IN notificationid, notif IN notification); PRAGMA SUPPLEMENTAL_LOG_DATA(update_notif, AUTO_WITH_COMMIT);

TBD