2 XStream Concepts

This chapter contains concepts related to XStream.

This chapter contains these topics:

XStream Out

XStream Out can capture transactions from the redo log of an Oracle database and send them efficiently to a client application. XStream Out provides a transaction-based interface for streaming these changes to client applications. The client application can interact with other systems, including non-Oracle systems, such as non-Oracle databases or file systems.

XStream Out has both OCI and Java interfaces and supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType.

This section contains these topics:

The Outbound Server

With XStream Out, an Oracle Streams apply process functions as an outbound server. An outbound server is an optional Oracle background process that sends database changes to a client application. Specifically, a client application can attach to an outbound server and extract database changes from LCRs. A client application attaches to the outbound server using the OCI or Java interface.

A client application can create multiple sessions. Each session can attach to only one outbound server, and each outbound server can serve only one session at a time. However, different client application sessions can connect to different outbound servers or inbound servers.

In an XStream Out configuration, a capture process captures database changes and sends these changes to an outbound server. A capture process is an optional Oracle background process that scans the database redo log to capture DML and DDL changes made to database objects. When a capture process is configured to capture changes from the redo log, the database where the changes were generated is called the source database for the capture process.

Figure 2-1 shows a capture process.

Figure 2-1 Capture Process

Description of Figure 2-1 follows
Description of "Figure 2-1 Capture Process"

Change capture can be performed on the same database as the outbound server or on a different database. When change capture is performed on a different database from the one that contains the outbound server, a propagation sends the changes from the change capture database to the outbound server database. Downstream capture is also a supported mode to reduce the load on the source database.

When both the capture process and the outbound server are enabled, data changes, encapsulated in row LCRs and DDL LCRs, are sent to the outbound server. The outbound server can publish LCRs in various formats, such as OCI and Java. The client application can process LCRs that are passed to it from the outbound server or wait for LCRs from the outbound server by using a loop.

An outbound server sends LOB, LONG, LONG RAW, and XMLType data to the client application in chunks. Several chunks comprise a single column value of LOB, LONG, LONG RAW, or XMLType data type.

Figure 2-2 shows an outbound server configuration.

Figure 2-2 XStream Out Outbound Server

Description of Figure 2-2 follows
Description of "Figure 2-2 XStream Out Outbound Server"

The client application can detach from the outbound server whenever necessary. When the client application re-attaches, the outbound server automatically determines where in the stream of LCRs the client application was when it detached. The outbound server starts sending LCRs from this point forward.

See Also:

Oracle Streams Concepts and Administration for detailed information about capture processes

Outbound Servers and Apply Process Features

An Oracle Streams apply process functions as an outbound server, but some apply process features are not applicable to an outbound server. The following sections describe which apply process features are applicable to outbound servers and which are not:

See Also:

Oracle Streams Concepts and Administration for information about apply processes
Apply Process Features That Are Applicable to Outbound Servers

The following apply process features can be used with outbound servers:

  • Rules and rule sets

    See Oracle Streams Concepts and Administration.

  • Rule-based transformations

    When a custom rule-based transformation is specified on a rule used by an outbound server, the user who calls the transformation function is the connect user for the outbound server.

    See Oracle Streams Concepts and Administration.

  • The following apply process parameters:

    • apply_sequence_nextval

    • disable_on_limit

    • grouptransops

    • ignore_transaction

    • max_sga_size

    • maximum_scn

    • startup_seconds

    • time_limit

    • trace_level

    • transaction_limit

    • txn_age_spill_threshold

    • txn_lcr_spill_threshold

    • write_alert_log

    These apply process parameters control the behavior of outbound servers.

    Note:

    Starting with Oracle Database 11g Release 2 (11.2.0.2), the following parameters are available: apply_sequence_nextval, ignore_transaction, grouptransops, and max_sga_size.

    See Oracle Database PL/SQL Packages and Types Reference.

  • Transaction assembly by reader servers

    See Oracle Streams Concepts and Administration.

  • The spilling of unapplied LCRs to hard disk

    See Oracle Streams Concepts and Administration.

  • Instantiation system change number (SCN) settings

    Instantiation SCNs are not required for database objects processed by an outbound server. If an instantiation SCN is set for a database object, then the outbound server only sends the LCRs for the database object with SCN values that are greater than the instantiation SCN value. If a database object does not have an instantiation SCN set, then the outbound server skips the instantiation SCN check and sends all LCRs for that database object. In both cases, the outbound server only sends LCRs that satisfy its rule sets.

    See Oracle Streams Replication Administrator's Guide.

