Search a clob for a pattern

Before we get too deep in the weeds, let's make sure we have a clear statement of the problem.

You say "search for a phrase." Do you need to search for it case-sensitive? Or should "Hello" match "hello"? Or do you want a function parameter to indicate whether search should be case sensitive or not? (Perhaps with a default if the parameter is not used?)

Can the word "hello" appear at the end of a line of text, then a newline, and then the word "world" at the beginning of the next line? Can there also be one (or more) blank line(s) in between?

Should the function find a match for "hello world" in the CLOB containing " ... Othello worldview ..." ?

I won't continue - I will let you attempt a complete, non-ambiguous phrasing of the requirement; I will ask follow-up questions if something remains unclear.

Here is one way. I used your original data for testing. I added two more rows to make sure the function works correctly when the CLOB is null and when it is non-null but it does not contain the search phrase.

insert into t values (7, null);
insert into t values (8, 'no match');

Newlines are not an issue; my question (which you did not dignify with an answer) was whether a newline BETWEEN the words "hello" and "world" should result in an automatic non-match. In this Reply I assume a newline between the search terms automatically makes it a non-match. Your users may not be happy about that, but that's on you.

Note that I won't entertain follow-up questions like "what if my words, in the phrase I am searching for, are separated by a newline, and that should still be a match" (or "separated by two spaces instead of one" etc.) I gave you a chance to clarify the question before looking for solutions; you chose to ignore that, fine - but I won't answer the same question more than once.

In the solution below I define a schema-level object type and corresponding schema-level table type to hold the output from the function. "Pipelined" is not essential; functions that "return rows" are known as table functions, and they may or may not be pipelined. In this solution I chose "pipelined" but that is not required.

For a long time now, functions like INSTR and LENGTH work fine on CLOB, and that's what I used.

create or replace type se_obj is object (s number, e number);
/
create or replace type se_tbl is table of se_obj;
/

create or replace function search_clob(p_lob clob, p_what varchar2)
  return se_tbl
  pipelined
as
  len int := length(p_what);
  s   int;
  pos int := 1;
begin
  if p_lob is not null then
    loop
      s := instr(p_lob, p_what, pos);
      exit when s = 0;
      pipe row (se_obj(s, s + len - 1));
      pos := s + len;
    end loop;
  end if;
  return;
end;
/

You can call the function with hard-coded arguments for testing. If you need to use it in a SQL context, you can do something like I show below. Note the OUTER APPLY operator; you need that if you want to show the input rows with no matches.

select t.x, l.s, l.e
from   t outer apply search_clob(y, 'hello world') l
;


         X          S          E
---------- ---------- ----------
         1         36         46
         2          8         18
         2         48         58
         3          8         18
         3         49         59
         4      28894      28904
         5      28894      28904
         6          1         11
         6      28908      28918
         7                      
         8