Home » Server Options » Streams & AQ » How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) (merged)
How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) (merged) [message #315717] Tue, 22 April 2008 09:25 Go to next message
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 #315718 is a reply to message #315717] Tue, 22 April 2008 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The below is the script to handle Update operation which is giving error
ERROR? What error? I don't see any error.

Read & following posting guidelines in URL below
http://www.orafaq.com/forum/t/88153/0/

What does your problem have to do with RAC or FailSafe?

[Updated on: Tue, 22 April 2008 09:33] by Moderator

Report message to a moderator

How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) [message #315832 is a reply to message #315717] Tue, 22 April 2008 22:53 Go to previous messageGo to next message
rsethy
Messages: 11
Registered: February 2008
Junior Member
I am configuring Oracle streams. Please guide me how to handle UPDATE dml operation in add_subset_rules.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) [message #315848 is a reply to message #315834] Tue, 22 April 2008 23:53 Go to previous messageGo to next message
rsethy
Messages: 11
Registered: February 2008
Junior Member
Thanks Michel. HAve a nice day.
Re: How to handle UPDATE dml opration in add_subset_rule(Oracle Streams) [message #319176 is a reply to message #315848] Fri, 09 May 2008 04:53 Go to previous message
rsethy
Messages: 11
Registered: February 2008
Junior Member
Hi,

I did it by handling the dml operation using procedure.

regards.

Raghu
Previous Topic: How to delete a column from LCR in Oracle 9i
Next Topic: Schema replication doesn't work
Goto Forum:
  


Current Time: Thu Mar 28 15:08:17 CDT 2024