Summary of DBMS_STREAMS_ADM Subprograms

Table 73-1 DBMS_STREAMS_ADM Subprograms

Subprogram	Description
"ADD_GLOBAL_PROPAGATION_RULES Procedure"	Adds propagation rules that propagate all the LCRs in a source queue to a destination queue
"ADD_GLOBAL_RULES Procedure"	Adds capture rules for an entire database or apply rules for all LCRs in a queue
"ADD_SCHEMA_PROPAGATION_RULES Procedure"	Adds propagation rules that propagate the LCRs related to the specified schema in a source queue to a destination queue
"ADD_SCHEMA_RULES Procedure"	Adds capture or apply rules for a schema
"ADD_SUBSET_RULES Procedure"	Adds apply rules for a subset of the rows in a table
"ADD_TABLE_PROPAGATION_RULES Procedure"	Adds propagation rules that propagate the LCRs related to the specified table in a source queue to a destination queue
"ADD_TABLE_RULES Procedure"	Adds capture or apply rules for a table
"PURGE_SOURCE_CATALOG Procedure"	Removes all Streams data dictionary information at the local database for the specified object
"REMOVE_RULE Procedure"	Removes the specified rule or all rules from the rule set associated with the specified capture process, apply process, or propagation job
"SET_UP_QUEUE Procedure"	Creates a queue table and a queue for use with the capture, propagate, and apply functionality of Streams

ADD_GLOBAL_PROPAGATION_RULES Procedure

Adds propagation rules that propagate all the LCRs in a source queue to a destination queue. This procedure also configures propagation using the current user, if necessary, and establishes a default propagation schedule. This procedure enables propagation of all LCRs in the source queue, subject to filtering conditions, to the destination queue. Only one propagation job is allowed between the source queue and destination queue.

If propagation rules are added, then the propagation job propagates DML changes, or DDL changes, or both from the specified source queue to the specified destination queue. This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is the database name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the database name plus the sequence number is too long, then the database name is truncated. For the overloaded ADD_GLOBAL_PROPAGATION_RULES procedure, the system-generated rule names for DML and DDL changes are returned.

A propagation job uses the rules created for filtering. If the propagation job does not have a rule set, then a rule set is created automatically, and the rules for propagating changes to the database are added to the rule set. Other rules in an existing rule set for the propagation job are not affected. You can add additional rules using the DBMS_RULE_ADM package.

The following is an example of a global rule condition that may be created for propagating DML changes with a propagation job:

:dml.get_source_database_name() = 'DBS1.NET' AND :dml.is_null_tag() = 'Y'

For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

Syntax

DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
   streams_name             IN  VARCHAR2,
   source_queue_name        IN  VARCHAR2,
   destination_queue_name   IN  VARCHAR2,
   include_dml              IN  BOOLEAN   DEFAULT true,
   include_ddl              IN  BOOLEAN   DEFAULT false,
   include_tagged_lcr       IN  BOOLEAN   DEFAULT false,
   source_database          IN  VARCHAR2  DEFAULT NULL,
   dml_rule_name            OUT VARCHAR2,
   ddl_rule_name            OUT VARCHAR2);

Parameters

Table 73-2 ADD_GLOBAL_PROPAGATION_RULES Procedure Parameters  

Parameter	Description
streams_name	The name of the propagation job. If the specified propagation job does not exist, then it is created automatically. If NULL and a propagation job exists for the same source queue and destination queue (including database link), then this propagation job is used. If NULL and no propagation job exists for the same source queue and destination queue (including database link), then a propagation job is created automatically with a system-generated name.
source_queue_name	The name of the source queue. The current database must contain the source queue.
destination_queue_name	The name of the destination queue, including any database link, such as STREAMS_QUEUE@DBS2. If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed.
include_dml	If TRUE, then creates a rule for DML changes. If FALSE, then does not create a DML rule. NULL is not permitted.
include_ddl	If TRUE, then creates a rule for DDL changes. If FALSE, then does not create a DDL rule. NULL is not permitted.
include_tagged_lcr	If TRUE, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database. If FALSE, then an LCR is considered for propagation only when the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. The source database is where the changes originated. If NULL, then no condition regarding the source database is added to the generated rules. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically. Oracle Corporation recommends that you specify a source database for propagation rules.
dml_rule_name	If include_dml is TRUE, then contains the DML rule name. If include_dml is FALSE, then contains a NULL.
ddl_rule_name	If include_ddl is TRUE, then contains the DDL rule name. If include_ddl is FALSE, then contains a NULL.