Apply Process Features That Are Not Applicable to Outbound Servers

The following apply process features cannot be used with outbound servers:

  • Apply handlers

    You cannot specify an apply handler for an outbound server. The client application can perform custom processing of the LCRs instead if necessary. However, if apply processes are configured in the same database as the outbound server, then you can specify apply handlers for these apply processes. In addition, you can configure general apply handlers for the database. An outbound server ignores general apply handlers.

    See Oracle Streams Concepts and Administration.

  • The following apply process parameters:

    • allow_duplicate_rows

    • commit_serialization

    • compare_key_only

    • disable_on_error

    • parallelism

    • preserve_encryption

    • rtrim_on_implicit_conversion

    Outbound servers ignore the settings for these apply process parameters.

    The commit_serialization parameter is always set to FULL for an outbound server, and the parallelism parameter is always set to 1 for an outbound server.

    Note:

    Starting with Oracle Database 11g Release 2 (11.2.0.2), the compare_key_only parameter is available.

    See Oracle Database PL/SQL Packages and Types Reference.

  • Apply tags

    An outbound server cannot set an apply tag for the changes it processes.

    See Oracle Streams Replication Administrator's Guide.

  • Apply database links

    Outbound servers cannot use database links.

    See Oracle Streams Replication Administrator's Guide.

  • Conflict detection and resolution

    An outbound server does not detect conflicts, and conflict resolution cannot be set for an outbound server.

    See Oracle Streams Replication Administrator's Guide.

  • Dependency scheduling

    An outbound server does not evaluate dependencies because its parallelism must be 1.

    See Oracle Streams Concepts and Administration.

  • Substitute key column settings

    An outbound server ignores substitute key column settings.

    See Oracle Streams Concepts and Administration.

  • Enqueue directives specified by the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package

    An outbound server cannot enqueue changes into an Oracle database queue automatically using the SET_ENQUEUE_DESTINATION procedure.

    See Oracle Database PL/SQL Packages and Types Reference.

  • Execute directives specified by the SET_EXECUTE procedure in the DBMS_APPLY_ADM package

    An outbound server ignores execute directives.

    See Oracle Database PL/SQL Packages and Types Reference.

  • Error creation and execution

    An outbound server does not create an error transaction when it encounters an error. It records information about errors in the ALL_APPLY and DBA_APPLY views, but it does not enqueue the transaction into the error queue.

    See Oracle Streams Concepts and Administration.

ID Key LCRs

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

XStream Out does not support the following data types in row LCRs:

  • BFILE

  • ROWID

  • User-defined types (including object types, REFs, varrays, and nested tables)

  • XMLType stored object relationally or as binary XML

  • The following Oracle supplied types: Any types, URI types, spatial types, and media types

These data type restrictions pertain to both ordinary (heap-organized) tables and index-organized tables.

ID key LCRs enable an XStream client application to process changes to rows that include unsupported data types. ID key LCRs do not contain all of the columns for a row change. Instead, they contain the rowid of the changed row, a group of key columns to identify the row in the table, and the data for the scalar columns of the table that are supported by XStream Out. ID key LCRs do not contain columns for unsupported data types.

An XStream client application can use ID key LCRs in the following ways:

  • If the application does not require the data in the unsupported columns, then the application can process the values of the supported columns in the ID key LCRs normally.

  • If the application requires the data in the unsupported columns, then the application can use the information in an ID key LCR to query the correct row in the database and consume the unsupported data for the row.

ID Key LCRs Demo

A demo is available that creates a sample client application that process ID key LCRs. Specifically, the client application attaches to an XStream outbound server and waits for LCRs from the outbound server. When the client application receives an ID key LCR, it can query the appropriate source database table using the rowid in the ID key LCR.

