Re: Streams configuration within schemas in a database

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Wed, 11 Mar 2009 08:19:06 -0500
Message-ID: <203315c10903110619r5de6f115jf203a1ca6a4efcdf_at_mail.gmail.com>

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.

>

>


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

Received on Wed Mar 11 2009 - 08:19:06 CDT