Hi, I am working on a project in which data marts are involved. We are creating triggers to transfer data from OLTP DB to data mart (Online extraction). Following is the code of a trigger for a table involving clob column. I have seen different solutions for Clobs and also used one of them, but how can we transfer clob in trigger as follows:
</
create or replace trigger transfer_case_info
after insert or update
on case_info
for each row
declare
cursor ppccursor is
select regexp_substr(:new.US_PPC,'[^,]+', 1, level) as sectionid from dual connect by regexp_substr(:new.US_PPC, '[^,]+', 1, level) is not null;

cursor cpccursor is
select regexp_substr(:new.US_CPC,'[^,]+', 1, level) as sectionid from dual connect by regexp_substr(:new.US_CPC, '[^,]+', 1, level) is not null;

cursor crpccursor is
select regexp_substr(:new.US_CRPC,'[^,]+', 1, level) as sectionid from dual connect by regexp_substr(:new.US_CRPC, '[^,]+', 1, level) is not null;


begin
insert into dim_case_info@shc_oltp_mart(
case_uac ,
case_no ,
case_year ,
category ,
district_in_which_instituted ,
us_ppc ,
us_cpc ,
us_crpc ,
status ,
disposal_date ,
nature_of_disposal ,
judicial_side ,
institution_date ,
judgement ,
first_court ,
case_title ,
case_stage ,
court_name ,
update_date ,
bench ,
method_of_institution ,
hc_side ,
judgement_status ,
applicant_type ,
respondent_type ,
highcourt )
values
(
:new.CASE_UAC,
:new.CASE_NO,
:new.CASE_YEAR,
:new.CATEGORY,
:new.DISTRICT_IN_WHICH_INSTITUTED,
:new.US_PPC,
:new.US_CPC,
:new.US_CRPC,
:new.STATUS,
:new.DISPOSAL_DATE,
:new.NATURE_OF_DISPOSAL,
:new.JUDICIAL_SIDE,
:new.INSTITUTION_DATE,
:new.JUDGEMENT,
:new.FIRST_COURT,
:new.CASE_TITLE,
:new.CASE_STAGE,
:new.COURT_NAME,
:new.UPDATE_DATE,
:new.BENCH,
:new.METHOD_OF_INSTITUTION,
:new.HC_SIDE,
:new.JUDGEMENT_STATUS,
:new.APPLICANT_TYPE,
:new.RESPONDENT_TYPE,
:new.DECISION_IN_FAVOR,
:new.STATEMENTS_STATUS,
:new.CASE_DIARY_STATUS,
:new.HIGHCOURT
);

for code in ppccursor
loop
insert into dim_cases_offences(
case_uac ,
code_category ,
section_id

)
values
(
:new.CASE_UAC,
'PPC',
code.sectionid
);
end loop;

for code in cpccursor
loop
insert into dim_cases_offences(
case_uac ,
code_category ,
section_id

)
values
(
:new.CASE_UAC,
'CPC',
code.sectionid
);
end loop;

for code in crpccursor
loop
insert into dim_cases_offences(
case_uac ,
code_category ,
section_id

)
values
(
:new.CASE_UAC,
'CRPC',
code.sectionid
);
end loop;


end;


>


here judgement column is clob data. Appreciate solutions and help! Thank you.

SQL> select * 
  2  from   t@db11;

ERROR:
ORA-22992: cannot use LOB locators selected from remote tables


SQL> select * 
  2  from   t@db122;

        ID C
---------- --------------------------------------------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--
-- target (database = db11)
--
SQL> create table t ( x int, c clob );

Table created.


--
-- source
--
SQL> create table t ( x int, c clob );

Table created.

SQL> create or replace trigger trg
  2  for insert on t
  3  compound trigger
  4
  5    pk sys.odcinumberlist := sys.odcinumberlist();
  6
  7    before each row is
  8    begin
  9      pk.extend;
 10      pk(pk.last) := :new.x;
 11    end before each row;
 12
 13    after statement is
 14    begin
 15      for i in pk.first .. pk.last loop
 16        insert into t@db11
 17        select x, c
 18        from   t
 19        where  x = pk(i);
 20      end loop;
 21
 22    end after statement;
 23
 24  end;
 25  /

Trigger created.

SQL>
SQL> insert into t values (1,'xxxxxxxxxxxxxxxx');

1 row created.

SQL> commit;

Commit complete.

--
-- target
--
SQL> select * from t;

         X C
---------- --------------------------
         1 xxxxxxxxxxxxxxxx

1 row selected.