Re: Streams configuration within schemas in a database

Thanks much for the reply. I will try this out. I have some doubt:

Does this setup requires a separate destination queue? I guess no.

Loopback database link is nothing but a dblink to the same database itself?

Thanks,
Gidhin

2009/3/9 Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>

> 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_at_gmail.com> 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.
>>
>
>
>

--
http://www.freelists.org/webpage/oracle-l