Database Change notifications (DCN or CQN)

Detailed Description

OcilibCApiAdvancedQueuing

OCILIB supports Oracle 10gR2 feature Database Change Notifications (DCN) also named Continuous Query Notifications (CQN)

This features allows a client application to register notifications when some changes are made in a database :

• Database status changes : startup and shutdown
• Database objects changes :
  • DDL changes : alter or drop actions
  • DML changes : insert, delete, update actions

This feature can be really useful in applications that hold a cache of data. Instead of refreshing data periodically by connecting to the server, the application could only refresh modified data when necessary or perform specific tasks depending on the events. It saves application time, network traffic and can help the design of the application logic.

The database status change notification is also interesting to be informed of instance startup / shutdown

Check Oracle documentation for more details about this feature

Note

No active database connection is required to receive the notifications as they are handled by the Oracle client using a dedicated socket connection to the server

Database changes

The client application can be notified of any database status change (single DB or multiple DB in a RAC environment).

Object changes

The notifications of object changes are based on the registration of a query ('select' SQL statement).

Oracle server will notify of any changes of any object that is part of the statement result set.

Registering a statement will notify about any changes on its result set rows performed after the registration of the query.

The query can be a simple 'select * from table' or a complex query involving many tables and/or criteria in the where clause.

For Object changes, the notification can be at :

• At Object level : only the object name (schema + object) is given
• At row level : same that object level + RowID of the altered row

Warning

Trying to use this features with a client/server version < 10gR2 will raise an error

Example

#include "ocilib.h"
 
#if defined(_WINDOWS)
    #define sleep(x) Sleep(x*1000)
#else
    #include <unistd.h>
#endif
 
#define wait_for_events() sleep(5)
 
void event_handler(OCI_Event *event);
void error_handler(OCI_Error *err);
 
int main(void)
{
    OCI_Connection   *con;
    OCI_Subscription *sub;
    OCI_Statement    *st;
 
    printf("=> Initializing OCILIB in event mode...\n\n");
 
    if (!OCI_Initialize(error_handler, NULL, OCI_ENV_EVENTS))
    {
        return EXIT_FAILURE;
    }
 
    printf("=> Connecting to usr@db...\n\n");
 
    con = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
 
    OCI_SetAutoCommit(con, TRUE);
 
    printf("=> Creating statement...\n\n");
 
    st = OCI_StatementCreate(con);
 
    printf("=> Creating tables...\n\n");
 
    OCI_ExecuteStmt(st, "create table table1(code number)");
    OCI_ExecuteStmt(st, "create table table2(str varchar2(10))");
 
    printf("=> Registering subscription...\n\n");
 
    sub = OCI_SubscriptionRegister(con, "sub-00", OCI_CNT_ALL, event_handler, 5468, 0);
 
    printf("=> Adding queries to be notified...\n\n");
 
    OCI_Prepare(st, "select * from table1");
    OCI_SubscriptionAddStatement(sub, st);
 
    OCI_Prepare(st, "select * from table2");
    OCI_SubscriptionAddStatement(sub, st);
 
    printf("=> Executing some DDL operation...\n\n");
 
    OCI_ExecuteStmt(st, "alter table table1 add price number");
 
    wait_for_events();
 
    printf("=> Executing some DML operation...\n\n");
 
    OCI_ExecuteStmt(st, "insert into table1 values(1, 10.5)");
    OCI_ExecuteStmt(st, "insert into table2 values('shoes')");
 
    OCI_ExecuteStmt(st, "update table1 set price = 13.5 where code = 1");
    OCI_ExecuteStmt(st, "delete from table2 ");
 
    wait_for_events();
 
    printf("=> Droping tables...\n\n");
 
    OCI_ExecuteStmt(st, "drop table table1");
    OCI_ExecuteStmt(st, "drop table table2");
 
    wait_for_events();
 
    printf("=> Unregistering subscription...\n\n");
 
    OCI_SubscriptionUnregister(sub);
 
    printf("=> Disconnecting from DB...\n\n");
 
    OCI_ConnectionFree(con);
 
    printf("=> Cleaning up OCILIB resources...\n\n");
 
    OCI_Cleanup();
 
    printf("=> Done...\n\n");
 
    return EXIT_SUCCESS;
}
 
