oracle 11g streams 逻辑修改记录（LCR）示例

接前面"oracle 11g streams 应用进程使用示例"

1、LCR中额外特性

--可以额外指定row_id、serial#、session#、thread#、tx_name、username--指定捕获（capture）ROW_ID、USERNAMEbegin    dbms_capture_adm.include_extra_attribute(        capture_name=>'DBXA_CAP',        attribute_name=>'ROW_ID',        include=>TRUE    );    dbms_capture_adm.include_extra_attribute(        capture_name=>'DBXA_CAP',        attribute_name=>'USERNAME',        include=>TRUE    );end;/PL/SQL procedure successfully completed.--查看包含LCR的额外特性信息select attribute_name,    includefrom dba_capture_extra_attributeswhere capture_name='DBXA_CAP'order by attribute_name;ATTRIBUTE_NAME              INCLUDE--------------------------- -------------ROW_ID                      YESSERIAL#                     NOSESSION#                    NOTHREAD#                     NOTX_NAME                     NOUSERNAME                    YESrows selected.

2、访问LCR内容

--展示提取和修改LCR内容的方法--创建一个审计表，用来存储LCR相关信息connstrmadmin/[e-mail]_audit(    msg_datedate default sysdate,    source_database_name varchar2(30),    command_type varchar2(30),    object_owner varchar2(30),    object_name varchar2(30),    is_null_tag varchar2(1),    tag varchar2(30),    transaction_id varchar2(30),    scn number,    empno number,    column_name varchar2(30),    old_value number,    new_value number,    row_id varchar2(30),    username varchar2(30));Tablecreated.--创建DML处理存储过程create or replace procedure salary_audit_proc (    in_any IN SYS.ANYDATA)is    -- Define variable lcr and rc, to access contents of row.    -- LCR from sys.lcr$_row_record type and to hold the return    -- code respectively.    lcr SYS.LCR$_ROW_RECORD;    rc PLS_INTEGER;    -- Define local variables to hold values for various data    -- fields from the LCR to insert into the salary_audit table.    l_source_db varchar2(30);    l_command_type varchar2(30);    l_object_owner varchar2(30);    l_object_name varchar2(30);    l_is_null_tag varchar2(1);    l_tag varchar2(30);    l_transaction_id varchar2(30);    l_scn number;    l_empno number;    l_column_name varchar2(30);    l_old_value number;    l_new_value number;    l_row_id varchar2(30);    l_username varchar2(30);    l_extra_attr ANYDATA;    l_get_number ANYDATA;    begin        -- Access the row LCR from the parameter in_any.        -- We use the GETOBJECT function of the SYS.ANYDATA type.        rc := in_any.GETOBJECT(lcr);        -- The contents of the LCR are now accessible using various        -- member functions of the SYS.LCR$_ROW_RECORD type.        -- Extract source_database_name using GET_SOURCE_DATABASE_NAME        -- function.        l_source_db := lcr.GET_SOURCE_DATABASE_NAME();        -- Extract command_type using GET_COMMAND_TYPE function.        l_command_type := lcr.GET_COMMAND_TYPE();        -- Extract object_owner using GET_OBJECT_OWNER function.        l_object_owner := lcr.GET_OBJECT_OWNER();        -- Extract object_name using GET_OBJECT_NAME function.        l_object_name := lcr.GET_OBJECT_NAME();        -- Extract is_null_tag using IS_NULL_TAG function.        l_is_null_tag := lcr.IS_NULL_TAG();        -- Extract tag value using GET_TAG function.        l_tag := lcr.GET_TAG();        -- Extract transaction_id using GET_TRANSACTION_ID function.        l_transaction_id := lcr.GET_TRANSACTION_ID();        -- Extract scn using GET_SCN function.        l_scn := lcr.GET_SCN();        -- We will use the GET_VALUE function to extract the value        -- of the EMPNO column. This column is configured for        -- unconditional supplemental logging and so it will be        -- present in the LCR for all changes to the row.        -- Since the procedure is invoked for UPDATE operation,        -- the EMPNO will be available in the old values in the LCR.        -- The GET_VALUE function returns ANYDATA type.        -- The ACCESSNUMBER function of ANYDATA will retrieve        -- the number for EMPNO.        l_get_number := lcr.GET_VALUE('OLD','EMPNO');        l_empno := l_get_number.ACCESSNUMBER();        -- We are tracking employee salary in column called SAL.        -- There is no need to extract the column name from the LCR.        -- We just set the variable to the column name.        l_column_name := 'SAL';        -- If the SAL column was updated then we want to store the        -- old and new value of this column in our audit table.        -- If it was changed then the LCR will have its old and        -- new value. If it was not changed, then the LCR will not        -- have new values.        -- The parameters for the GET_VALUE function indicate that we        -- are looking for the NEW values of SAL column and do not        -- want (N) the function to return old values.        l_get_number := lcr.GET_VALUE('NEW','SAL');        l_new_value := l_get_number.ACCESSNUMBER();        -- If l_new_value is NOT NULL, then it means the column was        -- changed, and we will extract the old value.        if l_new_value is not null        then            l_get_number := lcr.GET_VALUE('OLD','SAL','Y');            l_old_value := l_get_number.ACCESSNUMBER();        end if;        -- The rowid and username are the extra attributes that we have        -- captured in the LCR. The SYS.ANYDATA provides a member function        -- called GET_EXTRA_ATTRIBUTE to access those by their names.        -- The function returns the value as ANYDATA. We then need to use        -- the ACCESSUROWID and ACCESSVARCHAR2 functions to extract their        -- values from the ANYDATA type.        l_extra_attr := lcr.GET_EXTRA_ATTRIBUTE('row_id');        l_row_id := l_extra_attr.ACCESSUROWID();        l_extra_attr := lcr.GET_EXTRA_ATTRIBUTE('username');        l_username := l_extra_attr.ACCESSVARCHAR2();        -- Now, we insert the values in our audit table, only if the        -- SAL column was updated.        if l_new_value is not null        then            insert into salary_audit            values (sysdate,                l_source_db,                l_command_type,                l_object_owner,                l_object_name,                l_is_null_tag,                l_tag,                l_transaction_id,                l_scn,                l_empno,                l_column_name,                l_old_value,                l_new_value,                l_row_id,                l_username            );        end if;        -- Do not commit this row. It will be automatically done after        -- we execute the LCR. Please note that the LCR was handed to our        -- procedure by the apply process when it detected an UPDATE        -- operation against the SCOTT.EMP table. So, we must execute        -- the LCR irrespective of the change to the SAL column.        lcr.execute (true);        -- The TRUE option in the above command enables the automatic        -- conflict detection by the apply process.        -- Setting it to FALSE disables it.    end;/Procedure created.show errorsNo errors.--将存储过程与APPLY进程关联begin    dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'UPDATE',        error_handler => FALSE,        user_procedure => 'STRMADMIN.SALARY_AUDIT_PROC',        apply_name => 'DBXA_APP'    );end;/PL/SQL procedure successfully completed.--当对EMP表的员工编号7566工作从2975改成3000表更改，审计表信息如下：EMPNO  SOURCE_DB      COMMAND  OWNER    TABLE_NAME   IS_NULL_TAG TAG TXN_ID     SCN     COLUMN   OLD_VALUE  NEW_VALUE  ROW_ID             USERNAME ------ -------------- -------- -------- ------------ ----------- --- ---------- ------- -------- ---------- ---------- ------------------ --------  7566 DBXA.WORLD     UPDATE   SCOTT    EMP          Y                 6.0.2049 4188833 SAL            2975       3000 AAAPqZAAEAAAACzAAD SCOTT