ADD_GLOBAL_RULES Procedure

Adds capture rules for an entire database or apply rules for all LCRs in a queue.

If capture rules are added, then captures DML changes, or DDL changes, or both in the current database and enqueues these changes into the specified queue. For capture rules, you should execute this procedure at the source database. This procedure automatically invokes the PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package.

If apply rules are added, then the apply process receives and applies captured events that contain DML changes, or DDL changes, or both that originated at the source database matching the source_database parameter. For apply rules, you should execute this procedure at the destination database.

An apply process created by this procedure can apply events only at the local database and can apply only captured events. To create an apply process that applies events at a remote database or an apply process that applies user-enqueued events, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

Changes applied by an apply process created by this procedure generate tags in the redo log at the destination database with a value of '00' (double zero). You can use the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to alter the tag value after the apply process is created, if necessary.

You have the option of creating an apply process using the DBMS_APPLY_ADM.CREATE_APPLY procedure and specifying nondefault values for the apply_captured, apply_database_link, and apply_tag parameters when you run that procedure. Then you can use this ADD_GLOBAL_RULES procedure to add rules to the rule set used by the apply process.

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is the database name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the database name plus the sequence number is too long, then the database name is truncated.

For the overloaded ADD_GLOBAL_RULES procedure, the system-generated rule names for DML and DDL changes are returned.

A capture process or an apply process uses the rules created for filtering. If the generated process does not have a rule set, then a rule set is created automatically, and the rules are added to the rule set. Other rules in an existing rule set for the process are not affected. You can add additional rules using the DBMS_RULE_ADM package.

The following is an example of a global rule condition that may be created for capturing DML changes with a capture process:

:dml.is_null_tag() = 'Y'

The following is an example of a global rule condition that may be created for applying DML changes with an apply process:

:dml.get_source_database_name() = 'DBS1.NET' AND :dml.is_null_tag() = 'Y'

If this procedure creates a capture process or an apply process, then the user who runs this procedure is the user who captures or applies changes. The specified user must have the necessary privileges to perform these actions.

Syntax

DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
   streams_type             IN  VARCHAR2,
   streams_name             IN  VARCHAR2 DEFAULT NULL,
   queue_name               IN  VARCHAR2 DEFAULT 'streams_queue',
   include_dml              IN  BOOLEAN  DEFAULT true,
   include_ddl              IN  BOOLEAN  DEFAULT false,
   include_tagged_lcr       IN  BOOLEAN  DEFAULT false,
   source_database          IN  VARCHAR2 DEFAULT NULL,
   dml_rule_name            OUT VARCHAR2,
   ddl_rule_name            OUT VARCHAR2);

Parameters

Table 73-3 ADD_GLOBAL_RULES Procedure Parameters (Page 1 of 2)

Parameter	Description
streams_type	The type of process, either capture or apply
streams_name	The name of the capture or apply process. If the specified process does not exist, then it is created automatically. If NULL and one relevant capture or apply process for the queue exists, then the relevant process is used. If no relevant process exists for the queue, then a capture process or an apply process is created automatically with a system-generated name. If NULL and multiple processes of the specified streams_type for the queue exist, then an error is raised.
queue_name	The name of the local queue. For capture rules, the queue into which the changes will be enqueued. For apply rules, the queue from which changes will be dequeued.
include_dml	If TRUE, then creates a rule for DML changes. If FALSE, then does not create a DML rule. NULL is not permitted.
include_ddl	If TRUE, then creates a rule for DDL changes. If FALSE, then does not create a DDL rule. NULL is not permitted.
include_tagged_lcr	If TRUE, then a redo entry is always considered for capture and an LCR is always considered for apply, regardless of whether the redo entry or LCR has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database. If FALSE, then a redo entry is considered for capture and an LCR is considered for apply only when the redo entry or the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules. For capture rules, you can specify NULL, because currently the capture database must be the same as the source database. For apply rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.
dml_rule_name	If include_dml is TRUE, then contains the DML rule name. If include_dml is FALSE, then contains a NULL.
ddl_rule_name	If include_ddl is TRUE, then contains the DDL rule name. If include_ddl is FALSE, then contains a NULL.