The demo is available in the following location in both OCI and Java code:

$ORACLE_HOME/rdbms/demo/xstream/idkey

Sequence LCRs

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

A sequence LCR is a row LCR that includes information about sequence values. Sequence database objects generate sequence values.

You can stream sequence LCRs in the following ways:

  • To capture sequence LCRs using a capture process, set the capture process parameter capture_sequence_nextval to Y.

  • To construct sequence LCRs using the OCI interface, use the OCILCRNew function and the OCILCRHeaderSet function with the OCI_ROWLCR_SEQ_LCR flag.

  • To construct sequence LCRs using the Java interface, use the DefaultRowLCR constructor and setSequenceLCRFlag method.

An apply process or XStream inbound server can use sequence LCRs to ensure that the sequence values at a destination database use the appropriate values. For increasing sequences, the sequence values at the destination are equal to or greater than the sequence values at the source database. For decreasing sequences, the sequence values at the destination are less than or equal to the sequence values at the source database. To instruct an apply process or XStream inbound server to use sequence LCRs, set the apply_sequence_nextval apply process parameter to Y.

Note:

Sequence LCRs are intended for one-way replication configurations. Sequence LCRs cannot be used in bi-directional replication configurations.

See Also:

Considerations for XStream Outbound Servers

The following are considerations for XStream outbound servers:

  • LCRs processed by an outbound server must be LCRs that were captured by a capture process. An outbound server does not support LCRs that were captured by synchronous captures or LCRs that were constructed by applications.

  • A single outbound server can process captured LCRs from only one source database. The source database is the database where the changes encapsulated in the LCRs were generated in the redo log.

  • The source database for the changes captured by a capture process must be at 10.2.0 or higher compatibility level for these changes to be processed by an outbound server.

  • The capture process for an outbound server must be running on an Oracle Database 11g Release 2 (11.2) or later database.

  • A single capture process cannot capture changes for both an outbound server and an apply process. However, a single capture process can capture changes for multiple outbound servers.

  • An outbound server appears as an Oracle Streams apply process in Oracle Enterprise Manager.

  • Automatic split and merge of a stream is possible when the capture process and the outbound server for the stream run on different database instances. However, when the capture process and outbound server for a stream run on the same database instance, automatic split and merge of the stream is not possible. See Oracle Streams Replication Administrator's Guide for information about automatic split and merge.

XStream Out and Distributed Transactions

You can perform distributed transactions using either of the following methods:

  • Modify tables in multiple databases in a coordinated manner using database links.

  • Use the XA interface, as exposed by the DBMS_XA supplied PL/SQL package or by the OCI or JDBC libraries. The XA interface implements X/Open Distributed Transaction Processing (DTP) architecture.

In an XStream Out configuration, changes made to the source database during a distributed transaction using either of the preceding methods are streamed to an XStream outbound server. The outbound server sends the changes in a transaction to the XStream client application after the transaction has committed.

However, the distributed transaction state is not replicated or sent. The client application does not inherit the in-doubt or prepared state of such a transaction. Also, XStream does not replicate or send the changes using the same global transaction identifier used at the source database for XA transactions.

XA transactions can be performed in two ways:

  • Tightly coupled, where different XA branches share locks

  • Loosely coupled, where different XA branches do not share locks

XStream supports replication of changes made by loosely coupled XA branches regardless of the COMPATIBLE initialization parameter value. XStream supports replication of changes made by tightly coupled branches on an Oracle RAC source database only if the COMPATIBLE initialization parameter is set to 11.2.0 or higher.

See Also:

XStream In

XStream In enables a remote client application to send information into an Oracle database from another system, such as a non-Oracle database or a file system. XStream In provides an efficient, transaction-based interface for sending information to an Oracle database from client applications. XStream In can consume the information coming into the Oracle database in several ways, including data replication, auditing, and change data capture. XStream In supports both OCI and Java interfaces.

When compared with OCI client applications that make DML changes to an Oracle database directly, XStream In is more efficient for near real-time, transaction-based, heterogeneous DML changes to Oracle databases.

