pdb_setrepproc

(Oracle and Microsoft SQL Server only) Returns stored procedure replication marking status; marks specified procedures for replication; unmarks all marked procedures or a specified procedure; enables or disables replication for all marked procedures or a specified procedure.

Note: This command is available only for Oracle and Microsoft SQL Server.

Syntax

pdb_setrepproc
  [ { procname[, repname,] { mark
                             | unmark[, force]
                             | enable
                             | disable }
    | all, { unmark[, force]
           | enable
           | disable }
  } ]

To return stored procedure replication marking status:

pdb_setrepproc [ {procname|mark|unmark|enable|disable } ]

To unmark, enable, or disable all marked stored procedures:

pdb_setrepproc all, {unmark[, force]|enable|disable}

To mark a specified stored procedure for replication with a replicated name:

pdb_setrepproc procname, [repname,] mark

To unmark, enable, or disable a specified stored procedure:

pdb_setrepproc procname, {unmark[, force]|enable|disable}

Note: For Microsoft SQL Server, the pdb_dflt_object_repl configuration parameter has no impact on the pdb_setrepproc command.

Parameters

procname – The name of a user stored procedure in the primary database.
The procname option can be delimited with quote characters to specify the character case.
If mixed character case (both uppercase and lowercase) is required, the name must be delimited. For example:
```
"Proc"
```
Note: If you must use an object name case that does not match the setting of the ltl_character_case parameter, the object name must be delimited.
If an object name contains any non-alphanumeric characters, such as spaces, periods, and so forth, it must be delimited with quote characters. For example:
```
"proc name"
```
```
"proc.name"
```
If an object name contains a period, it must be both owner-qualified and delimited with quote characters. For example:
```
owner."proc.name"
```
```
"proc.owner"."proc.name"
```
repname – The name of the stored procedure specified in a function replication definition for the primary stored procedure.
The repname option can be delimited with quote characters to specify character case. See the previous description of the procname option for details.

By specifying a replicated name, stored procedure invocations can be replicated to a stored procedure invocation in the replicate database that has a different stored procedure name from the primary database.

Note: The replicated name you specify with the pdb_setrepproc command must match the name specified by a with primary function named clause in a Replication Server function replication definition for the primary database connection. Replication Agent cannot validate the function replication definition, but if it does not exist, function replication from the primary database fails.
all – A keyword that refers to all user stored procedures in the primary database. By using the all keyword, you can mark all user stored procedures, or apply an unmark, enable, or disable operation to all marked stored procedures.
mark – A keyword that refers to marking user stored procedures for replication. The Replication Agent must have CREATE ANY PROCEDURE permission to mark procedures for replication.
Note: You must specify an owner when using the mark keyword.
unmark – A keyword that refers to unmarking marked stored procedures.
force – A keyword that refers to the unmark operation. When the force keyword follows the unmark keyword, the pdb_setrepproc command immediately unmarks the specified stored procedure in the primary database, without first checking the enable status of the stored procedure. When the force keyword follows the unmark keyword and the all keyword, the pdb_setrepproc command immediately removes replication marking from all marked stored procedures in the primary database, regardless of their enable status.
The force keyword also forces complete execution of the unmarking script, even if errors occur during the unmarking process. Normally, when errors occur during script execution, the script terminates immediately without completing.

Note: Read the “Usage” section that follows to better understand how scripts are used in Oracle procedure marking and unmarking.
The force keyword can be useful when a previous script execution failed and left the unmarking operation incomplete. When errors occur during a forced script execution, the pdb_setrepproc command returns this message:
```
Errors were encountered and ignored during FORCED script execution. See error log for details.
```
enable – A keyword that refers to enabling replication for marked stored procedures.
disable – A keyword that refers to disabling replication for marked stored procedures.

Examples

Example 1 –
```
pdb_setrepproc
```
This command returns replication marking information for all marked stored procedures in the primary database.
Example 2 –
```
pdb_setrepproc authors
```
This command returns replication marking information for the user stored procedure named “authors” in the primary database.
Example 3 –
```
pdb_setrepproc authors, mark
```
This command marks the user stored procedure named “authors” in the primary database.
Example 4 –
```
pdb_setrepproc authors, enable
```
This command enables replication for the marked stored procedure named “authors” in the primary database.
Example 5 –
```
pdb_setrepproc all, unmark
```
This command unmarks all marked stored procedures in the primary database.

Usage

If a marked procedure is renamed or dropped and a new procedure with the original name is created, you must explicitly mark the new procedure because the new procedure has no marking-related information from the original procedure. The marking information is maintained internally by object id, not object name.
How you use the pdb_setrepproc command depends on the type of replication definition that you have created at Replication Server. If you have created a database replication definition with no function replication definition, the replicate procedure in the pdb_setrepproc command refers to the procedure in the replicate database. However, if you have created a function replication definition, the replicate procedure in the pdb_setrepproc command refers to the name of the function replication definition, and it is the function replication definition that must map to the procedure in the replicate database.
If no function replication definition exists and will not be added prior to replication, but only a database replication definition exists, use these commands to mark a procedure for replication:
- When the procedure in the replicate database has the same name as the procedure in the primary database:
```
pdb_setrepproc pdb_proc, mark
```
  where:
  
  pdb_proc is the name of the procedure in the primary database that you want to mark for replication.