ADD_SCHEMA_PROPAGATION_RULES Procedure

Adds propagation rules that propagate the LCRs related to the specified schema in a source queue to a destination queue. This procedure also configures propagation using the current user, if necessary, and establishes a default propagation schedule. This procedure enables propagation of LCRs for the specified schema, subject to filtering conditions. Only one propagation job is allowed between the source queue and the destination queue.

If propagation rules are added, then the propagation job propagates DML changes, or DDL changes, or both that are related to the specified schema from the specified source queue to the specified destination queue. This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is the schema name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the schema name plus the sequence number is too long, then the schema name is truncated. For the overloaded ADD_SCHEMA_PROPAGATION_RULES procedure, the system-generated rule names for DML and DDL changes are returned.

A propagation job uses the rules created for filtering. If the propagation job does not have a rule set, then a rule set is created automatically, and the rules for propagating changes to the schema are added to the rule set. Other rules in an existing rule set for the propagation job are not affected. Additional rules can be added using the DBMS_RULE_ADM package.

The following is an example of a schema rule condition that may be created for propagating DML changes with a propagation job:

:dml.get_object_owner() = 'HR' AND :dml.is_null_tag() = 'Y'
AND :dml.get_source_database_name() = 'DBS1.NET'

For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

Syntax

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
   schema_name              IN  VARCHAR2,
   streams_name             IN  VARCHAR2,
   source_queue_name        IN  VARCHAR2,
   destination_queue_name   IN  VARCHAR2,
   include_dml              IN  BOOLEAN  DEFAULT true,
   include_ddl              IN  BOOLEAN  DEFAULT false,
   include_tagged_lcr       IN  BOOLEAN  DEFAULT false,
   source_database          IN  VARCHAR2 DEFAULT NULL,
   dml_rule_name            OUT VARCHAR2,
   ddl_rule_name            OUT VARCHAR2);

Parameters

Table 73-4 ADD_SCHEMA_PROPAGATION_RULES Procedure Parameters  

Parameter	Description
schema_name	The name of the schema. For example, hr.
streams_name	The name of the propagation job. If the specified propagation job does not exist, then it is created automatically. If NULL and a propagation job exists for the same source queue and destination queue (including database link), then this propagation job is used. If NULL and no propagation job exists for the same source queue and destination queue (including database link), then a propagation job is created automatically with a system-generated name.
source_queue_name	The name of the source queue. The current database must contain the source queue.
destination_queue_name	The name of the destination queue, including database link, for example STREAMS_QUEUE@DBS2. If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed.
include_dml	If TRUE, then creates a rule for DML changes. If FALSE, then does not create a DML rule. NULL is not permitted.
include_ddl	If TRUE, then creates a rule for DDL changes. If FALSE, then does not create a DDL rule. NULL is not permitted.
include_tagged_lcr	If TRUE, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database. If FALSE, then an LCR is considered for propagation only when the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. The source database is where the change originated. If NULL, then no condition regarding the source database is added to the generated rules. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically. Oracle Corporation recommends that you specify a source database for propagation rules.
dml_rule_name	If include_dml is TRUE, then contains the DML rule name. If include_dml is FALSE, then contains a NULL.
ddl_rule_name	If include_ddl is TRUE, then contains the DDL rule name. If include_ddl is FALSE, then contains a NULL.

ADD_SCHEMA_RULES Procedure

Adds capture or apply rules for a schema.

If capture rules are added, then the capture process captures DML changes, or DDL changes, or both in the specified schema and enqueues these changes into the specified queue. For capture rules, you should execute this procedure at the source database. This procedure automatically invokes the PREPARE_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified schema.

If apply rules are added, then the apply process receives and applies captured events that contain DML changes, or DDL changes, or both for the specified schema. For apply rules, you should execute this procedure at the destination database.

An apply process created by this procedure can apply events only at the local database and can apply only captured events. To create an apply process that applies events at a remote database or an apply process that applies user-enqueued events, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