XStream In uses the following features of Oracle Streams:

  • High performance processing of DML changes using an apply process and, optionally, apply process parallelism

  • Apply process features such as SQL generation, conflict detection and resolution, error handling, and customized processing with apply handlers

  • Streaming network transmission of information with minimal network round-trips

  • Rules, rule sets, and rule-based transformations

    When a custom rule-based transformation is specified on a rule used by an inbound server, the user who calls the transformation function is the apply user for the inbound server.

XStream In supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType. A client application sends LOB and XMLType data to the inbound server in chunks. Several chunks comprise a single column value of LOB, LONG, LONG RAW, or XMLType data type.

This section contains these topics:

The Inbound Server

With XStream In, an Oracle Streams apply process functions as an inbound server. An inbound server is an optional Oracle background process that receives LCRs from a client application. Specifically, a client application can attach to an inbound server and send row changes and DDL changes encapsulated in LCRs.

An external client application connects to the inbound server using the OCI or the Java interface. After the connection is established, the client application acts as the capture agent for the inbound server by streaming LCRs to it.

A client application can create multiple sessions. Each session can attach to only one inbound server, and each inbound server can serve only one session at a time. However, different client application sessions can connect to different inbound servers or outbound servers. A client application can detach from the inbound server whenever necessary.

Figure 2-3 shows an inbound server configuration.

Figure 2-3 XStream In Inbound Server

Description of Figure 2-3 follows
Description of "Figure 2-3 XStream In Inbound Server"

Note:

An inbound server uses a queue that is not shown in Figure 2-3. An inbound server's queue is only used to store error transactions.

Considerations for XStream Inbound Servers

The following are considerations for XStream inbound servers:

  • You can control a DML or DDL trigger's firing property using the SET_TRIGGER_FIRING_PROPERTY procedure in the DBMS_DDL package. This procedure lets you specify whether a trigger always fires, fires once, or fires for apply process changes only. When a trigger is set to fire once, it fires for changes made by a user process, but it does not fire for changes made by an apply process or inbound server. A trigger's firing property works the same for apply processes and inbound servers. See Oracle Streams Concepts and Administration.

  • An inbound server ignores the setting for the ignore_transaction apply process parameter because LCRs sent to the inbound server by the client application might not have transaction ID values.

    Note:

    Starting with Oracle Database 11g Release 2 (11.2.0.2), the ignore_transaction parameter is available for outbound servers and apply processes.
  • An inbound server ignores the setting for the maximum_scn apply process parameter because LCRs sent to the inbound server by the client application might not have SCN values.

  • Currently, an inbound server appears as an Oracle Streams apply process in Oracle Enterprise Manager.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about apply process parameters

Position Order in an LCR Stream

The following sections describe the position order in an LCR stream for both XStream Out and XStream In:

About Position Order

Both XStream Out and XStream In use LCR streams to share transactions. XStream Out sends LCR streams to a client application. XStream In receives LCR streams from a client application.

Each LCR has a position attribute. The position of an LCR identifies its placement in the stream of LCRs in a transaction. Each LCR position has the following properties:

  • The position is unique for each LCR.

  • The position is of RAW data type.

  • The position is strictly increasing within the LCR stream, within a transaction, and across transactions.

  • The position is byte-comparable, and the comparison results for multiple positions determines the ordering of the LCRs in the stream.

  • The position of an LCR remains identical when the database, the client application, or an XStream component restarts.

  • The position is not affected by any rule changes that might reduce or increase the number of LCRs in the stream.

XStream Out only sends committed data, and XStream In only receives committed data.

The following are the properties related to an LCR stream:

  • An LCR stream must be repeatable.

  • An LCR stream must contain a list of assembled, committed transactions. LCRs from one transaction are contiguous. There is no interleaving of transactions in an LCR stream.

  • Each transaction within an LCR stream must have an ordered list of LCRs and a transaction ID.

  • The last LCR in each transaction must be a commit LCR.

  • Each LCR must have a unique position.

  • The position of all LCRs within a single transaction and across transactions must be strictly increasing.