void error_handler(OCI_Error *err)
{
    int         err_type = OCI_ErrorGetType(err);
    const char *err_msg = OCI_ErrorGetString(err);
 
    printf("** %s - %s\n", err_type == OCI_ERR_WARNING ? "Warning" : "Error", err_msg);
}
 
void event_handler(OCI_Event *event)
{
    unsigned int type = OCI_EventGetType(event);
    unsigned int op = OCI_EventGetOperation(event);
    OCI_Subscription *sub = OCI_EventGetSubscription(event);
 
    printf("** Notification      : %s\n\n", OCI_SubscriptionGetName(sub));
    printf("...... Database      : %s\n", OCI_EventGetDatabase(event));
 
    switch (type)
    {
    case OCI_ENT_STARTUP:
        printf("...... Event         : Startup\n");
        break;
    case OCI_ENT_SHUTDOWN:
        printf("...... Event         : Shutdown\n");
        break;
    case OCI_ENT_SHUTDOWN_ANY:
        printf("...... Event         : Shutdown any\n");
        break;
    case OCI_ENT_DROP_DATABASE:
        printf("...... Event         : drop database\n");
        break;
    case OCI_ENT_DEREGISTER:
        printf("...... Event         : deregister\n");
        break;
    case OCI_ENT_OBJECT_CHANGED:
 
        printf("...... Event         : object changed\n");
        printf("........... Object   : %s\n", OCI_EventGetObject(event));
 
        switch (op)
        {
        case OCI_ONT_INSERT:
            printf("........... Action   : insert\n");
            break;
        case OCI_ONT_UPDATE:
            printf("........... Action   : update\n");
            break;
        case OCI_ONT_DELETE:
            printf("........... Action   : delete\n");
            break;
        case OCI_ONT_ALTER:
            printf("........... Action   : alter\n");
            break;
        case OCI_ONT_DROP:
            printf("........... Action   : drop\n");
            break;
        }
 
        if (op < OCI_ONT_ALTER)
            printf("........... Rowid    : %s\n", OCI_EventGetRowid(event));
 
        break;
    }
 
    printf("\n");
}

Functions
OCI_SYM_PUBLIC OCI_Subscription *OCI_API	OCI_SubscriptionRegister (OCI_Connection *con, const otext *name, unsigned int type, POCI_NOTIFY handler, unsigned int port, unsigned int timeout)
	Register a notification against the given database.
OCI_SYM_PUBLIC boolean OCI_API	OCI_SubscriptionUnregister (OCI_Subscription *sub)
	Unregister a previously registered notification.
OCI_SYM_PUBLIC boolean OCI_API	OCI_SubscriptionAddStatement (OCI_Subscription *sub, OCI_Statement *stmt)
	Add a statement to the notification to monitor.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_SubscriptionGetName (OCI_Subscription *sub)
	Return the name of the given registered subscription.
OCI_SYM_PUBLIC unsigned int OCI_API	OCI_SubscriptionGetPort (OCI_Subscription *sub)
	Return the port used by the notification.
OCI_SYM_PUBLIC unsigned int OCI_API	OCI_SubscriptionGetTimeout (OCI_Subscription *sub)
	Return the timeout of the given registered subscription.
OCI_SYM_PUBLIC OCI_Connection *OCI_API	OCI_SubscriptionGetConnection (OCI_Subscription *sub)
	Return the connection handle associated with a subscription handle.
OCI_SYM_PUBLIC unsigned int OCI_API	OCI_EventGetType (OCI_Event *event)
	Return the type of event reported by a notification.