Changes applied by an apply process created by this procedure generate tags in the redo log at the destination database with a value of '00' (double zero). You can use the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to alter the tag value after the apply process is created, if necessary.

You have the option of creating an apply process using the DBMS_APPLY_ADM.CREATE_APPLY procedure and specifying nondefault values for the apply_captured, apply_database_link, and apply_tag parameters when you run that procedure. Then you can use this ADD_SCHEMA_RULES procedure to add rules to the rule set used by the apply process.

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is the schema name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the schema name plus the sequence number is too long, then the schema name is truncated.

The following is an example of a schema rule condition that may be created for filtering DML statements:

:dml.get_object_owner() = 'HR' AND :dml.is_null_tag() = 'Y'

For the overloaded ADD_SCHEMA_RULES procedure, the system-generated rule names for DML and DDL changes are returned.

A capture process or an apply process uses the rules created for filtering. If the process does not have a rule set, then a rule set is created automatically, and the rules for the schema are added to the rule set. Other rules in an existing rule set for the process are not affected. Additional rules can be added using the DBMS_RULE_ADM package.

If this procedure creates a capture process or an apply process, then the user who runs this procedure is the user who captures or applies changes. The specified user must have the necessary privileges to perform these actions.

Syntax

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name              IN  VARCHAR2,
   streams_type             IN  VARCHAR2,
   streams_name             IN  VARCHAR2 DEFAULT NULL,
   queue_name               IN  VARCHAR2 DEFAULT 'streams_queue',
   include_dml              IN  BOOLEAN  DEFAULT true,
   include_ddl              IN  BOOLEAN  DEFAULT false,
   include_tagged_lcr       IN  BOOLEAN  DEFAULT false,
   source_database          IN  VARCHAR2 DEFAULT NULL,
   dml_rule_name            OUT VARCHAR2,
   ddl_rule_name            OUT VARCHAR2);

Parameters

Table 73-5 ADD_SCHEMA_RULES Procedure Parameters (Page 1 of 2)

Parameter	Description
schema_name	The name of the schema. For example, hr. You can specify a schema that does not yet exist, because Streams does not validate the existence of the schema.
streams_type	The type of process, either capture or apply
streams_name	The name of the process. If the specified process does not exist, then it is created automatically. If NULL and one relevant capture or apply process for the queue exists, then the relevant process is used. If no relevant capture or apply process exists for the queue, then a capture process or an apply process is created automatically with a system-generated name. If NULL and multiple processes of the specified streams_type for the queue exist, then an error is raised.
queue_name	The name of the local queue. For capture rules, the queue into which the changes will be enqueued. For apply rules, the queue from which changes will be dequeued.
include_dml	If TRUE, then creates a rule for DML changes. If FALSE, then does not create a DML rule. NULL is not permitted.
include_ddl	If TRUE, then creates a rule for DDL changes. If FALSE, then does not create a DDL rule. NULL is not permitted.
include_tagged_lcr	If TRUE, then a redo entry is always considered for capture and an LCR is always considered for apply, regardless of whether the redo entry or LCR has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database. If FALSE, then a redo entry is considered for capture and an LCR is considered for apply only when the redo entry or the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules. For capture rules, you can specify NULL, because currently the capture database must be the same as the source database. For apply rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.
dml_rule_name	If include_dml is TRUE, then contains the DML rule name. If include_dml is FALSE, then contains a NULL.
ddl_rule_name	If include_ddl is TRUE, then contains the DDL rule name. If include_ddl is FALSE, then contains a NULL.

ADD_SUBSET_RULES Procedure

Adds apply rules for a subset of the rows in a table.

The apply process receives and applies captured events that contain DML changes for the specified subset of rows in the table. You should execute this procedure at the destination database.

Running this procedure generates three rules for the specified apply process: one for INSERT statements, one for UPDATE statements, and one for DELETE statements. For INSERT and DELETE statements, only row LCRs that satisfy the condition specified for the dml_condition parameter are applied. For UPDATE statements, the following variations are possible:

• If both the new and old values in a row LCR satisfy the specified dml_condition, then the LCR is applied without any changes.
• If neither the new or old values in a row LCR satisfy the specified dml_condition, then the row LCR is not applied.
• If the old values for a row LCR satisfy the specified dml_condition, but the new values do not, then the row LCR is converted into a delete.
• If the new values for a row LCR satisfy the specified dml_condition, but the old values do not, then the row LCR is converted to an insert.

