Home » Server Options » Streams & AQ » Oracle Streams - coming with hex value in target system (oracle 11.2.0.1.0 linux 2.6)
Oracle Streams - coming with hex value in target system [message #515744] Wed, 13 July 2011 03:06 Go to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear sir / madam,
We have setup Oracle streams for TARGET SYSTEM to pull data from the source system to the ODS layer. One specific column is coming from the source as a hex value into TARGET SYSTEM.

Needed your views on :-
• We have written a BEFORE INSERT / UPDATE trigger on TARGET SYSTEM table to convert this hex value to ASCII when there is a change in value. Will this trigger impact the performance of streams?
If the execution of trigger returns an error will it impact Oracle streams flow?
• Can the structure of the TARGET SYSTEM table be different from the source from where it is being streamed? (probably a couple of columns more in TARGET SYSTEM). Can oracle streams be configured to map source to target columns?

Thank you,
Kesavan
Re: Oracle Streams - coming with hex value in target system [message #515805 is a reply to message #515744] Wed, 13 July 2011 05:59 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
dear Sir / Madam,

I further checked, and found below:

for column level mapping:

we have to use a dml handler to remove the columns from the LCR
with the method lcr.delete_column.

For an example of DML handler use, have a look at "Oracle9i Streams" chapter 20 "Single Database
Capture and Apply Example":
http://download-west.oracle.com/docs...demo.htm#64404

For the LCR$_ROW_RECORD type, have a look at: "Supplied PL/SQL Packages and Types Reference",
Chapter 108 "Logical Change Record Types":
http://download-west.oracle.com/docs...cr.htm#1007284


and for trigger error, i got below link from metalink source:

http://blogs.oracle.com/db/entry/oracle_support_master_note_for_troubleshooting_streams_apply_errors_ora-1403_ora-26787_or_ora-26786

Could anyone further comment on this ?

Thank you,
Kesavan
Re: Oracle Streams - coming with hex value in target system [message #516418 is a reply to message #515805] Mon, 18 July 2011 00:57 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Sir / Madam,

further adding below 2 questions:

1 - will a trigger on a streamed table in ODS affect the streams performance ??

2 - any error with trigger will result in an error on streams ?



Thank you,
Kesavan
Re: Oracle Streams - coming with hex value in target system [message #516659 is a reply to message #516418] Tue, 19 July 2011 22:04 Go to previous messageGo to next message
ssgottik
Messages: 3
Registered: January 2009
Junior Member
Hi Kesavan,

What type of streams you are using ( TABLE, SCHEMA , DATABASE)?

- If you are using table level streams, you have to do some workaround to manage triggers. Better is to disable that triggers at target site and create a different trigger as per the requirment.

- If you have schema level streams , in that case no need to wory much about triggers ( provided source and target schema structure are same).

Below link may be useful for you

gssdba.wordpress.com/2011/04/20/steps-to-implement-schema-level-oracle-streams/

Thanks and Regards,
Satish.G.S
gssdba.wordpress.com
Re: Oracle Streams - coming with hex value in target system [message #516670 is a reply to message #516659] Wed, 20 July 2011 00:09 Go to previous message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Hi Sathish,

Thank you for your time and reply in this regard.

It will be a SCHEMA LEVEL stream and there are chances for a TABLE LEVEL stream as well.

But things are not yet implemented. We are just looking the pros and cons to arrive on a decision.That's how these queries came up.

note: source tables and target table may have diff columns..i.e. 2 columns will be more.. when i checked in goodle, and i got below response for column level mapping eventhough the structure of table is different in both source & target :

" column level mapping can be done using dml handler to remove the columns from the LCR with the method lcr.delete_column "

Sathish please give me your views for this column level mapping as well.


I further request you guide me.
If possible, can i have your email id ? I may send email regarding this issue only... i will not distrub for other issues.

Thank you very much Sathish.
kesavan
Previous Topic: NEW ORACLE STRAMS CONFIG
Next Topic: how to check whether STREAMS working or not
Goto Forum:
  


Current Time: Thu Mar 28 08:34:56 CDT 2024