OCI_SYM_PUBLIC unsigned int OCI_API	OCI_EventGetOperation (OCI_Event *event)
	Return the type of operation reported by a notification.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_EventGetDatabase (OCI_Event *event)
	Return the name of the database that generated the event.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_EventGetObject (OCI_Event *event)
	Return the name of the object that generated the event.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_EventGetRowid (OCI_Event *event)
	Return the rowid of the altered database object row.
OCI_SYM_PUBLIC OCI_Subscription *OCI_API	OCI_EventGetSubscription (OCI_Event *event)
	Return the subscription handle that generated this event.

Function Documentation

OCI_SubscriptionRegister()

OCI_SYM_PUBLIC OCI_Subscription *OCI_API OCI_SubscriptionRegister	(	OCI_Connection *	con,
		const otext *	name,
		unsigned int	type,
		POCI_NOTIFY	handler,
		unsigned int	port,
		unsigned int	timeout
	)

#include <api.h>

Register a notification against the given database.

Parameters

con	- Connection handle
name	- Notification name
type	- Subscription type
handler	- User handler callback
port	- Port to use for notifications
timeout	- notification timeout

Note

Parameter 'type' can be one of the following values :

• OCI_CNT_OBJECTS : request for changes at objects (e.g. tables) level (DDL / DML)
• OCI_CNT_ROWS : request for changes at rows level (DML)
• OCI_CNT_DATABASES : request for changes at database level (startup, shutdown)
• OCI_CNT_ALL : request for all changes

Note

Parameter 'port' and 'timeout' are optional (use value 0 a optional value)

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

@

Warning

Port usage All notifications are using the same port. Port number can be either:

• determined automatically by Oracle client once the first subscription had been created and can be retrieved using OCI_SubscriptionGetPort()
• Set by the parameter 'port' during the first call to OCI_SubscriptionRegister(). In this case later calls can provide same port number or 0

Note

Requires Oracle Client 10gR2 or above

Subscription handles are automatically managed by the library

Returns

Subscription handle on success or NULL on failure

Referenced by ocilib::Subscription::Register().

OCI_SubscriptionUnregister()

OCI_SYM_PUBLIC boolean OCI_API OCI_SubscriptionUnregister

(

OCI_Subscription *

sub

)

#include <api.h>

Unregister a previously registered notification.

Parameters

sub	- Subscription handle

Note

OCI_Cleanup() will automatically unregister any registered subscriptions

If the database connection passed to OCI_SubscriptionRegister() has been closed by the time that the application calls OCI_SubscriptionUnregister, the library internally reconnects to the given database, performs the unregistration and then disconnects

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Returns

TRUE on success otherwise FALSE

OCI_SubscriptionAddStatement()

OCI_SYM_PUBLIC boolean OCI_API OCI_SubscriptionAddStatement	(	OCI_Subscription *	sub,
		OCI_Statement *	stmt
	)

#include <api.h>

Add a statement to the notification to monitor.

Parameters

sub	- Subscription handle
stmt	- Statement handle

Note

The given statement must be prepared but not executed before being passed to this function. OCI_SubscriptionAddStatement() executes the statement and register it for notifications

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

The given statement must hold a 'SELECT' SQL statement

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Subscription::Watch().

OCI_SubscriptionGetName()

OCI_SYM_PUBLIC const otext *OCI_API OCI_SubscriptionGetName

(

OCI_Subscription *

sub

)

#include <api.h>

Return the name of the given registered subscription.

Parameters

sub	- Subscription handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Referenced by ocilib::Subscription::GetName().

OCI_SubscriptionGetPort()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_SubscriptionGetPort

(

OCI_Subscription *

sub

)

#include <api.h>

Return the port used by the notification.

Parameters

sub	- Subscription handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Referenced by ocilib::Subscription::GetPort().

OCI_SubscriptionGetTimeout()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_SubscriptionGetTimeout

(

OCI_Subscription *

sub

)