- When the procedure in the replicate database has the different name than the procedure in the primary database:
```
pdb_setrepproc pdb_proc, rep_proc, mark
```
  where:
  
  pdb_proc is the name of the procedure in the primary database that you want to mark for replication.
  
  rep_proc is the name of the procedure in the replicate database.
If a function replication definition exists or will be added prior to replication, regardless of whether or not a database replication definition exists, use these commands to mark a procedure for replication:
- When the function replication definition has the same name as the procedure in the primary database:
```
pdb_setrepproc pdb_proc, mark
```
  where:
  
  pdb_proc is the name of the procedure in the primary database that you want to mark for replication.
  - If the procedure in the replicate database also has the same name as the function replication definition, there is no need to use the “deliver as” clause in the replication definition in the primary Replication Server. For example:
    create function replication definition pdb_proc with primary at data_server. database …
  - If the procedure in the replicate database has a different name than the name of function replication definition, the function replication definition must map to the procedure in the replicate database. For example:
    create function replication definition pdb_proc with primary at data_server.database deliver as ‘rep_proc’ …
- When the name of the function replication definition is different than the procedure in the primary database:
```
pdb_setrepproc pdb_proc , rdpri_proc, mark
```
  where:
  
  pdb_proc is the name of the procedure in the primary database that you want to mark for replication.
  
  rdpri_proc is the name of the function replication definition.
  - If the procedure in the replicate database also has the same name as the function replication definition, there is no need to use the “deliver as” clause in the replication definition in the primary Replication Server. For example:
    create function replication definition rdpri_proc with primary at data_server.database …
  - If the procedure in the replicate database has a different name from the function replication definition, the function replication definition must map to the procedure in the replicate database. For example:
    create function replication definition rdpri_proc with primary at data_server.database deliver as 'rep_proc' …
When multiple keywords and options are specified, each must be separated by a comma. Blank space before or after a comma is optional. For example:
```
pdb_setrepproc all, unmark, force
```
When you specify a stored procedure name in the pdb_setrepproc command, you must use the name of a valid user stored procedure.
You cannot specify these items as a stored procedure name in the pdb_setrepproc command:
- System procedures
- Replication Agent transaction log procedures
If a stored procedure name in the primary database is the same as a keyword, it can be identified by adding the string proc= to the beginning of the stored procedure name. For example:
```
pdb_setrepproc proc=unmark, mark
```
When you use the unmark keyword to remove replication marking from a stored procedure, Replication Agent verifies that replication is disabled for that stored procedure and there are no pending (unprocessed) operations for that stored procedure in the transaction log. If replication is not disabled for that procedure, or if there is a pending operation for that procedure in the transaction log, pdb_setrepproc returns an error.
When pdb_setrepproc is invoked with either no option or a single option, it returns marking information about the stored procedures in the primary database.
- If pdb_setrepproc is invoked with no option, it returns a list of all marked procedures in the primary database.
  
  Note: Invoking the pdb_setrepproc command with no option produces the same result as invoking the pdb_setrepproc command with only the mark keyword.
- If pdb_setrepproc is invoked with a procedure name, it returns complete marking information about the specified procedure.
- If pdb_setrepproc is invoked with the mark keyword, it returns a list of all marked procedures in the primary database.
- If pdb_setrepproc is invoked with the unmark keyword, it returns a list of all unmarked procedures in the primary database.
- If pdb_setrepproc is invoked with the enable keyword, it returns a list of all marked procedures in the primary database, for which replication is currently enabled.
- If pdb_setrepproc is invoked with the disable keyword, it returns a list of all marked procedures in the primary database, for which replication is currently disabled.
Stored procedures marked for replication are recorded in the RASD. All other user procedures are considered unmarked.

Note: The Replication Agent system procedures are not included in the list of unmarked procedures. Also not included are any synonyms or aliases for these procedures.

For procedures listed as unmarked or disabled, their invocations are not captured for replication.
When pdb_setrepproc is invoked with the all keyword and an action keyword (unmark, enable, or disable), the action specified is applied to either all user stored procedures in the primary database, or to all marked procedures in the primary database.
- If pdb_setrepproc is invoked with the all and unmark keywords, it removes replication marking from all marked procedures in the primary database.
  
  You can specify the force keyword after the unmark keyword to force immediate unmarking of all marked procedures, including procedures for which replication is still enabled.
