ok, you may use substr in correlation to instr to find the starting position of your string
select
dbms_lob.substr(
product_details,
length('NEW.PRODUCT_NO'), --amount
dbms_lob.instr(product_details,'NEW.PRODUCT_NO') --offset
)
from my_table
where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1;