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