An LCR stream can batch LCRs from multiple transactions and arrange them in increasing position order. LCRs from one transaction are contiguous, and the position must be increasing in the transaction. Also, the position must be nonzero for all LCRs.

Position of LCRs and XStream Out

An XStream Out outbound server streams LCRs that were captured by a capture process to a client application. This section describes concepts related to the LCR positions for an outbound server.

Additional LCR Attributes Related to Position

LCRs that were captured by a capture process contain the following additional attributes related to LCR position:

  • The scn_from_position attribute contains the SCN of the LCR.

  • The commit_scn_from_position attribute contains the commit SCN of the transaction to which the LCR belongs.

Note:

The scn_from_position and commit_scn_from_position attributes are not present in row LCRs captured by a synchronous capture nor in explicitly captured row LCRs.

The Processed Low Position and Restartability for XStream Out

If the outbound server or the client application stops abnormally, then the connection between the two is broken automatically. In this case, the client application must roll back all incomplete transactions.

The processed low position is a position below which all transactions have been processed by the client application. The client application must maintain its processed low position to recover properly after either it or the outbound server (or both) are restarted. The processed low position indicates that the client application has processed all LCRs that are less than or equal to this value. The client application can update the processed low position for each transaction that it consumes.

When the client application attaches to the outbound server, the following conditions related to the processed low position are possible:

  • The client application can pass a processed low position to the outbound server that is equal to or greater than the outbound server's processed low position. In this case, the outbound server resumes streaming LCRs from the first LCR that has a position greater than the client application's processed low position.

  • The client application can pass a processed low position to the outbound server that is less than the outbound server's processed low position. In this case, the outbound server raises an error.

  • The client application can pass NULL to the outbound server. In this case, the outbound server determines the processed low position automatically and starts streaming LCRs from the LCR that has a position greater than this processed low position. When this happens, the client application must suppress or discard each LCR with a position less than or equal to the client application's processed low position.

Streaming Network Transmission

To minimize network latency, the outbound server streams LCRs to the client application with time-based acknowledgments. For example, the outbound server might send an acknowledgment every 30 seconds. This streaming protocol fully utilizes the available network bandwidth, and the performance is unaffected by the presence of a wide area network (WAN) separating the sender and the receiver. The outbound server extends the underlying Oracle Streams infrastructure, and the outbound server maintains the streaming performance rate.

Using OCI, you can control the time period of the interval by setting the OCI_ATTR_XSTREAM_ACK_INTERVAL attribute through the OCI client application. The default is 30 seconds.

Using Java, you can control the time period of the interval by setting the batchInterval parameter in the attach method in the XStreamOut class. The client application can specify this interval when it invokes the attach method.

If the interval is large, then the outbound server can stream out more LCRs for each acknowledgment interval. However, a longer interval delays how often the client application can send the processed low position to the outbound server. Therefore, a longer interval might mean that the processed low position maintained by the outbound server is not current. In this case, when the outbound server restarts, it must start processing LCRs at an earlier position than the one that corresponds to the processed low position maintained by the client application. Therefore, more LCRs might be retransmitted, and the client application must discard the ones that have been applied.

Position of LCRs and XStream In

A client application streams LCRs to an XStream In inbound server. This section describes concepts related to the LCR positions for an inbound server.

Each position must be encoded in a format (such as base-16 encoding) that supports byte comparison. The position is essential to the total order of the transaction stream sent by client applications using the XStream In interface.

The following positions are important for inbound servers:

  • The applied low position indicates that the LCRs less than or equal to this value have been applied.

    An LCR is applied by an inbound server when the LCR has either been executed, sent to an apply handler, or moved to the error queue.

  • The spill position indicates that the LCRs with positions less than or equal to this value have either been applied or spilled from memory to hard disk.

  • The applied high position indicates the highest position of an LCR that has been applied.

    When the commit_serialization apply process parameter is set to DEPENDENT_TRANSACTIONS for an inbound server, an LCR with a higher commit position might be applied before an LCR with a lower commit position. When this happens, the applied high position is different from the applied low position.

  • The processed low position is the higher value of either the applied low position or the spill position.

    The processed low position is the position below which the inbound server no longer requires any LCRs. This position corresponds with the oldest SCN for an Oracle Streams apply process that applies changes captured by a capture process.

    The processed low position indicates that the LCRs with positions less than or equal to this position have been processed by the inbound server. If the client re-attaches to the inbound server, then it must send only LCRs with positions greater than the processed low position because the inbound server discards any LCRs with positions less than or equal to the processed low position.

