Re: Streams configuration within schemas in a database
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: gidhin joy <gidhin@xxxxxxxxx>
- Date: Wed, 11 Mar 2009 08:19:06 -0500
Forgot to declare l_owner variable. Obviously, I am unable to test it.. Send
me offline email, if you run in to more syntax issues :-)
Try this:
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);
l_owner 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;
/
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
> 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 declared
> SQL>
>
> Thanks.
>
>
Other related posts:
