Re: Streams configuration within schemas in a database
Date: Wed, 11 Mar 2009 12:47:49 +0530
Message-ID: <97e490bc0903110017n7e13239cmfe94928c37886c37_at_mail.gmail.com>
Hi,
dml handler creation failes as below,
scott and walsh schemas are on the same database "ebis"
Done the normal streams configuration and started apply process.but at the
end dml handler creation fails.
Any idea on what i can be the issue on this..
SQL> CREATE OR REPLACE PROCEDURE generic_dml_handler(in_any IN SYS.ANYDATA)
IS
2 lcr SYS.LCR$_ROW_RECORD;
3 rc PLS_INTEGER;
4 command VARCHAR2(10);
5 old_values SYS.LCR$_ROW_LIST;
6 l_object_name varchar2(31);
7 BEGIN
8 rc := in_any.GETOBJECT(lcr);
9 command := lcr.GET_COMMAND_TYPE();
10 l_object_name := lcr.GET_OBJECT_NAME();
11 if (l_object_name='EMP') then
12 l_owner := lcr.GET_OBJECT_OWNER(); 13 IF (l_owner = 'SCOTT') then 14 lcr.SET_OBJECT_OWNER ( 'WALSH'); 15 end if;
16 end if;
17 lcr.EXECUTE(true);
18 END;
19 /
Warning: Procedure created with compilation errors.
SQL> show err;
Errors for PROCEDURE GENERIC_DML_HANDLER:
LINE/COL ERROR
-------- ----------------------------------------------------------------- 12/4 PL/SQL: Statement ignored 12/4 PLS-00201: identifier 'L_OWNER' must be declared 13/4 PL/SQL: Statement ignored 13/9 PLS-00201: identifier 'L_OWNER' must be declaredSQL> Thanks.
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-lReceived on Wed Mar 11 2009 - 02:17:49 CDT