Selecting a Storage Mode

Publication Service provides two storage modes, Publish by Value and Publish by Reference, to copy columns from a source table to a publishing table. The storage mode you select depends on your specific requirements.

Publish by Value

With the Publish by Value storage mode, all specified columns in a source table are copied to the publishing table. Publish by Value makes for a fast copy, but does not support some specific data types, such as Oracle LONG and LONG RAW.

If you use Publish by Value, the following restrictions apply to publishing tables:

Publishing tables cannot contain columns with the LONG data type. If you have a source table that contains a column with the LONG data type, that column cannot be copied to the publishing table. This is because the trigger generated by the palette cannot copy the LONG column value through the :new construct.
This is an Oracle restriction documented in the Oracle SQL Reference manual. The problem is not detected by Oracle during trigger creation. However, when the trigger fires and attempts to copy the LONG column value to the publishing table, the database connection will hang for some time and then eventually terminate.
When you define parent-child relationships between tables, the publishing table that is created for a parent table cannot contain a column with the LONG data type. However, a child table can contain a column with the LONG data type. This is because data in child table rows is not copied using the :new construct.
The LONG RAW data is not allowed in the publishing table.

These restrictions do not apply to publishing tables when you use the Publish by Reference storage mode, and LONG and LONG RAW are non-key types.

An example of Publish by Value is as follows:

A publishing table named P_CUSTOMER is created for the source table, CUSTOMER. When the CUSTOMER table is updated, the new data is copied to the P_CUSTOMER table. The adapter polls the P_CUSTOMER table and publishes the new data.

The following figure shows the Publish by Value storage mode.

Note: If you use the Publish by Value storage mode, loop detection can be enabled. If a subscription exists that uses the same subject and the CUSTOMER table as the destination table, any changes to the CUSTOMER table will not be published repeatedly.

Publish by Reference

With the Publish by Reference storage mode, only key column values are copied to a publishing table. By using this feature, Publication Service can publish data directly from the source table without copying the data from the source table to the publishing table firstly. A trigger, a stored procedure, and a publishing table are created, but the publishing table contains the necessary adapter fields and only the key fields of the source table.

The advantage of Publish by Reference is that the data to be published is stored just once. Also, data types, such as Oracle LONG and LONG RAW are supported for Publish by Reference.

A key column or substitute key column is required when you use Publish by Reference, because the publishing table contains only key values. If no column is specified, the publication is not added.

An example of Publish by Reference is as follows:

A publisher endpoint is configured to publish from the P_CUSTOMER table with the CUST_ID key field. The publishing table is created with the necessary fields and the CUST_ID field. When a row in the P_CUSTOMER table is modified, the trigger fires, populates fields, and copies the CUST_ID field value to the publishing table. When the adapter polls the publishing table, it detects the new row and selects it from the P_CUSTOMER table by using the CUST_ID field value found in the publishing table. Then the message is published.

The following figure shows the Publish by Reference storage mode.

Publish by Reference Object

To use a view or a different database object as the source table, you can configure the adapter to publish data by a reference object, where key columns are stored in the publishing table and data to publish is selected from the reference object.

Similar to the Publish by Reference feature, Publish by Reference Object allows only key values from the source table to be stored in the publishing table.

The difference is that, if Publish by Reference Object is used, when a row changes in the source table and the associated trigger fires, the adapter fetches data from the reference object instead of the source table. The name of the reference object is stored in the ADB_REF_OBJECT column in the publishing table. It is good practice to use Publish by Reference Object when a view provides the most efficient access to source data, for example, when many levels of nesting exist between a parent and a child table.