#include <api.h>

Return the timeout of the given registered subscription.

Parameters

sub	- Subscription handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Referenced by ocilib::Subscription::GetTimeout().

OCI_SubscriptionGetConnection()

OCI_SYM_PUBLIC OCI_Connection *OCI_API OCI_SubscriptionGetConnection

(

OCI_Subscription *

sub

)

#include <api.h>

Return the connection handle associated with a subscription handle.

Parameters

sub	- Subscription handle

Note

It may return a NULL handle if the connection used in OCI_SubscriptionRegister has been closed.

Referenced by ocilib::Subscription::GetConnection().

OCI_EventGetType()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_EventGetType

(

OCI_Event *

event

)

#include <api.h>

Return the type of event reported by a notification.

Parameters

event

- Event handle

Note

The returned value can be one of the following values :

• OCI_ENT_STARTUP : a database has been started up
• OCI_ENT_SHUTDOWN : a database has been shut down
• OCI_ENT_SHUTDOWN_ANY : a database has been shut down (RAC)
• OCI_ENT_DROP_DATABASE : a database has been dropped
• OCI_ENT_DEREGISTER : the notification is timed out
• OCI_ENT_OBJECT_CHANGED : a database object has been modified

Note

OCI_EventGetDatabase() returns the affected database

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

OCI_EventGetObject() returns the affected object ('schema_name'.'object_name')

Referenced by ocilib::Event::GetType().

OCI_EventGetOperation()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_EventGetOperation

(

OCI_Event *

event

)

#include <api.h>

Return the type of operation reported by a notification.

Parameters

event

- Event handle

Note

This call is only valid when OCI_EventGetType() reports the event type OCI_ENT_OBJECT_CHANGED

The returned value can be one of the following values :

• OCI_ONT_INSERT : an insert has been performed
• OCI_ONT_UPDATE : an update has been performed
• OCI_ONT_DELETE : a delete has been performed
• OCI_ONT_ALTER : an alter has been performed
• OCI_ONT_DROP : a drop has been performed
• OCI_ONT_GENERIC : generic undefined action

Note

OCI_EventGetDatabase() returns the affected database

OCI_EventGetObject() returns the affected object ('schema_name'.'object_name')

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

if OCI_CNT_ROWS is passed to OCI_SubscriptionRegister(), the rowid of the altered row can be retrieved with OCI_EventGetRowid()

Referenced by ocilib::Event::GetObjectEvent().

OCI_EventGetDatabase()

OCI_SYM_PUBLIC const otext *OCI_API OCI_EventGetDatabase

(

OCI_Event *

event

)

#include <api.h>

Return the name of the database that generated the event.

Parameters

event

- Event handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Warning

When using Oracle pluggable databases (PDBs), OCI_EventGetDatabase() returns the container database name and NOT the pluggable database name (Oracle limitation).

Referenced by ocilib::Event::GetDatabaseName().

OCI_EventGetObject()

OCI_SYM_PUBLIC const otext *OCI_API OCI_EventGetObject

(

OCI_Event *

event

)

#include <api.h>

Return the name of the object that generated the event.

Parameters

event

- Event handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Referenced by ocilib::Event::GetObjectName().

OCI_EventGetRowid()

OCI_SYM_PUBLIC const otext *OCI_API OCI_EventGetRowid

(

OCI_Event *

event

)

#include <api.h>

Return the rowid of the altered database object row.

Parameters

event

- Event handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Referenced by ocilib::Event::GetRowID().

OCI_EventGetSubscription()

OCI_SYM_PUBLIC OCI_Subscription *OCI_API OCI_EventGetSubscription

(

OCI_Event *

event

)

#include <api.h>

Return the subscription handle that generated this event.

Parameters

event

- Event handle

Note

OCI_ENV_EVENTS flag must be passed to OCI_Initialize() to be able to use subscriptions

Referenced by ocilib::Event::GetSubscription().