3、访问DDLLCR内容

--创建DDL LCR审计表conn strmadmin/[e-mail] table ddl_audit (    msg_date date,    source_database_name varchar2(30),    command_type varchar2(30),    object_owner varchar2(30),    object_name varchar2(30),    object_type varchar2(20),    ddl_text clob,    logon_user varchar2(30),    current_schema varchar2(30),    base_table_owner varchar2(30),    base_table_name varchar2(30),    streams_tag raw(10),    transaction_id varchar2(30),    scn number)/Table created.  --创建处理存储过程create or replace procedure    ddl_audit_proc (in_any IN SYS.ANYDATA)is    lcr SYS.LCR$_DDL_RECORD;    rc PLS_INTEGER;    l_ddl_text CLOB default empty_clob();begin    -- Access the DDL LCR from the parameter in_any.    -- We use the GETOBJECT function of the SYS.ANYDATA type.    rc := in_any.GETOBJECT(lcr);    -- The contents of the DDL LCR are now accessible using various    -- member functions of the SYS.LCR$_DDL_RECORD type.    -- These functions are similar in behavior to the ones we saw    -- earlier for accessing the row LCR contents.    -- To access the DDL Text in the LCR we initialize the    -- LOB segment.    dbms_lob.createtemporary(l_ddl_text, TRUE);    -- Extract the DDL Text from the LCR into the local CLOB.    lcr.GET_DDL_TEXT(l_ddl_text);    -- Extract information from DDL LCR to insert into the    -- DDL Audit table using member functions    -- of SYS.LCR$_DDL_RECORD.    insert into ddl_audit    VALUES (SYSDATE,        lcr.GET_SOURCE_DATABASE_NAME(),        lcr.GET_COMMAND_TYPE(),        lcr.GET_OBJECT_OWNER(),        lcr.GET_OBJECT_NAME(),        lcr.GET_OBJECT_TYPE(),        l_ddl_text,        lcr.GET_LOGON_USER(),        lcr.GET_CURRENT_SCHEMA(),        lcr.GET_BASE_TABLE_OWNER(),        lcr.GET_BASE_TABLE_NAME(),        lcr.GET_TAG(),        lcr.GET_TRANSACTION_ID(),        lcr.GET_SCN()    );        -- We want to ignore the DDL command that creates indexes        -- in the destination database.    if lcr.GET_COMMAND_TYPE != 'CREATE INDEX'    then        lcr.execute();    end if;    -- Free the temporary LOB from temp tablespace.    DBMS_LOB.FREETEMPORARY(l_ddl_text);END;/Procedure created.show errorsNo errors.--和APPLY进程关联begin        dbms_apply_adm.alter_apply(        apply_name => 'DBXA_APP',        ddl_handler => 'DDL_AUDIT_PROC'    );end;/PL/SQL procedure successfully completed.--假如在EMP表中添加了新列，可以在审计表中看到以下内容COMMAND      LOGON_USER OBJECT_OWNER OBJECT_NAME OBJECT_TYPE BASE_TABLE_OWNER BASE_TABLE_NAME TRANSACTION_ID SCN        DDL_TEXT------------ ---------- ------------ ----------- ----------- ---------------- --------------- -------------- ---------- ----------------------------------------ALTER TABLE  STRMADMIN  SCOTT        EMP         TABLE       SCOTT            EMP             9.9.2022          4202354 alter table scott.emp add (PHONE number)