The following is an example of a rule condition that may be created for filtering LCRs containing an update operation when the dml_condition is region_id = 2 and the table_name is hr.regions:

:dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'REGIONS' AND 
:dml.is_null_tag() = 'Y' AND :dml.get_command_type() = 'UPDATE' AND 
(:dml.get_value('NEW','"REGION_ID"') IS NOT NULL) AND 
(:dml.get_value('OLD','"REGION_ID"') IS NOT NULL) AND 
(:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2) AND 
(:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)

An apply process uses the generated rules for filtering LCRs. If the apply process does not have a rule set, then one is created automatically, and the rules for the table are added to the rule set. Other rules in an existing rule set for the apply process are not affected. Additional rules can be added using the DBMS_RULE_ADM package.

Rules for INSERT, UPDATE, and DELETE statements are created automatically when you run this procedure, and these rules are given a system-generated rule name. The system-generated rule name is the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated. The ADD_SUBSET_RULES procedure is overloaded, and the system-generated rule names for INSERT, UPDATE, and DELETE statements are returned.

An apply process created by this procedure can apply events only at the local database and can apply only captured events. To create an apply process that applies events at a remote database or an apply process that applies user-enqueued events, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

Changes applied by an apply process created by this procedure generate tags in the redo log at the destination database with a value of '00' (double zero). You can use the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to alter the tag value after the apply process is created, if necessary.

You have the option of creating an apply process using the DBMS_APPLY_ADM.CREATE_APPLY procedure and specifying nondefault values for the apply_captured, apply_database_link, and apply_tag parameters when you run that procedure. Then you can use this ADD_SUBSET_RULES procedure to add rules to the rule set used by the apply process.

When you create a subset rule for a table, you should create an unconditional supplemental log group at the source database with all the columns in the table. Supplemental logging is required if an update must be converted to an insert. The apply process must have all the column values to be able to perform the insert correctly.

If this procedure creates an apply process, then the user who runs this procedure is the user who applies changes. The specified user must have the necessary privileges to apply events.

Syntax

DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
   table_name               IN   VARCHAR2,
   dml_condition            IN   VARCHAR2,
   streams_type             IN   VARCHAR2 DEFAULT 'apply',
   streams_name             IN   VARCHAR2 DEFAULT NULL,
   queue_name               IN   VARCHAR2 DEFAULT 'streams_queue',
   include_tagged_lcr       IN   BOOLEAN  DEFAULT false,
   source_database          IN   VARCHAR2 DEFAULT NULL,
   insert_rule_name         OUT  VARCHAR2,
   update_rule_name         OUT  VARCHAR2,
   delete_rule_name         OUT  VARCHAR2);

Parameters

Table 73-6 ADD_SUBSET_RULES Procedure Parameters (Page 1 of 2)

Parameter	Description
table_name	The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default. You can specify a table that does not yet exist, because Streams does not validate the existence of the table.
dml_condition	The subset condition. You specify this condition similar to the way you specify conditions in a WHERE clause in SQL. For example, to specify rows in the hr.employees table where the salary is greater than 4000 and the job_id is SA_MAN, enter the following as the condition: ' salary > 4000 and job_id = ''SA_MAN'' ' Note: The quotation marks in the preceding example are all single quotation marks.
streams_type	The type of process. Currently, the only valid type is apply.
streams_name	The name of the apply process. If the specified apply process does not exist, then it is created automatically. If NULL, then the apply process for the queue is used. If no apply process exists for the queue, then one is created automatically with a system-generated name. If multiple apply processes exist, then an error is raised.
queue_name	The name of the local queue from which changes will be dequeued.
include_tagged_lcr	If TRUE, then an LCR is always considered for apply, regardless of whether the LCR has a non-NULL tag. If FALSE, then an LCR is considered for apply only when the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules. Specify the source database for the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.
insert_rule_name	Contains the system-generated INSERT rule name. This rule handles insert LCRs and update LCRs that must be converted into insert LCRs.
update_rule_name	Contains the system-generated UPDATE rule name. This rule handles update LCRs that remain update LCRs.
delete_rule_name	Contains the system-generated DELETE rule name. This rule handles delete LCRs and update LCRs that must be converted into delete LCRs

