SCOTT@orcl_11g> CREATE TABLE base_tab
  2    (col1  NUMBER,
  3  	col2  NUMBER,
  4  	col3  VARCHAR2 (30))
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO base_tab VALUES (2, 3, 'widgets')
  3  INTO base_tab VALUES (3, 5, 'widgets')
  4  INTO base_tab VALUES (2, 3, 'gadgets')
  5  INTO base_tab VALUES (3, 5, 'gadgets')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW mview AS
  2  SELECT (col1 * col2) || ' ' || col3 AS computed_col
  3  FROM   base_tab
  4  /

Materialized view created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON mview (computed_col)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
15
6
GADGETS
WIDGETS

SCOTT@orcl_11g> SELECT * FROM mview WHERE CONTAINS (computed_col, '6 widgets') > 0
  2  /

COMPUTED_COL
-----------------------------------------------------------------------
6 widgets

SCOTT@orcl_11g> SELECT * FROM mview WHERE CONTAINS (computed_col, '15 gadgets') > 0
  2  /

COMPUTED_COL
-----------------------------------------------------------------------
15 gadgets

SCOTT@orcl_11g> 

SCOTT@orcl_11g> CREATE TABLE base_tab
  2    (col1  NUMBER,
  3  	col2  NUMBER,
  4  	col3  VARCHAR2 (30),
  5  	computed_col VARCHAR2 (1))
  6  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO base_tab VALUES (2, 3, 'widgets', NULL)
  3  INTO base_tab VALUES (3, 5, 'widgets', NULL)
  4  INTO base_tab VALUES (2, 3, 'gadgets', NULL)
  5  INTO base_tab VALUES (3, 5, 'gadgets', NULL)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE compute_col
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    SELECT (col1 * col2) || ' ' || col3 AS computed_col
  7    INTO   p_clob
  8    FROM   base_tab
  9    WHERE  ROWID = p_rowid;
 10  END compute_col;
 11  /

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', 'compute_col');
  4  END;
  5  /

PL/SQL procedure successfully completed.

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

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
15
6
GADGETS
WIDGETS

SCOTT@orcl_11g> SELECT * FROM base_tab WHERE CONTAINS (computed_col, '6 widgets') > 0
  2  /

      COL1       COL2 COL3                           C
---------- ---------- ------------------------------ -
         2          3 widgets

SCOTT@orcl_11g> SELECT * FROM base_tab WHERE CONTAINS (computed_col, '15 gadgets') > 0
  2  /

      COL1       COL2 COL3                           C
---------- ---------- ------------------------------ -
         3          5 gadgets

SCOTT@orcl_11g> 

CREATE TABLE ECN_DESCRIPTION
(	"ECN_ID" NUMBER(38,0) NOT NULL ENABLE, 
	"ECN_DESCRIPTION_TYPE" NUMBER(2,0) NOT NULL ENABLE, 
	"BLANK_FLAG" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000 BYTE), 
	 CONSTRAINT "PK_ECN_DESCRIPTION" PRIMARY KEY ("ECN_ID", "ECN_DESCRIPTION_TYPE") 	 
) ;

CREATE INDEX ECNSRCH ON ECN_DESCRIPTION ("DESCRIPTION") 
INDEXTYPE IS "CTXSYS"."CONTEXT" ;


Query:

 Select distinct ecn_id FROM

              (Select ecn_id,

              (select description from ecn_description where ecn_description_type = 1 and ecn_id = ecndesc.ecn_id) "Change Description",

              (select description from ecn_description where ecn_description_type = 2 and ecn_id = ecndesc.ecn_id) "Change Reason",

              (select description from ecn_description where ecn_description_type = 3 and ecn_id = ecndesc.ecn_id) "Test Approach",

              (select description from ecn_description where ecn_description_type = 4 and ecn_id = ecndesc.ecn_id) "Implementation Notes",

              (select title from environment_change_note where environment_change_note.ecn_id = ecndesc.ecn_id) "Title"

              from ecn_description ecndesc)
         where CONTAINS("Change Description", '%b%')  > 0

Select distinct ecn_id 
FROM   (Select ecn_id,
               (select description 
                from   ecn_description 
                where  CONTAINS (description, '%b%') > 0
                and    ecn_description_type = 1 
                and    ecn_id = ecndesc.ecn_id) "Change Description",
               (select description 
                from   ecn_description 
                where  ecn_description_type = 2 
                and    ecn_id = ecndesc.ecn_id) "Change Reason",
               (select description 
                from   ecn_description 
                where  ecn_description_type = 3 
                and    ecn_id = ecndesc.ecn_id) "Test Approach",
               (select description 
                from   ecn_description 
                where  ecn_description_type = 4 
                and    ecn_id = ecndesc.ecn_id) "Implementation Notes"
               (select title 
                from   environment_change_note 
                where  environment_change_note.ecn_id = ecndesc.ecn_id) "Title"
        from   ecn_description ecndesc)
/