4、修改LCR内容

--修改行LCR--将HIRE_DATE和BIRTH_DATE数据类型改为DATE类型create or replace procedure    convert_timestamp_to_date (in_any in anydata)is    lcr SYS.LCR$_ROW_RECORD;    rc PLS_INTEGER;    l_command_type varchar2(30);    l_timestamp timestamp;    l_new_values SYS.LCR$_ROW_LIST;    l_old_values SYS.LCR$_ROW_LIST;begin    -- Access the row LCR.    rc := in_any.GETOBJECT(lcr);    -- Extract the command_type    l_command_type := lcr.GET_COMMAND_TYPE();    -- Check for the command type and perform actions.    -- If the command was INSERT, then we will have only    -- the new values for the table columns.    if l_command_type='INSERT'    then        -- Extract the list of new values.        l_new_values := lcr.GET_VALUES('NEW');        -- Loop through the new values list.        for i in 1 .. l_new_values.count        loop            if l_new_values(i).data is not null            then                -- Check if interested columns are in the list.                if l_new_values(i).column_name in ('HIRE_DATE','BIRTH_DATE')                then                    -- Extract the values of the interested columns.                    rc := l_new_values(i).data.GETTIMESTAMP(l_timestamp);                    -- Convert the Timestamp data type to Date.                    l_new_values(i).data := ANYDATA.CONVERTDATE(to_date(trunc(l_timestamp)));                end if;            end if;        end loop;        -- Set the new values list in the LCR.        lcr.SET_VALUES('NEW',value_list=>l_new_values);        --        -- If the command was UPDATE then we will have        -- the old and new values for the table columns.    elsif l_command_type='UPDATE'    then        -- Extract the list of Old values.        l_old_values := lcr.GET_VALUES('OLD', 'Y');        for i in 1 .. l_old_values.count        -- Loop through the old values list.        loop            if l_old_values(i).data is not null            then        -- Check if interested columns are in the list.                if l_old_values(i).column_name in ('HIRE_DATE','BIRTH_DATE')                then                    -- Extract the values of the interested columns.                    rc := l_old_values(i).data.GETTIMESTAMP(l_timestamp);                    -- Convert the Timestamp data type to Date.                    l_old_values(i).data := ANYDATA.CONVERTDATE(to_date(trunc(l_timestamp)));                end if;            end if;        end loop;        -- Set the old values list in the LCR.        lcr.SET_VALUES('OLD',value_list=>l_old_values);        -- Now, extract the list of new values.        l_new_values := lcr.GET_VALUES('NEW', 'N');        -- Loop through the new values list.        for i in 1 .. l_new_values.count        loop            if l_new_values(i).data is not null            then                -- Check if interested columns are in the list.                if l_new_values(i).column_name in ('HIRE_DATE','BIRTH_DATE')                then                    -- Extract the values of the interested columns.                    rc := l_new_values(i).data.GETTIMESTAMP(l_timestamp);                    -- Convert the Timestamp data type to Date.                    l_new_values(i).data := ANYDATA.CONVERTDATE(to_date(trunc(l_timestamp)));                end if;            end if;        end loop;        -- Set the new values list in the LCR.        lcr.SET_VALUES('NEW',value_list=>l_new_values);        --        -- If the command was DELETE then we will have only        -- the old values for the table columns.    elsif l_command_type ='DELETE'    then        l_old_values := lcr.GET_VALUES('OLD', 'Y');        for i in 1 .. l_old_values.count        -- Loop through the old values list.        loop            if l_old_values(i).data is not null            then            -- Check if interested columns are in the list.                if l_old_values(i).column_name in ('HIRE_DATE','BIRTH_DATE')                then                    -- Extract the values of the interested columns.                    rc := l_old_values(i).data.GETTIMESTAMP(l_timestamp);                    -- Convert the Timestamp data type to Date.                    l_old_values(i).data := ANYDATA.CONVERTDATE(to_date(trunc(l_timestamp)));                end if;            end if;        end loop;        -- Set the old values list in the LCR.        lcr.SET_VALUES('OLD',value_list=>l_old_values);    end if;    -- Execute the modified LCR.    lcr.execute(true);end;/Procedure created.SQL> show errorsNo errors.--将上面的存储过程与应用程序关联begin    dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'UPDATE',        error_handler => FALSE,        user_procedure => 'STRMADMIN.CONVERT_TIMESTAMP_TO_DATE',        apply_name => 'DBXA_APP'    );    dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'INSERT',        error_handler => FALSE,        user_procedure => 'STRMADMIN.CONVERT_TIMESTAMP_TO_DATE',        apply_name => 'DBXA_APP'    );    dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'DELETE',        error_handler => FALSE,        user_procedure => 'STRMADMIN.CONVERT_TIMESTAMP_TO_DATE',        apply_name => 'DBXA_APP'    );end;/PL/SQL procedure successfully completed.