ADD_TABLE_PROPAGATION_RULES Procedure

Adds propagation rules that propagate the LCRs related to the specified table in a source queue to a destination queue. This procedure also configures propagation using the current user, if necessary, and establishes a default propagation schedule. This procedure enables propagation of LCRs for the specified table, subject to filtering conditions. Only one propagation job is allowed between the source queue and the destination queue.

If propagation rules are added, then the propagation job propagates DML changes, or DDL changes, or both related to the specified table from the specified source queue to the specified destination queue. This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated. For the overloaded ADD_TABLE_PROPAGATION_RULES procedure, the system-generated rule names for For the overloaded ADD_SCHEMA_RULES procedure, the system-generated rule names for DML and DDL changes are returned.

A propagation job uses the rules created for filtering. If the propagation job does not have a rule set, then a rule set is created automatically, and the rules for propagating changes to the table are added to the rule set. Other rules in an existing rule set for the propagation job are not affected. Additional rules can be added using the DBMS_RULE_ADM package.

The following is an example of a table rule condition that may be created for propagating DML changes with a propagation job:

:dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'LOCATIONS' 
AND :dml.is_null_tag() = 'Y' AND :dml.get_source_database_name() = 'DBS1.NET'

For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

Syntax

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name                IN  VARCHAR2,
   streams_name              IN  VARCHAR2,
   source_queue_name         IN  VARCHAR2,
   destination_queue_name    IN  VARCHAR2,
   include_dml               IN  BOOLEAN  DEFAULT true,
   include_ddl               IN  BOOLEAN  DEFAULT false,
   include_tagged_lcr        IN  BOOLEAN  DEFAULT false,
   source_database           IN  VARCHAR2 DEFAULT NULL,
   dml_rule_name             OUT VARCHAR2,
   ddl_rule_name             OUT VARCHAR2);

Parameters

Table 73-7 ADD_TABLE_PROPAGATION_RULES Procedure Parameters  

Parameter	Description
table_name	The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.
streams_name	The name of the propagation job. If the specified propagation job does not exist, then it is created automatically. If NULL and a propagation job exists for the same source queue and destination queue (including database link), then this propagation job is used. If NULL and no propagation job exists for the same source queue and destination queue (including database link), then a propagation job is created automatically with a system-generated name.
source_queue_name	The name of the source queue. The current database must contain the source queue.
destination_queue_name	The name of the destination queue, including database link, for example STREAMS_QUEUE@DBS2. If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed.
include_dml	If TRUE, then creates a rule for DML changes. If FALSE, then does not create a DML rule. NULL is not permitted.
include_ddl	If TRUE, then creates a rule for DDL changes. If FALSE, then does not create a DDL rule. NULL is not permitted.
include_tagged_lcr	If TRUE, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database. If FALSE, then an LCR is considered for propagation only when the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. The source database is where the change originated. If NULL, then no condition regarding the source database is added to the generated rules. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically. Oracle Corporation recommends that you specify a source database for propagation rules.
dml_rule_name	If include_dml is TRUE, then contains the DML rule name. If include_dml is FALSE, then contains a NULL.
ddl_rule_name	If include_ddl is TRUE, then contains the DDL rule name. If include_ddl is FALSE, then contains a NULL.

ADD_TABLE_RULES Procedure

Adds capture or apply rules for a table.

If capture rules are added, then the capture process captures DML changes, or DDL changes, or both in the specified table and enqueues these changes into the specified queue. For capture rules, you should execute this procedure at the source database. This procedure automatically invokes the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package for the specified table.

If apply rules are added, then the apply process receives and applies captured events that contain DML changes, or DDL changes, or both for the specified table. For apply rules, you should execute this procedure at the destination database.

An apply process created by this procedure can apply events only at the local database and can apply only captured events. To create an apply process that applies events at a remote database or an apply process that applies user-enqueued events, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

Changes applied by an apply process created by this procedure generate tags in the redo log at the destination database with a value of '00' (double zero). You can use the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to alter the tag value after the apply process is created, if necessary.

