Re: Streams configuration within schemas in a database
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: gidhin@xxxxxxxxx
- Date: Mon, 9 Mar 2009 08:51:57 -0500
Hello gidhin
Since you have streams running already, I assume, you know how to do
basic setup for streams. With that, to setup streams between two schemas in
the same database, you would setup streams just like schemas between two
different database (i.e. loop back database link, capture, propagate and
apply) etc. Then add a DML handler to the table at apply process.
Please see example code below. Of course, I haven't tested this, but
should work just fine.
Example: EMP table from SCOTT schema to MARY schema.
REM
REM We create a generic dml handler so that we can reuse it.
REM Following example manipulates LCRs and updates schema.
REM conn as strmadmin for this procedure
CREATE OR REPLACE PROCEDURE generic_dml_handler(in_any IN SYS.ANYDATA) IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
command VARCHAR2(10);
old_values SYS.LCR$_ROW_LIST;
l_object_name varchar2(31);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
-- Get the object command type
command := lcr.GET_COMMAND_TYPE();
-- Get current object name
l_object_name := lcr.GET_OBJECT_NAME();
-- Set the object_owner in the row LCR if object name and owner matches.
if (l_object_name='EMP') then
l_owner := lcr.GET_OBJECT_OWNER();
IF (l_owner = 'SCOTT') then
-- Schema owner modified from scott to mary for emp table.
lcr.SET_OBJECT_OWNER ( 'MARY');
end if;
end if;
-- Apply the row LCR as an INSERT into the new table
lcr.EXECUTE(true);
END;
/
REM Add above DML Handler to the table
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'scott.emp',
object_type => 'TABLE',
operation_name => 'INSERT',-- modify accordingly
error_handler => false,
user_procedure => 'strmadmin.generic_dml_handler',
apply_database_link => NULL);
END;
/
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Mon, Mar 9, 2009 at 7:58 AM, gidhin joy <gidhin@xxxxxxxxx> wrote:
> Hi Lists,
>
> I need to configure streams for tables within database (From schema A to
> B) which i feels difficult since my configuration just fails.
> Can someone help me with the steps on configuring streams in same database
> or provide some links on this topic
> I am already using a streams environment between tables in databases
> located in Windows and Linux db server wich work fine.
>
> OS: AIX 5.3
> Oracle: 10.2.0.2.0
>
> Thanks.
>
Other related posts:
