Oracle

The oraproc example is built from the files oraproc.cpp and ildutil.cpp. It creates a PL/SQL package that contains a type definition and a procedure. Then, the procedure is called and the output values of the parameters are printed.

The procedure takes two parameters:

The parameter array size is mandatory since one of the parameters is of type array. Because the first parameter is a non-null scalar integer, its bind call does not need to use the optional arguments for the null indicator, the input/output status, or the actual array size. The user address of the value buffer is given but, because it is a fixed-size value type, the default value for size (-1) is passed: DB Link will take care of the actual size.

For the second parameter, a specific array size is passed as the seventh argument in the bind call. That size is smaller than the maximum. Due to the procedure execution, it is clear that the actual value of the first parameter must be smaller than or equal to the actual array size of the second parameter.

The procedure sets some values for some elements of the array but it also sets an element to the null value, as can be seen when the returned array is printed.

The actual procedure call must be enclosed in an anonymous PL/SQL block.

The values of the parameters are retrieved using the DB Link API but the user memory slots can also be accessed directly. The DB Link API also tests whether the parameter values are null but this can have been checked by directly accessing the value of the user indicators that were bound using the IldRequest::bindCol function.

The first parameter does not require that a user-allocated memory space be bound.

The third argument to the binding of the second parameter is the actual user-side size of one element of the array.

The sixth argument to the binding of the second parameter is ignored by DB Link for Oracle. Its value can therefore be set to IlFalse without any change in the execution behavior.

The oracurs example is built from the files oracurs.cpp and ildutil.cpp. It creates a PL/SQL package that contains two type definitions and a procedure. Then, the procedure is called and the output values of the parameters are printed.

The procedure takes two parameters:

The sample first creates a table in which it inserts some rows before creating the package and calling the procedure. The returned value of the first parameter is then fetched just as with a usual IldRequest object that would have been used to execute a select statement.

This sample cannot be run against an Oracle server whose version is lower than 7.3.

The ora8obj example, built from the files ora8obj.cpp and ildutil.cpp, illustrates how the user-defined data-type features of Oracle are handled. It cannot be run against a server with a version lower than 8. This example is divided into three steps:

1. In the first step, an object type, a collection type, and tables with columns of those types are created. A parameter of IldObjectType type is used. Its value is built using the abstract data type descriptor returned from a call to the member function IldDbms::getAbstractType.

2. In the second step, rows are inserted using parameterized queries. A parameter of IldCollectionType type is used. Its value is also built using an abstract type descriptor.

The notif example, built from the files notif.cpp and ildutil.cpp, demonstrates how the notification mechanism is implemented.

This feature is implemented only for the Oracle81 driver (this is a new feature in this database).

It is delivered with three SQL command files. These files need to be executed using SQL-Plus, for example. They achieve the following requirements:

notifocr.sql for 'Notification objects creation'. This batch is to be executed first to create the queues required by the RDBMS to implement the notification mechanism.

notif.sql. This batch will generate events that will be detected by the DBLink notif sample. It should be executed twice, when the DBLink sample is running.

notifodr.sql for 'Notification objects drop'. This batch is to be executed once the DBLink sample is completed, to clean the queues and various objects created by notifocr.sql.

The notif sample demonstrates the following features:

Subscribe to two different events: 'PUBSUB.INSERT_NOTIF:AGENT', and 'PUBSUB.UPDATE_NOTIF:AGENT'. When the subscription is done, a specific callback function is attached to these two events: 'insertCallBack' and 'updateCallBack'. Each callback function will display a specific message to show that it was called, and the insert callback will count the number of insert events.

The sample will then wait for three insert operations. Note that the application may perform any operation during this time, and it is notified asynchronously when an event occurs. To simplify the sample, a sleep operation is done to wait for the events to be generated.

Then, the sample will unsubscribe the update event. The same notif.sql batch is to be executed a second time to demonstrate that the update event is not received any more.