Do SharePlex replicate creation of PL/SQL objects like procedures functions and packages if their replication is turned on but their creation on source runs into compilation errors (4297333)

General information.

SharePlex will replicate such PL/SQL objects regardless of whether they are VALID (no compilation errors) or INVALID (with compilation errors). The creation of such objects is replicated if the parameter SP_OCT_REPLICATE_ALL_ DDL is set at 1. The following test illustrates this:

I have the parmaeter SP_OCT_REPLICATE_ALL_ DDL set at 1. This parameter enables extended DDL support for objects not in replication. Such objects also include PL/SQL objects like stored procedures, functions and packages:

sp_ctrl (alvsupl14:9111)> list param modified capture

Oracle Capture parameters:

Parameter  Name                 Actual Value                           Units   Set At

------------------------------  ------------------------------------  -------  ---------------

SP_OCT_REPLICATE_ALL_DDL        1                                              Live

   Default   Value:     0

Here is the my procedure executed on source which errored out as the table named table2y never existed on source:

SQL> create procedure proc2 as

 x NUMBER := 0;

BEGIN

FOR i IN 1..10 LOOP

 INSERT INTO sha91.table2y values (x);

 x := x+1;

END LOOP;

COMMIT;

END;  2    3    4    5    6    7    8    9

 10  /

Warning: Procedure created with compilation errors.

SQL> show errors

Errors for PROCEDURE PROC2:

LINE/COL ERROR

-------- -----------------------------------------------------------------

5/2      PL/SQL: SQL Statement ignored

5/20     PL/SQL: ORA-00942: table or view does not exist

The DBA view shows that the status of this procedure is INVALID as shown:

SQL> select object_name, object_type, status from user_objects where object_name='PROC2';

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE         STATUS

------------------- -------

PROC2

PROCEDURE           INVALID

Despite the INVALID status, it will be replicated across to the target as the following query on target shows:

Target:

SQL> select text from all_source where owner='SHA91' and name = 'PROC2';

TEXT

--------------------------------------------------------------------------------

procedure proc2 as

 x NUMBER := 0;

BEGIN

FOR i IN 1..10 LOOP

 INSERT INTO sha91.table2y values (x);

 x := x+1;

END LOOP;

COMMIT;

END;

9 rows selected.

SQL>  select object_name, object_type, status from user_objects where object_name='PROC2';

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE         STATUS

------------------- -------

PROC2

PROCEDURE           INVALID

To correct the problem, I then create the missing table named table2y on source and recompile the procedure as:

Source:

SQL> create table table2y (num_col number);

Table created.

Target:

SQL> desc table2y;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 NUM_COL                                            NUMBER

Source:

SQL> alter procedure proc2 compile;

Procedure altered.

SQL> select object_name, object_type, status from user_objects where object_name='PROC2';

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE         STATUS

------------------- -------

PROC2

PROCEDURE           VALID

Once the table named table2y was created on source, it replicated across to the target due to SP_OCT_REPLICATE_ALL_ DDL set at 1. One need not recompile the PL/SQL object manually on target as doing so on source will recompile it on target automatically, given that the ALTER command for such objects is replicated.

Target:

SQL> select object_name, object_type, status from user_objects where object_name='PROC2';

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE         STATUS

------------------- -------

PROC2

PROCEDURE           VALID