You have the option of creating an apply process using the DBMS_APPLY_ADM.CREATE_APPLY procedure and specifying nondefault values for the apply_captured, apply_database_link, and apply_tag parameters when you run that procedure. Then you can use this ADD_TABLE_RULES procedure to add rules to the rule set used by the apply process.

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated.

For example, the following is an example of a rule condition that may be created for filtering DML statements:

:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'EMPLOYEES' 
AND :dml.is_null_tag() = 'Y' AND :dml.get_source_database_name() = 'DBS1.NET'

For the overloaded ADD_TABLE_RULES procedure, the system-generated rule names for DML and DDL changes are returned.

A capture process or an apply process uses the rules created for filtering. If the process does not have a rule set, then a rule set is created automatically, and the rules for the table are added to the rule set. Other rules in an existing rule set for the process are not affected. Additional rules can be added using the DBMS_RULE_ADM package.

If this procedure creates a capture process or an apply process, then the user who runs this procedure is the user who captures or applies changes. The specified user must have the necessary privileges to perform these actions.

Syntax

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name               IN  VARCHAR2,
   streams_type             IN  VARCHAR2,
   streams_name             IN  VARCHAR2 DEFAULT NULL,
   queue_name               IN  VARCHAR2 DEFAULT 'streams_queue',
   include_dml              IN  BOOLEAN  DEFAULT true,
   include_ddl              IN  BOOLEAN  DEFAULT false,
   include_tagged_lcr       IN  BOOLEAN  DEFAULT false,
   source_database          IN  VARCHAR2 DEFAULT NULL,
   dml_rule_name            OUT VARCHAR2,
   ddl_rule_name            OUT VARCHAR2);

Parameters

Table 73-8 ADD_TABLE_RULES Procedure Parameters (Page 1 of 2)

Parameter	Description
table_name	The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default. You can specify a table that does not yet exist, because Streams does not validate the existence of the table.
streams_type	The type of process, either capture or apply
streams_name	The name of the process. If the specified process does not exist, then it is created automatically. If NULL and one relevant capture or apply process for the queue exists, then the relevant capture or apply process is used. If no relevant process exists for the queue, then a capture process or an apply process is created automatically with a system-generated name. If NULL and multiple processes of the specified streams_type for the queue exist, then an error is raised.
queue_name	The name of the local queue. For capture rules, the queue into which the changes will be enqueued. For apply rules, the queue from which changes will be dequeued.
include_dml	If TRUE, then creates a DML rule for DML changes. If FALSE, then does not create a DML rule. NULL is not permitted.
include_ddl	If TRUE, then creates a DDL rule for DDL changes. If FALSE, then does not create a DDL rule. NULL is not permitted.
include_tagged_lcr	If TRUE, then a redo entry is always considered for capture and an LCR is always considered for apply, regardless of whether redo entry or LCR has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database. If FALSE, then a redo entry is considered for capture and an LCR is considered for apply only when the redo entry or the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database. See Also: Oracle9i Streams for more information about tags
source_database	The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules. For capture rules, you can specify NULL, because currently the capture database must be the same as the source database. For apply rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.
dml_rule_name	If include_dml is TRUE, then contains the DML rule name. If include_dml is FALSE, then contains a NULL.
ddl_rule_name	If include_ddl is TRUE, then contains the DDL rule name. If include_ddl is FALSE, then contains a NULL.

PURGE_SOURCE_CATALOG Procedure

Removes all Streams data dictionary information at the local database for the specified object. You can use this procedure to remove Streams metadata that is not needed currently and will not be needed in the future.

The global name of the source database containing the object must be specified for the source_database parameter. If the current database is not the source database for the object, then data dictionary information about the object is removed at the current database, not the source database.

For example, suppose changes to the hr.employees table at the dbs1.net source database are being applied to the hr.employees table at the dbs2.net destination database. Also, suppose hr.employees at dbs2.net is not a source at all. In this case, specifying dbs2.net as the source_database for this table results in an error. However, specifying dbs1.net as the source_database for this table while running the PURGE_SOURCE_CATALOG procedure at the dbs2.net database removes data dictionary information about the table at dbs2.net.

Do not run this procedure at a database if either of the following conditions are true:

