CREATE OR REPLACE FUNCTION no_html
  (p_string  IN CLOB)
  RETURN	CLOB
AS
  v_string_in   CLOB := p_string;
  v_string_out  CLOB;
BEGIN
  WHILE INSTR (v_string_in, '>') > 0 LOOP
    v_string_out := v_string_out
		 || SUBSTR (v_string_in, 1, INSTR (v_string_in, '<') - 1);
    v_string_in  := SUBSTR (v_string_in, INSTR (v_string_in, '>') + 1);
  END LOOP;
  v_string_out := v_string_out || v_string_in;
  RETURN v_string_out;
END no_html;
/ 

select regexp_replace(val, '<[^>]+>', '') new_val from data

--This is asktom function
SQL> select str_html('<p>He has to pay amount>$25 </p>') from dual;

STR_HTML('<P>HEHASTOPAYAMOUNT>$25</P>')
--------------------------------------------------------------------------------
He has to pay amount$25
--This is from Barbara Boehmer
SQL> select no_html('<p>He has to pay amount>$25 </p>') from dual;

NO_HTML('<P>HEHASTOPAYAMOUNT>$25</P>')
--------------------------------------------------------------------------------
He has to pay amount>$25 $25
--This is from Michel Cadot
SQL>  select regexp_replace('<p>He has to pay amount>$25 </p>', '<[^>]+>', '') new_val from dual;

NEW_VAL
-------------------------
He has to pay amount>$25

See this case if the input contains any "<" or ">" other than html tags..

SQL> select str_html('<p>He has to pay amount>$25 and this should be <$30 </p>') from dual;

STR_HTML('<P>HEHASTOPAYAMOUNT>$25ANDTHISSHOULDBE<$30</P>')
--------------------------------------------------------------------------------
He has to pay amount$25 and this should be

SQL> select no_html('<p>He has to pay amount>$25 and this should be <$30 </p>') from dual;

NO_HTML('<P>HEHASTOPAYAMOUNT>$25ANDTHISSHOULDBE<$30</P>')
--------------------------------------------------------------------------------
He has to pay amount>$25 and this should be $25 and this should be

SQL> select regexp_replace(<p>He has to pay amount>$25 and this should be <$30 </p>',
  2  '<[^>]+>', '') new_val from dual;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> select regexp_replace('<p>He has to pay amount>$25 and this should be <$30 </p>',
  2  '<[^>]+>', '') new_val from dual;

NEW_VAL
--------------------------------------------
He has to pay amount>$25 and this should be

SQL> 

SCOTT@orcl_11gR2> BEGIN
  2    CTX_DDL.CREATE_POLICY ('test_policy', 'CTXSYS.AUTO_FILTER');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION no_html
  2    (p_clob IN CLOB)
  3    RETURN CLOB
  4  AS
  5    v_blob		  BLOB;
  6    v_clob		  CLOB;
  7    v_dest_offset NUMBER := 1;
  8    v_src_offset  NUMBER := 1;
  9    v_lang		  NUMBER := 0;
 10    v_warning     VARCHAR2 (1000);
 11  BEGIN
 12    DBMS_LOB.CREATETEMPORARY (v_blob, FALSE);
 13    DBMS_LOB.CONVERTTOBLOB
 14  	      (v_blob,
 15  	       p_clob,
 16  	       DBMS_LOB.LOBMAXSIZE,
 17  	       v_dest_offset,
 18  	       v_src_offset,
 19  	       DBMS_LOB.DEFAULT_CSID,
 20  	       v_lang,
 21  	       v_warning);
 22    DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
 23    CTX_DOC.POLICY_FILTER ('test_policy', v_blob, v_clob, TRUE);
 24    DBMS_LOB.FREETEMPORARY (v_blob);
 25    RETURN v_clob;
 26    DBMS_LOB.FREETEMPORARY (v_clob);
 27  END no_html;
 28  /

Function created.

SCOTT@orcl_11gR2> select no_html('<p>He has to pay amount>$25 </p>') from dual
  2  /

NO_HTML('<P>HEHASTOPAYAMOUNT>$25</P>')
--------------------------------------------------------------------------------


He has to pay amount>$25



1 row selected.

SCOTT@orcl_11gR2> select no_html('<p>He has to pay amount>$25 and this should be <$30 </p>') from dual
  2  /

NO_HTML('<P>HEHASTOPAYAMOUNT>$25ANDTHISSHOULDBE<$30</P>')
--------------------------------------------------------------------------------


He has to pay amount>$25 and this should be <$30



1 row selected.

SCOTT@orcl_11gR2>