1 Reply Latest reply: May 14, 2013 12:09 AM by 961076 RSS

    Streams vachar2 to clob not working

    961076
      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
          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;
          /