This discussion is archived
1 Reply Latest reply: May 14, 2013 12:09 AM by 961076 RSS

Streams vachar2 to clob not working

961076 Newbie
Currently Being Moderated
Hi everybody,
i am configuring streams between two DBs with different schema names and table names
source tables has 9 columns and target has only 7
source table's columns are of different data types (varchar2, char, number) and target has almost all are varchar2 except for one which is clob(in source its varchar2(4000 char))
column names in source and target are same

i setup rules for renaming schema,table, keep_columns and all is working fine except for the varchar2 to clob conversion
as per http://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_apply.htm (Automatic Data Type Conversion During Apply) varchar2 will be automatically transformed to clob in oracle 11g.

when i start the streams and make entries
INSERT:all columns works but clob column doesnt get the entry(NULL values is recorded)
UPDATE :doesn't work
DELETE:works


this is my configuration
-----
source

begin dbms_streams_adm.add_table_rules
( table_name => 'user1.table1',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name=> 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/


begin dbms_streams_adm.add_table_propagation_rules
( table_name => 'user1.table1',
streams_name => 'DB1_2_DB2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@DB2',
include_dml => true,
include_ddl => true,
source_database => 'DB1',
inclusion_rule => true);
end;
/


declare
source_scn number;
begin
source_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@DB2
( source_object_name => 'user1.table1',
source_database_name => 'DB1',
instantiation_scn => source_scn);
end;
/
-----

Target

begin
dbms_streams_adm.add_table_rules
( table_name => 'user1.table1',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'DB1',
inclusion_rule => true);
END;
/


select rule_name,rule_owner from dba_rules;


begin
dbms_streams_adm.rename_schema(
rule_name => 'STRMADMIN.table1559',
from_schema_name => 'user1',
to_schema_name => 'user2',
operation => 'ADD');
end;
/

BEGIN
DBMS_STREAMS_ADM.RENAME_TABLE(
rule_name => 'STRMADMIN.table1559',
from_table_name => 'user1.table1',
to_table_name => 'user2.table2',
operation => 'ADD');
END;
/



declare
     keep_cols DBMS_UTILITY.LNAME_ARRAY;
begin
keep_cols(1):='A';
keep_cols(2):='B';
keep_cols(3):='C';
keep_cols(4):='D';
keep_cols(5):='E';
keep_cols(6):='F';
keep_cols(7):='G';
dbms_streams_adm.keep_columns(
rule_name => 'strmadmin.table1559',
table_name => 'user1.table1',
column_table=>keep_cols,
value_type => '*',
step_number => 0,
operation => 'ADD'
);
END;
/
-----
  • 1. Re: Streams vachar2 to clob not working
    961076 Newbie
    Currently Being Moderated
    To verify the functioning i created a procedure to enter the command type and the value of column D (which is varchar2 in source and clob in target) into a table status and it is data is as fllows
    -----
    select * from strmadmin.status;

    N COMMAND
    ---------- ----------
    1 INSERT (inserted a row)
    2 G3333333
    1 UPDATE (updated column c)
    2
    1 UPDATE (updated column D)
    2 V
    1 INSERT (inserted new row)
    2 G3333333
    1 DELETE
    2
    -----

    procedure is
    -----
    CREATE OR REPLACE PROCEDURE dml_check (in_any IN SYS.ANYDATA)
    IS

    lcr SYS.LCR$_ROW_RECORD;
    rc PLS_INTEGER;

    new_lcrrow sys.lcr$_row_list;
    old_lcrrow sys.lcr$_row_list;

    new_data varchar2(4000 char);
    new_data_anydata SYS.ANYDATA;


    command     varchar2(10 char);


    BEGIN
    rc := in_any.GETOBJECT(lcr);

    new_lcrrow := lcr.GET_VALUES('NEW');

    command :=     lcr.get_command_type();

    insert into strmadmin.status values(1,command);

    for i in 1..new_lcrrow.count
    loop
    if new_lcrrow(i).column_name = 'D' THEN
         new_data_anydata := new_lcrrow(i).data;
    rc := new_data_anydata.GETVARCHAR2(new_data);
         end if;
    end loop;


    insert into strmadmin.status values(2,new_data);

    commit;
    lcr.execute(true);

    END;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points