5、修改DDLLCR（替换schema和owner）

connect strmadmin/[e-mail] or replace procedure ddl_handler (    in_any IN SYS.ANYDATA)is    lcr SYS.LCR$_DDL_RECORD;    rc PLS_INTEGER;    l_ddl_text CLOB default empty_clob();    l_ddl_text_new CLOB default empty_clob();    l_source_schema1 varchar2(30);    l_source_schema2 varchar2(30);    l_dest_schema1 varchar2(30);    l_dest_schema2 varchar2(30);begin    -- Initialize the variables for source and destination schema names.    l_source_schema1 := ' '||'SCOTT';    l_dest_schema1 := ' '||'KIRTI';    l_source_schema2 := ' "'||'SCOTT'||'"';    l_dest_schema2 := ' "'||'KIRTI'||'"';    -- Access the DDL LCR from the parameter in_any.    -- We use the GETOBJECT function of the SYS.ANYDATA type.    rc := in_any.GETOBJECT(lcr);    -- The contents of the DDL LCR are now accessible using various    -- member functions of the SYS.LCR$_DDL_RECORD type.    -- To access the DDL Text in the LCR we initialize the    -- LOB segment.    dbms_lob.createtemporary(l_ddl_text, TRUE);    -- Extract the DDL Text from the LCR into the local variable.    lcr.GET_DDL_TEXT(l_ddl_text);    -- Change the current schema to match the destination schema name.    lcr.SET_CURRENT_SCHEMA('KIRTI');    -- Change the object owner to match the destination object owner.    lcr.SET_OBJECT_OWNER('KIRTI');    -- Using the Regular Expression function, replace all occurrences    -- of the source schema name to destination schema name in the DDL text.    l_ddl_text_new :=    regexp_replace(l_ddl_text,l_source_schema1||'\.',l_dest_schema1,1,0,'i');    l_ddl_text_new :=    regexp_replace(l_ddl_text_new,l_source_schema2||'\.',l_dest_schema2,1,0,'i');    -- Set the DDL text in the LCR to the new text.    lcr.SET_DDL_TEXT(l_ddl_text_new);    -- Free the temp lob for DDL text.    dbms_lob.freetemporary(l_ddl_text);    -- Execute the modified DDL LCR.    lcr.execute();end;/Procedure created.show errorsNo errors.--和APPLY进程关联begin    dbms_apply_adm.alter_apply(        apply_name => 'DBXA_APP',        ddl_handler => 'DDL_HANDLER'    );end;/PL/SQL procedure successfully completed.

