-- DDL - Create Table / Sequence
drop table TAB_check_clob;
create table TAB_check_clob (tcc_string_id number(16)
,tcc_check_lfd varchar2(16)
,tcc_string varchar2(4000)
,tcc_value varchar2(4000)
,tcc_vorkommnis number(3)
);
--
drop sequence SEQ_check_clob;
create sequence SEQ_check_clob
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1;
-- DML - Insert "ToCheck"-Values
insert into TAB_check_clob values (SEQ_check_clob.nextval,'1','<Firma>',' ctpm',1);
insert into TAB_check_clob values (SEQ_check_clob.nextval,'1','<Telefon>',' +49 (0)221 277446-45',1);
insert into TAB_check_clob values (SEQ_check_clob.nextval,'1','<Standort>',' Lindlar',1);
insert into TAB_check_clob values (SEQ_check_clob.nextval,'1','<Standort>',' Köln',2);
insert into TAB_check_clob values (SEQ_check_clob.nextval,'1','<Standort>',' Hamburg',3);
insert into TAB_check_clob values (SEQ_check_clob.nextval,'1','<Standort>',' Berlin',4);
commit;

-- PL/SQL -
declare
procedure check_clob (IN_check_lfd TAB_check_clob.tcc_check_lfd%type, IN_clob clob) as
begin
for rec in (select *
from tab_check_clob c
where c.tcc_check_lfd = IN_check_lfd)
loop
dbms_output.put_line('Test-: '||IN_check_lfd||
' - ' ||rec.tcc_string||
' - ' ||dbms_lob.compare( IN_clob
,rec.tcc_value
,length(rec.tcc_value)
, dbms_lob.instr(IN_clob
,rec.tcc_string
,1
,rec.tcc_vorkommnis
)+length(rec.tcc_string)
)
);
end loop;
end;
begin
dbms_output.enable(10000000);
check_clob('1','<Firma> ctpm'||chr(10)||
'<Telefon> +49 (0)221 277446-45'||chr(10)||
'<Standort> Lindlar'||chr(10)||
'<Standort> Koeln'||chr(10)||
'<Standort> Hamburg'||chr(10)||
'<Standort> Berlin'
);
end;

--Ausgabe:

/*
Test-: 1 - <Firma> - 0
Test-: 1 - <Telefon> - 0
Test-: 1 - <Standort> - 0
Test-: 1 - <Standort> - -1 -- Koeln vs. Köln
Test-: 1 - <Standort> - 0
Test-: 1 - <Standort> - 0
*/