If the client application stops abnormally, then the connection between the client application and the inbound server is automatically broken. Upon restart, the client application retrieves the processed low position from the inbound server and instructs its capture agent to retrieve changes starting from this processed low position.

To limit the recovery time of a client application using the XStream In interface, the client application can send activity, such as empty transactions, periodically to the inbound server. Row LCRs can include commit transaction control directives. When there are no LCRs to send to the server, the client application can send a row LCR with a commit directive to advance the inbound server's processed low position. This activity acts as an acknowledgment so that the inbound server's processed low position is advanced.

Example 2-1 Advancing the Processed Low Position of an Inbound Server

Consider a client application and an external data source. The client application sends changes made to the hr.employees table to the inbound server for processing, but the external data source includes many other tables, including the oe.orders table.

Assume that the following changes are made to the external data source:

Position Change Client Application Activity
1 Insert into the hr.employees table Send row LCR including the change to the inbound server
2 Insert into the oe.orders table None
3 Commit Send a row LCR with a commit directive to inbound server
4 Insert into the oe.orders table None
5 Update the oe.orders table None
6 Commit None
7 Commit None
... ... (Activity on the external data source, but no changes to the hr.employees table) None
100 Insert into the oe.orders table None
101 Commit None

The client application gets the changes from the external data source, generates appropriate LCRs, and sends the LCRs to the inbound server. Therefore, the inbound server receives the following LCRs:

  • Row LCR for position 1

  • Row LCR for position 3

After position 3, there are no relevant changes to send to the inbound server. If the inbound server restarts when the client application has processed all the changes up to position 101, then, after restarting, the client application must recheck all of the external database changes from position 4 forward. The rechecks are required because the inbound server's processed low position is 3.

Instead, assume that the client application sends commits to the inbound server periodically, even when there are no relevant changes to the hr.employees table:

Position Change Client Application Activity
1 Insert into the hr.employees table Send row LCR including the change to the inbound server
2 Insert into the oe.orders table None
3 Commit Send a row LCR with a commit directive to inbound server
4 Insert into the oe.orders table None
5 Update the oe.orders table None
6 Commit None
7 Commit None
... ... (Activity on the external data source, but no changes to the hr.employees table) Send several row LCRs, each one with a commit directive, to the inbound server
100 Insert into the oe.orders table None
101 Commit Send a row LCR with a commit directive to the inbound server

In this case, the inbound server moves its processed low position to 101 when it has processed all of the row LCRs sent by the client application. If the inbound server restarts, its processed low position is 101, and the client application does not need to check all of the changes back to position 3.

The sample applications in "Sample XStream Client Application" include code that sends a row LCR with a commit directive to an inbound server. These commit directives are sometimes called "ping LCRs." Search for the word "ping" in the sample XStream client applications to find the parts of the applications that include this code.

Summary of Position Use in XStream Out and XStream In

Table 2-1 compares how an XStream Out outbound server and an XStream In inbound server use positions.

Table 2-1 Position Use in the Outbound Server and the Inbound Server

XStream Out Outbound Server XStream In Inbound Server

The outbound server exposes the position.

The client application sets the position.

If the outbound server or client application stops abnormally, then all LCRs above the processed low position are resent. The processed low position is equivalent to an apply process low watermark (LWM), and the apply process obtains the oldest SCN value by using this value.

If the inbound server or client application stops abnormally, then the client application must retransmit all LCRs with a position greater than or equal to the processed low position. The processed low position is equivalent to the apply process low water mark (LWM).


XStream and SQL Generation