• LCRs captured by the capture process for the object are or may be applied locally without reinstantiating the object.
• LCRs captured by the capture process for the object are or may be forwarded by the database without reinstantiating the object.

  Note: These conditions do not apply to LCRs that were not created by the capture process. That is, these conditions do not apply to user-created LCRs.

Syntax

DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG(
   source_database        IN VARCHAR2,
   source_object_name     IN VARCHAR2,
   source_object_type     IN VARCHAR2);

Parameters

Table 73-9 PURGE_SOURCE_CATALOG Procedure Parameters

Parameter	Description
source_database	The global name of the source database containing the object. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.
source_object_name	The name of the object specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.
source_object_type	Type of the object. Currently, TABLE is the only possible object type.

REMOVE_RULE Procedure

Removes the specified rule or all rules from the rule set associated with the specified capture process, apply process, or propagation job.

Syntax

DBMS_STREAMS_ADM.REMOVE_RULE(
   rule_name             IN VARCHAR2,
   streams_type           IN VARCHAR2,
   streams_name          IN VARCHAR2,
   drop_unused_rule      IN BOOLEAN DEFAULT true);

Parameters

Table 73-10 REMOVE_RULE Procedure Parameters

Parameter	Description
rule_name	The name of the rule to remove. If NULL, then removes all rules for the specified capture process, apply process, or propagation job rule set.
streams_type	The type of Streams rule, either capture, apply, or propagate
streams_name	The name of the capture process, apply process, or propagation job
drop_unused_rule	If false, then the rule is not dropped from the database. If true and the rule is not in any rule set, then the rule is dropped from the database. If true and the rule exists in any rule set, then the rule is not dropped from the database.

SET_UP_QUEUE Procedure

Creates a queue table and a Streams queue for use with the capture, propagate, and apply functionality of Streams.

Set up includes the following actions:

• If the specified queue table does not exist, then this procedure runs the CREATE_QUEUE_TABLE procedure in the DBMS_AQADM package to create the queue table with the specified storage clause.
• If the specified queue name does not exist, then this procedure runs the CREATE_QUEUE procedure in the DBMS_AQADM package to create the queue.
• This procedure starts the queue.
• If a queue user is specified, then this procedure configures this user as a secure queue user of the queue and grants ENQUEUE and DEQUEUE privileges on the queue to the specified queue user.

  To configure the queue user as a secure queue user, this procedure creates an Advanced Queuing agent with the same name as the user name, if one does not already exist. If an agent with this name already exists and is associated with the queue user only, then it is used. SET_UP_QUEUE then runs the ENABLE_DB_ACCESS procedure in the DBMS_AQADM package, specifying the agent and the user.

This procedure creates a SYS.AnyData queue that is both a secure queue and a transactional queue.

Syntax

DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_table          IN VARCHAR2 DEFAULT 'streams_queue_table',
   storage_clause       IN VARCHAR2 DEFAULT NULL,
   queue_name           IN VARCHAR2 DEFAULT 'streams_queue',
   queue_user           IN VARCHAR2 DEFAULT NULL,
   comment              IN VARCHAR2 DEFAULT NULL);

Parameters

Table 73-11 SET_UP_QUEUE Procedure Parameters

Parameter	Description
storage_clause	The storage clause for queue table The storage parameter is included in the CREATE TABLE statement when the queue table is created. You can specify any valid table storage clause. If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace of the user who runs this procedure. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause. If NULL, then Oracle uses the storage characteristics of the tablespace in which the queue table is created. See Also: Oracle9i SQL Reference for more information about storage clauses
queue_name	The name of the queue that will function as the Streams queue, specified as [schema_name.]queue_name. For example, strmadmin.streams_queue. If the schema is not specified, then the current user is the default. If the queue owner is not specified, then it defaults to the queue table owner. The owner of the queue table must also be the owner of the queue. The queue owner automatically has privileges to perform all queue operations on the queue.
queue_user	The name of the user who requires ENQUEUE and DEQUEUE privileges for the queue. This user is also configured as a secure queue user of the queue. The queue user cannot grant these privileges to other users because they are not granted with the GRANT option. If NULL, then no privileges are granted. You can also grant queue privileges to the appropriate users using the DBMS_AQADM package.
comment	The comment for the queue