this is test 1

 
this is test 2

SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (id	   NUMBER,
  3  	text_col1  VARCHAR2 (10),
  4  	text_col2  VARCHAR2 (10),
  5  	dir_col    VARCHAR2 (30),
  6  	path_col   VARCHAR2 (30),
  7  	all_cols   VARCHAR2 ( 1))
  8  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY test_dir AS 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES (1, 'test one', null, null, null, null)
  3  INTO test_tab VALUES (2, null, 'test two', null, null, null)
  4  INTO test_tab VALUES (3, 'test three', 'test four', null, null, null)
  5  INTO test_tab VALUES (4, null, null, 'TEST_DIR', 'test1.dat', null)
  6  INTO test_tab VALUES (5, null, null, 'TEST_DIR', 'test2.dat', null)
  7  INTO test_tab VALUES (6, null, null, null, null, null)
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5    v_clob	   CLOB;
  6    v_bfile	   BFILE;
  7  BEGIN
  8    FOR r IN (SELECT * FROM test_tab WHERE ROWID = p_rowid) LOOP
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (r.text_col1), 0) + 1, r.text_col1 || CHR (10));
 10  	 DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (r.text_col2), 0) + 1, r.text_col2 || CHR (10));
 11  	 IF r.dir_col IS NOT NULL AND r.path_col IS NOT NULL THEN
 12  	   v_bfile := BFILENAME (r.dir_col, r.path_col);
 13  	   DBMS_LOB.OPEN (v_bfile);
 14  	   DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
 15  	   DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
 16  	   DBMS_LOB.CLOSE (v_bfile);
 17  	   DBMS_LOB.APPEND (p_clob, v_clob);
 18  	   DBMS_LOB.FREETEMPORARY (v_clob);
 19  	 END IF;
 20    END LOOP;
 21  END test_proc;
 22  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'test_proc');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (all_cols)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE test_datastore')
  4  /

Index created.

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT id, CTX_DOC.SNIPPET ('test_idx', ROWID, 'test')
  2  FROM   test_tab
  3  WHERE  CONTAINS (all_colS, 'test') > 0
  4  /

        ID
----------
CTX_DOC.SNIPPET('TEST_IDX',ROWID,'TEST')
--------------------------------------------------------------------------------
         1
<b>test</b> one


         2
<b>test</b> two

         3
<b>test</b> three
<b>test</b> four

         4
this is <b>test</b> 1

         5
this is <b>test</b> 2


SCOTT@orcl_11g> 
SCOTT@orcl_11g>

BEGIN 
  CTX_DOC.SET_KEY_TYPE ('ROWID'); 
END;
/