SQL generation is the ability to generate the SQL statement required to perform the change encapsulated in a row LCR. Apply processes, XStream outbound servers, and XStream inbound servers can use SQL generation to generate the SQL statement necessary to perform the insert, update, or delete operation in a row LCR.

This section contains these topics:

Interfaces for Performing SQL Generation

You can use the following interfaces to perform SQL generation:

  • The PL/SQL interface, which uses the GET_ROW_TEXT and GET_WHERE_CLAUSE member procedures for row LCRs

  • The OCI for XStream

  • The Java interface for XStream

The PL/SQL interface generates SQL in a CLOB data type, while the OCI and Java interfaces generate SQL in plain text. In the Java interface, the size of the text is limited by the size of String data type.

See Also:

SQL Generation Formats

SQL statements can be generated in one of two formats: inline values or bind variables. Use inline values when the returned SQL statement is relatively small. For larger SQL statements, use bind variables. In this case, the bind variables are passed to the client application in a separate list that includes pointers to both old and new column values.

For information about using bind variables with each interface, refer to the following documentation:

Note:

For generated SQL statements with the values inline, SQL injection is possible. SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database to view or manipulate restricted data. Oracle strongly recommends using bind variables if you plan to execute the generated SQL statement. See Oracle Database PL/SQL Language Reference for more information about SQL injection.

Data Types and Character Sets

Regarding data types and character sets, SQL generation works the same way for XStream Out outbound servers, XStream In inbound servers, and apply processes. For detailed information, see Oracle Streams Concepts and Administration.

SQL Generation Demo

A demo that performs SQL generation is available. The demo uses the DBMS_XSTREAM_ADM PL/SQL package to configure an XStream Out environment, and it uses an OCI client application to perform SQL generation.

The demo uses SQL generation to replicate DML changes from a source database to a destination database. Specifically, the demo creates the xsdemosg schema in both the source database and the destination database. It creates various types of tables in the xsdemosg schema at each database, including tables with LOB columns. It executes a set of DML statements on the tables in xsdemosg schema in the source database. Oracle Streams components, such as a capture process and a queue, send the changes in the form of LCRs to an XStream outbound server that is also running on the source database. The outbound server makes the LCRs available to the demo client application.

The demo client application, when run, uses the OCI API to connect to the outbound server and receive the LCRs. The demo client application uses SQL generation to execute the changes that are encapsulated in the LCRs. Therefore, the client application replicates the changes made to xsdemosg schema in the source database to the xsdemosg in the destination database.

You can modify the demo to replicate changes to any schema. Both the schema and the replicated tables must exist on both the source database and the destination database. SQL generation is only possible for DML changes. Therefore, this demo cannot be used to replicate DDL changes.

This demo is available in the following location:

$ORACLE_HOME/rdbms/demo/xstream/sqlgen

Note:

The SQL generation demo is not available for the XStream Java API.

XStream and Security

XStream Out allows a user to receive LCRs. After an XStream Out user receives LCRs, the user might save the contents of LCRs to a file or generate the SQL statements to execute the LCRs on a non-Oracle database. XStream In allows a user to update tables in its own schema. XStream does not assume that the connected user to the outbound server or inbound server is trusted.

Java and OCI client applications must connect to an Oracle database before attaching to an XStream outbound server created on that database. The connected user must be the same as the connect user configured for the outbound server. Otherwise, an error is raised.

Java and OCI client applications must connect to an Oracle database before attaching to an XStream inbound server created on that database. The connected user must be the same as the apply user configured for the inbound server. Otherwise, an error is raised.

The XStream Java layer API relies on Oracle JDBC security because XStream accepts the Oracle JDBC connection instance created by client applications in the XStream attach API. The connected user is validated as an XStream user.

See Also:

Other Ways to Share Information in a Heterogeneous Environment

Oracle Streams provides other ways to implement heterogeneous information sharing besides XStream, both in past releases and in the current release. These ways include:

  • Replicating data changes to a non-Oracle database using an Oracle Database Gateway

  • Dequeuing messages from an Oracle database using a Java Message Service (JMS) client

  • Enqueuing messages directly into an Oracle database queue with a client application