| How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) (merged) [message #315717] |
Tue, 22 April 2008 09:25  |
rsethy
Messages: 11
Registered: February 2008
|
Junior Member |
|
|
Hi,
I am trying to configure to replicate date for the table dbt_m_bank where the branch_code=729.
I am able to replicate all insert & Delete operation on that table. But i am facing to problem in Update operation. The below is the script to handle Update operation which is giving error. Please check the script or suggest how to handle UPDATE operation using add_subset_rule.
Configure in Destination Database.
<Connect as your Streams Administrator, eg STRMADMIN>
rem Create DML handler procedure
CREATE OR REPLACE PROCEDURE EBAMEND_DML_HANDLER(in_any IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
-- Filter out required row and and columns
IF object_owner = 'TD01' and
object_name = 'TFT_P_GTEE_EBAMEND' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN
-- Remove Columns
lcr.delete_column('rep_colsupp','*');
LCR.EXECUTE(TRUE);
END IF;
END;
/
rem Set the DML Handler for the INSERT operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'TD01.TFT_P_GTEE_EBAMEND',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EBAMEND_DML_HANDLER',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the UPDATE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'TD01.TFT_P_GTEE_EBAMEND',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EBAMEND_DML_HANDLER',
apply_database_link=> NULL);
END;
/
rem Set the DML Handler for the DELETE operations
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'TD01.TFT_P_GTEE_EBAMEND',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EBAMEND_DML_HANDLER',
apply_database_link=> NULL);
END;
/
Regards.
Raghu
|
|
|
|
|
|
|
|
| Re: How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) [message #315834 is a reply to message #315832] |
Tue, 22 April 2008 23:16  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It is useless to create a new topic with the same question.
Streams was introduced late in 9.2. It does not work very well in this version, most of those who use Streams use it in 10.2, so if you don't have answer maybe nobody knows the answer or maybe it will take time for someone knowing it to read your post (did you read all mine?), so be patient or call Oracle support.
You could also upgrade to 10.2 then you will have more answers.
Regards
Michel
[Updated on: Tue, 22 April 2008 23:17] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