- If pdb_setrepproc is invoked with the all and enable keywords, it enables replication for all marked procedures in the primary database.
- If pdb_setrepproc is invoked with the all and disable keywords, it disables replication for all marked procedures in the primary database.
When pdb_setrepproc is invoked with a valid user stored procedure name and followed by an action keyword (mark, unmark, enable, or disable), the action specified is applied to the specified procedure.
- If pdb_setrepproc is invoked with a procedure name and the mark keyword, it marks the specified procedure in the primary database for replication.
- If pdb_setrepproc is invoked with a procedure name and the unmark keyword, it removes replication marking from the specified procedure in the primary database.
- If pdb_setrepproc is invoked with a procedure name and the enable keyword, it enables replication for the specified marked procedure in the primary database.
- If pdb_setrepproc is invoked with a procedure name and the disable keyword, it disables replication for the specified marked procedure in the primary database.
Note: Use [mark | unmark] instead of [enable | disable] since the results are the same.
If you specify a stored procedure name that does not exist in the primary database, the pdb_setrepproc command returns an error.
When pdb_setrepproc is invoked with a procedure name and a replicated name, followed by the mark keyword, the primary procedure is marked for replication with the specified replicated name.

If the primary procedure name you specify does not exist in the primary database, the pdb_setrepproc command returns an error.

By specifying a replicated name, procedure invocations can be replicated to a procedure in the replicate database that has a different name from the primary procedure.

Note: The replicated name you specify with the pdb_setrepproc command must match the name of a Replication Server function replication definition for the primary database connection. Replication Agent cannot validate the function replication definition, but if it does not exist, function replication from the primary database fails.
If RASD is not initialized (Oracle and Microsoft SQL Server), the pdb_setrepproc command returns an error.

For Oracle:

To support stored procedure replication in Oracle, a stored procedure that is marked for replication must be modified. The modification is required to record the stored procedures execution in the Oracle transaction log. As a result of the modifications, consider this behavior when marking and unmarking stored procedures in Oracle:

You must disable DDL replication before marking or unmarking a procedure, and re-enable it after marking or unmarking to prevent modifications from replicating to standby.
Marking and unmarking a stored procedure for replication requires that Replication Agent drop, and then re-create the procedure. However, Replication Agent sets all the same privileges on the re-created procedure as those defined on the original procedure.

Note: Do not remove or alter Replication Agent comments in a marked stored procedure.
When pdb_setrepproc is invoked to mark a procedure for replication, Replication Agent:
- Modifies the user procedure to add code that captures input parameter values and generates Replication Agent transaction log records.
- Generates a SQL script that creates the procedures required for the Replication Agent transaction log in the primary database.
- Saves the generated script in a file called partmark.sql in the RAX-15_5\inst_name\scripts\procname directory, where inst_name is the name of the Replication Agent instance, and procname is the name of the stored procedure being marked. This script cannot be manually executed—it is for informational purposes only.
  
  Note: If the value of the pdb_auto_run_scripts configuration parameter is false, the partmark.sql script is saved but not executed automatically. You cannot manually run the script. To complete marking the procedure, you must first set pdb_auto_run_scripts to true, then re-run the pdb_setrepproc command.
- Executes the script to mark the stored procedure and create the transaction log objects in the primary database (if the value of the pdb_auto_run_scripts configuration parameter is true).
- After the script completes successfully, moves the partmark.sql file to the RAX-15_5\inst_name\scripts\procname\installed directory.
- If the mark script fails, it is stored in a file (partmark.sql) in the RAX-15_5\inst_name\scripts\procname directory, the stored procedure is not marked, and transaction log objects are not created. You can examine the script by viewing the mark.sql file.
When pdb_setrepproc is invoked to unmark a marked stored procedure, Replication Agent:
- Modifies the user procedure to remove Replication Agent code that captures input parameter values and generates transaction log records.
- Generates a SQL script that removes the tables and procedures required for the transaction log in the primary database.
- Saves the generated script in a file called partunmark.sql in the RAX-15_5\inst_name\scripts\procname directory, where inst_name is the name of the Replication Agent instance and procname is the name of the stored procedure being unmarked. For Oracle, this script named partunmark.sql because it cannot be manually executed—it is for informational purposes only.
  
  Note: If the value of the pdb_auto_run_scripts configuration parameter is false, the partunmark.sql script is saved but not executed automatically. You cannot manually run the script. To complete unmarking the procedure, you must first set pdb_auto_run_scripts to true, then re-run the pdb_setrepproc command.
- Executes the script to unmark the stored procedure and remove the transaction log objects in the primary database (if the value of the pdb_auto_run_scripts configuration parameter is true).
- After the script completes successfully, moves the partunmark.sql file to the RAX-15_5\inst_name\scripts\procname\installed directory.
- If the unmark script fails, it is stored in a file (partunmark.sql) in the RAX-15_5\inst_name\procname\scripts directory and the stored procedure is not unmarked and the transaction log objects are not removed. You can examine the script by viewing the partunmark.sql file.
  When the unmark script execution encounters a fatal error on any database object, the pdb_setrepproc command returns this message:
```
Could not unmark the following objects: ... 
See error log for details.
```
- The pdb_setrepproc command is used in replicating Oracle stored procedures that have an argument of type Boolean. See Replication Agent Primary Database Guide > Replication Agent for Oracle > Stored Procedure Replication with BOOLEAN Arguments.