6、LCR和LOB数据类型

--修改目标数据库应用LOB，提高应用效率--创建对行操作handlercreate or replace procedure LOB_ASSEMBLER(in_any IN SYS.ANYDATA)is    lcr SYS.LCR$_ROW_RECORD;    rc PLS_INTEGER;BEGIN    -- Access the LCR    rc := in_any.GETOBJECT(lcr);    -- Apply the row LCR    lcr.EXECUTE(TRUE);END;/Procedure created.--为含有LOB类型的DML指定APPLY进程与LOB_ASSEMBLER关联begin    dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'INSERT',        error_handler => TRUE,        assemble_lobs => TRUE,        user_procedure => 'strmadmin.lob_assembler',        apply_name => 'DBXA_APP');        dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'UPDATE',        error_handler => TRUE,        assemble_lobs => TRUE,        user_procedure => 'strmadmin.lob_assembler',        apply_name => 'DBXA_APP');        dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'DELETE',        operation_name => 'INSERT',        error_handler => TRUE,        assemble_lobs => TRUE,        user_procedure => 'strmadmin.lob_assembler',        apply_name => 'DBXA_APP');        dbms_apply_adm.set_dml_handler(        object_name => 'SCOTT.EMP',        object_type => 'TABLE',        operation_name => 'LOB_UPDATE',        error_handler => TRUE,        assemble_lobs => TRUE,        user_procedure => 'strmadmin.lob_assembler',        apply_name => 'DBXA_APP'    );end;/PL/SQL procedure successfully completed.