OraFAQ Forum: Text & interMedia

Home » Server Options » Text & interMedia » query with clob (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query with clob [message #473756]

Tue, 31 August 2010 11:29

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

is there any way to optimise the query which has a blob column in where condition ,without using the domain index.

Report message to a moderator

Re: query with clob [message #473759 is a reply to message #473756]

Tue, 31 August 2010 11:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>is there any way to optimise the query which has a blob column in where condition ,without using the domain index.
NO

Report message to a moderator

Re: query with clob [message #473787 is a reply to message #473759]

Tue, 31 August 2010 12:38

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

ok.. even i created the domain index, but in explain plan that domain index is not using.

Report message to a moderator

Re: query with clob [message #473788 is a reply to message #473787]

Tue, 31 August 2010 12:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

if you say so.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

[Updated on: Tue, 31 August 2010 12:40]

Report message to a moderator

Re: query with clob [message #473790 is a reply to message #473787]

Tue, 31 August 2010 12:50

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Your title says clob, but your message says blob; Which is it, clob or blob? Please post a small test case, including create table statement, insert statement for sample data, index creation, query, and explain plan.

Report message to a moderator

Re: query with clob [message #473809 is a reply to message #473790]

Tue, 31 August 2010 14:02

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

This is the query,cames_notes is a clob field

explain plan for select distinct ist.ITEMTYPE as ItemType,
ist.ITEMID as ItemId from 
HCLT_PARKING_LOT IST
WHERE (UPPER(IST.CM_Description) like '%TCK68072%' OR
UPPER(IST.USER_IMPACT) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_CAMS) like '%TCK68072%' OR
CONTAINS(IST.Cams_Notes, 'TCK68072') > 0 or
UPPER(IST.PM_Smart_Status) like '%TCK68072%' OR
UPPER(IST.CM_ItemCode) like '%TCK68072%' or
UPPER(IST.MQ_Comments) like '%TCK68072%' OR
UPPER(IST.CAMS_Status) like '%TCK68072%' OR
UPPER(IST.CM_Name) like '%TCK68072%' OR
UPPER(IST.Customer_Phone) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_PM_Smart) like '%TCK68072%' OR
UPPER(IST.Number_of_Attachments) like '%TCK68072%' OR
UPPER(IST.Customer_GUID) like '%TCK68072%' OR
UPPER(IST.CM_OverallStatus) like '%TCK68072%' OR
UPPER(IST.CAMS_Ticket_Type) like '%TCK68072%' OR
UPPER(IST.CAMS_Id) like '%TCK68072%' OR
UPPER(IST.Customer_Name) like '%TCK68072%')
and ist.ownertype = 'Prj'
and ist.ownerid = 76129
and ist.itemtype = 'TCK_f'
Order by ItemId

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3950410978
--------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | By
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     1 |  5
|   1 |  SORT ORDER BY                |                             |     1 |  5
|   2 |   HASH UNIQUE                 |                             |     1 |  5
|*  3 |    TABLE ACCESS BY INDEX ROWID| HCLT_PARKING_LOT            |     1 |  5
|*  4 |     INDEX RANGE SCAN          | HCLT_IDX_PARKING_OWNER_ITEM |     1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(UPPER("IST"."CM_DESCRIPTION") LIKE '%TCK68072%' OR UPPER("IST"."US
              '%TCK68072%' OR UPPER("IST"."DATE_CREATED_IN_CAMS") LIKE '%TCK6807
              UPPER("IST"."PM_SMART_STATUS") LIKE '%TCK68072%' OR UPPER("IST"."C
              UPPER("IST"."MQ_COMMENTS") LIKE '%TCK68072%' OR UPPER("IST"."CAMS_
              UPPER("IST"."CM_NAME") LIKE '%TCK68072%' OR UPPER("IST"."CUSTOMER_

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              UPPER("IST"."DATE_CREATED_IN_PM_SMART") LIKE '%TCK68072%' OR UPPER
              '%TCK68072%' OR UPPER("IST"."CM_OVERALLSTATUS") LIKE '%TCK68072%'
              LIKE '%TCK68072%' OR UPPER("IST"."CAMS_ID") LIKE '%TCK68072%' OR U
              '%TCK68072%' OR UPPER(TO_CHAR("IST"."NUMBER_OF_ATTACHMENTS")) LIKE
              "CTXSYS"."CONTAINS"("IST"."CAMS_NOTES",'TCK68072')>0)
   4 - access("IST"."OWNERTYPE"='Prj' AND "IST"."OWNERID"=76129 AND "IST"."ITEMT

26 rows selected

IF I CHNAGE THE QUERY LIKE THIS

select distinct ist.ITEMTYPE as ItemType,
ist.ITEMID as ItemId from 
HCLT_PARKING_LOT IST
WHERE (/*UPPER(IST.CM_Description) like '%TCK68072%' OR
UPPER(IST.USER_IMPACT) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_CAMS) like '%TCK68072%' OR*/
CONTAINS(IST.Cams_Notes, 'TCK68072') > 0 --or
/*UPPER(IST.PM_Smart_Status) like '%TCK68072%' OR
UPPER(IST.CM_ItemCode) like '%TCK68072%' or
UPPER(IST.MQ_Comments) like '%TCK68072%' OR
UPPER(IST.CAMS_Status) like '%TCK68072%' OR
UPPER(IST.CM_Name) like '%TCK68072%' OR
UPPER(IST.Customer_Phone) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_PM_Smart) like '%TCK68072%' OR
UPPER(IST.Number_of_Attachments) like '%TCK68072%' OR
UPPER(IST.Customer_GUID) like '%TCK68072%' OR
UPPER(IST.CM_OverallStatus) like '%TCK68072%' OR
UPPER(IST.CAMS_Ticket_Type) like '%TCK68072%' OR
UPPER(IST.CAMS_Id) like '%TCK68072%' OR
UPPER(IST.Customer_Name) like '%TCK68072%'*/)
and ist.ownertype = 'Prj'
and ist.ownerid = 76129
and ist.itemtype = 'TCK_f'
Order by ItemId

Plan of this query

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2714365578
--------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |  2104 |
|   1 |  SORT ORDER BY                |                   |     1 |  2104 |
|   2 |   HASH UNIQUE                 |                   |     1 |  2104 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HCLT_PARKING_LOT  |     1 |  2104 |
|*  4 |     DOMAIN INDEX              | MY_ITEMS_NAME_IDX |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("IST"."OWNERTYPE"='Prj' AND "IST"."OWNERID"=76129 AND
              "IST"."ITEMTYPE"='TCK_f')
   4 - access("CTXSYS"."CONTAINS"("IST"."CAMS_NOTES",'TCK68072')>0)

18 rows selected

Index details

SQL> select index_name,column_name,column_position from user_ind_columns where table_name='HCLT_PARKING_LOT';

INDEX_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
HCLT_IDX_PARKING_OWNER_ITEM    OWNERTYPE                                                                                      1
HCLT_IDX_PARKING_OWNER_ITEM    OWNERID                                                                                        2
HCLT_IDX_PARKING_OWNER_ITEM    ITEMTYPE                                                                                       3
HCLT_IDX_PARKING_OWNER_ITEM    CM_OVERALLSTATUS                                                                               4
HCLT_IDX_PARKING_OWNER_ITEM    ENTERPRISEID                                                                                   5
HCLT_IDX_PARKING_ITEMTYPE      ITEMTYPE                                                                                       1
HCLT_IDX_PARKING_OWNER         OWNERTYPE                                                                                      1
HCLT_IDX_PARKING_OWNER         OWNERID                                                                                        2
HCLT_IDX_PARKING_OWNER         ENTERPRISEID                                                                                   3
SYS_C006417                    OWNERTYPE                                                                                      1
SYS_C006417                    OWNERID                                                                                        2
SYS_C006417                    ITEMTYPE                                                                                       3
SYS_C006417                    ITEMID                                                                                         4
SYS_C006417                    ENTERPRISEID                                                                                   5
MY_ITEMS_NAME_IDX              CAMS_NOTES                                                                                     1

SQL> select index_name,index_type,table_name from User_Indexes where table_name='HCLT_PARKING_LOT';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
HCLT_IDX_PARKING_OWNER_ITEM    NORMAL                      HCLT_PARKING_LOT
HCLT_IDX_PARKING_ITEMTYPE      NORMAL                      HCLT_PARKING_LOT
HCLT_IDX_PARKING_OWNER         NORMAL                      HCLT_PARKING_LOT
SYS_C006417                    NORMAL                      HCLT_PARKING_LOT
MY_ITEMS_NAME_IDX              DOMAIN                      HCLT_PARKING_LOT
SYS_IL0000054161C00068$$       LOB                         HCLT_PARKING_LOT

stats data

QL> SELECT TABLE_NAME,STATUS,LAST_ANALYZED,PCT_FREE FROM USER_TABLES WHERE TABLE_NAME='HCLT_PARKING_LOT';

TABLE_NAME                     STATUS   LAST_ANALYZED   PCT_FREE
------------------------------ -------- ------------- ----------
HCLT_PARKING_LOT               VALID    9/1/2010 12:2         10

[Updated on: Tue, 31 August 2010 14:12]

Report message to a moderator

Re: query with clob [message #473817 is a reply to message #473809]

Tue, 31 August 2010 15:56

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You should create a multi_column_datastore that includes all of the unstructured columns that you are searching for the value TCK68072 in, include that in your index parameters, then just use one contains clause to search for all of those columns, eliminating all of the clauses with like. That will allow one domain index hit for all of those columns. You should also use a bind variable for your search string, so that each subsequent query with a different value does not have to be re-parsed and what is in the SGA can be re-used. Please see the demo below in which I have just included two of the unstrucutred non-clob columns.

SCOTT@orcl_11gR2> create table hclt_parking_lot
  2    ( cams_notes	 clob
  3    , ownertype	 varchar2 (18)
  4    , ownerid	 number
  5    , itemtype	 varchar2 (18)
  6    , itemid 	 number
  7    , cm_description  varchar2 (30)
  8    , user_impact	 varchar2 (30)
  9  	-- other unstructured columns
 10    , search_columns  varchar2 (1)
 11    )
 12  /

Table created.

SCOTT@orcl_11gR2> insert into hclt_parking_lot values
  2  ('word1 TCK68072 word2', 'Prj', 76129, 'TCK_f', 0, 'word3 TCK68072 word4', 'word5 TCK68072 word6', null)
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into hclt_parking_lot
  2  select object_name, object_type, object_id, object_type, object_id, object_name, object_name, null
  3  from   all_objects
  4  /

71706 rows created.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_preference ('test_datastore', 'multi_column_datastore');
  3    ctx_ddl.set_attribute
  4  	  ('test_datastore',
  5  	   'columns',
  6  	   'cams_notes, cm_description, user_impact'); -- add other unstrucutred columns to list
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index test_idx
  2  on hclt_parking_lot (search_columns)
  3  indextype is ctxsys.context
  4  parameters ('datastore test_datastore')
  5  /

Index created.

SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'hclt_parking_lot')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> variable search_string varchar2 (100)
SCOTT@orcl_11gR2> exec :search_string := 'TCK68072'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> set autotrace on explain
SCOTT@orcl_11gR2> select distinct ist.ITEMTYPE as ItemType,
  2  	    ist.ITEMID as ItemId
  3  from   HCLT_PARKING_LOT IST
  4  WHERE  CONTAINS (IST.search_columns, :search_string) > 0
  5  and    ist.ownertype = 'Prj'
  6  and    ist.ownerid   = 76129
  7  and    ist.itemtype  = 'TCK_f'
  8  Order  by ItemId
  9  /

ITEMTYPE               ITEMID
------------------ ----------
TCK_f                       0

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1734504895

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    42 |     6  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                |                  |     1 |    42 |     6  (34)| 00:00:01 |
|   2 |   HASH UNIQUE                 |                  |     1 |    42 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HCLT_PARKING_LOT |     1 |    42 |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX              | TEST_IDX         |       |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("IST"."OWNERID"=76129 AND "IST"."OWNERTYPE"='Prj' AND
              "IST"."ITEMTYPE"='TCK_f')
   4 - access("CTXSYS"."CONTAINS"("IST"."SEARCH_COLUMNS",:SEARCH_STRING)>0)

SCOTT@orcl_11gR2>

Report message to a moderator

Re: query with clob [message #473869 is a reply to message #473817]

Wed, 01 September 2010 05:00

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Thanks ... Its working fine, but one thing i am getting here,is that i have 100 columns in my table and once i set the ctx_ddl.set_attribute with all columns, its size exceeds and give the error message drg-10706 means maximum size for column list is 500, so please advice me how can i do this and second thing how can i set the sync option of index creation with auto commit .

Report message to a moderator

Re: query with clob [message #473890 is a reply to message #473869]

Wed, 01 September 2010 08:08

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

prashant_ora wrote on Wed, 01 September 2010 03:00

... error message drg-10706 means maximum size for column list is 500, so please advice me how can i do this ...

The column list is limited to 500 bytes. As a workaround, you can use a procedure with a user_datastore instead, which is what the multi_column_datastore does behind the scenes.

prashant_ora wrote on Wed, 01 September 2010 03:00

... how can i set the sync option of index creation with auto commit .

Add "sync (on commit)" to your index parameters.

Please see the revised demo below.

SCOTT@orcl_11gR2> create table hclt_parking_lot
  2    ( cams_notes	 clob
  3    , ownertype	 varchar2 (18)
  4    , ownerid	 number
  5    , itemtype	 varchar2 (18)
  6    , itemid 	 number
  7    , cm_description  varchar2 (30)
  8    , user_impact	 varchar2 (30)
  9  	-- other unstructured columns
 10    , search_columns  varchar2 (1)
 11    )
 12  /

Table created.

SCOTT@orcl_11gR2> insert into hclt_parking_lot values
  2  ('word1 TCK68072 word2', 'Prj', 76129, 'TCK_f', 0, 'word3 TCK68072 word4', 'word5 TCK68072 word6', null)
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into hclt_parking_lot
  2  select object_name, object_type, object_id, object_type, object_id, object_name, object_name, null
  3  from   all_objects
  4  /

71747 rows created.

SCOTT@orcl_11gR2> create or replace procedure test_proc
  2    (p_rowid in rowid,
  3  	p_clob	in out nocopy clob)
  4  as
  5  begin
  6    for r in
  7  	 (select * from hclt_parking_lot where rowid = p_rowid)
  8    loop
  9  	 dbms_lob.append (p_clob, r.cams_notes);
 10  	 dbms_lob.writeappend (p_clob, length (r.cm_description) + 1, ' ' || r.cm_description);
 11  	 dbms_lob.writeappend (p_clob, length (r.user_impact) + 1, ' ' || r.user_impact);
 12  	 -- add other unstrucutred columns to list
 13    end loop;
 14  end test_proc;
 15  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> 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_11gR2> create index test_idx
  2  on hclt_parking_lot (search_columns)
  3  indextype is ctxsys.context
  4  parameters
  5    ('datastore test_datastore
  6  	 sync	   (on commit)')
  7  /

Index created.

SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'hclt_parking_lot')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> variable search_string varchar2 (100)
SCOTT@orcl_11gR2> exec :search_string := 'TCK68072'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> set autotrace on explain
SCOTT@orcl_11gR2> select distinct ist.ITEMTYPE as ItemType,
  2  	    ist.ITEMID as ItemId
  3  from   HCLT_PARKING_LOT IST
  4  WHERE  CONTAINS (IST.search_columns, :search_string) > 0
  5  and    ist.ownertype = 'Prj'
  6  and    ist.ownerid   = 76129
  7  and    ist.itemtype  = 'TCK_f'
  8  Order  by ItemId
  9  /

ITEMTYPE               ITEMID
------------------ ----------
TCK_f                       0

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1734504895

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    42 |     6  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                |                  |     1 |    42 |     6  (34)| 00:00:01 |
|   2 |   HASH UNIQUE                 |                  |     1 |    42 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HCLT_PARKING_LOT |     1 |    42 |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX              | TEST_IDX         |       |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("IST"."OWNERID"=76129 AND "IST"."OWNERTYPE"='Prj' AND
              "IST"."ITEMTYPE"='TCK_f')
   4 - access("CTXSYS"."CONTAINS"("IST"."SEARCH_COLUMNS",:SEARCH_STRING)>0)

SCOTT@orcl_11gR2>

Report message to a moderator

Re: query with clob [message #473908 is a reply to message #473890]

Wed, 01 September 2010 09:04

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Thanks a lot, for your support.
one thing more can i add one more table columns in the same proceuer like this

create or replace procedure test_proc
2 (p_rowid in rowid,
3 p_clob in out nocopy clob)
4 as
5 begin
6 for r in
7 (select * from hclt_parking_lot where rowid = p_rowid)
8 loop
9 dbms_lob.append (p_clob, r.cams_notes);
10 dbms_lob.writeappend (p_clob, length (r.cm_description) + 1, ' ' || r.cm_description);
11 dbms_lob.writeappend (p_clob, length (r.user_impact) + 1, ' ' || r.user_impact);
12 -- add other unstrucutred columns to list
13 end loop;
(select * from eformdynamic where rowid = p_rowid)
8 loop
9 dbms_lob.append (p_clob, r.cams_notes);
10 dbms_lob.writeappend (p_clob, length (r.cm_description) + 1, ' ' || r.cm_description);
11 dbms_lob.writeappend (p_clob, length (r.user_impact) + 1, ' ' || r.user_impact);
12 -- add other unstrucutred columns to list
13 end loop;
14 end test_proc;

and suppose if any one of my table got chnages because of dml,then this procedure will call for all record or only for incremental and is there any draw back or limitations of this approach, that i will have to take care before implementing it on production.

Report message to a moderator

Re: query with clob [message #473937 is a reply to message #473908]

Wed, 01 September 2010 10:15

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Once more:
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.

Why are you so reluctant to follow the guide when you are not to request our help?

Regards
Michel

Report message to a moderator

Re: query with clob [message #473972 is a reply to message #473908]

Wed, 01 September 2010 13:10

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

prashant_ora wrote on Wed, 01 September 2010 07:04

can i add one more table columns in the same proceuer like this

Yes, but not the way that you posted. You need to figure out what columns to join the tables on, then include that in the implicit cursor used by the for loop. I don't know what columns they can be joined on, which is master, which is detail, and so on. If you have columns in different tables with the same names, then you will need to provide aliases for them. You should test your query from SQL*Plus first, then use it in your for loop, something like:

select h.*, e.*
from hclt_parking_lot h, eformdynamic e
where
-- h.rowid = p_rowid and -- skip this line while testing from SQL*Plus
h.some_column = p.some_column;

prashant_ora wrote on Wed, 01 September 2010 07:04

suppose if any one of my table got chnages because of dml,then this procedure will call for all record or only for incremental and is there any draw back or limitations of this approach, that i will have to take care before implementing it on production.

You should create a before update row trigger on the search_columns column that the index is created on and make sure that you update that column any time that you do any DML that should affect the test index, so that synchronization will be triggered. You should also periodically optimize and rebuild to avoid index fragmentation.

You can find information and examples on all of these things in the searchable online documentation in the Text Reference and Text Application Deveoper's Guide.

Report message to a moderator

Re: query with clob [message #474002 is a reply to message #473972]

Wed, 01 September 2010 23:53

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Ok.
It means procedure creation would be one time activity and once i create the procedure with this statement(select h.*, e.*
from hclt_parking_lot h, eformdynamic e
where -- h.rowid = p_rowid and
h.some_column = p.some_column) and then i create the index on cm_item_code (this is the column of hclt_parking_lot) and then as you are saying i will have to create one before update trigger on hclt_parking_lot table ,but what code i will wite inside the trigger body, means how my index will synchronised and even some time because of dml one of the column of eformdynamic table got chnaged, so in this case how the complete index will be synchronised because we are creating the triiger on hclt_parking_lot table and will fire this trigger only when cm_item_code will chnage. Please advice.

Report message to a moderator

Re: query with clob [message #474006 is a reply to message #474002]

Thu, 02 September 2010 00:35

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

The trigger should be a before update row trigger. If any of the columns that are included in the indexed search_columns are updated, then that trigger needs to update the column that the index is created on, in order to cause synchronization. It is common to use a separate column of varchar2(1) for that purpose and just set it equal to itself within the tirgger. This column is frequently called a dummy column. There is an example here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5533095920114#42006163435505

[Updated on: Thu, 02 September 2010 00:42]

Report message to a moderator

Re: query with clob [message #474016 is a reply to message #474006]

Thu, 02 September 2010 01:47

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

I done this activity:

create or replace procedure test_proc(p_rowid in rowid,p_clob in out nocopy clob)
as
begin
for r in
(select * from hclt_parking_lot where rowid = p_rowid)
loop
  dbms_lob.append (p_clob, r.cm_itemcode);
--  dbms_lob.writeappend (p_clob, length (r.search_item) + 1, ' ' || r.search_item);
  dbms_lob.writeappend (p_clob, length (r.cm_duedate) + 1, ' ' || r.cm_duedate);
  dbms_lob.writeappend (p_clob, length (r.cm_seqnumber) + 1, ' ' || r.cm_seqnumber);
  dbms_lob.writeappend (p_clob, length (r.cm_name) + 1, ' ' || r.cm_name);
  dbms_lob.writeappend (p_clob, length (r.cm_description) + 1, ' ' || r.cm_description);
  dbms_lob.writeappend (p_clob, length (r.cm_overallstatus) + 1, ' ' || r.cm_overallstatus);
  dbms_lob.writeappend (p_clob, length (r.itemid) + 1, ' ' || r.itemid);
  dbms_lob.writeappend (p_clob, length (r.cams_notes) + 1, ' ' || r.cams_notes);
  dbms_lob.writeappend (p_clob, length (r.itemtype) + 1, ' ' || r.itemtype);
  dbms_lob.writeappend (p_clob, length (r.cm_wbscode) + 1, ' ' || r.cm_wbscode);
  dbms_lob.writeappend (p_clob, length (r.cm_containertype) + 1, ' ' || r.cm_containertype);
  dbms_lob.writeappend (p_clob, length (r.cm_containerid) + 1, ' ' || r.cm_containerid);
  dbms_lob.writeappend (p_clob, length (r.cm_cfmstatus) + 1, ' ' || r.cm_cfmstatus);
  dbms_lob.writeappend (p_clob, length (r.cm_checkincheckoutby) + 1, ' ' || r.cm_checkincheckoutby);
  dbms_lob.writeappend (p_clob, length (r.cm_checkincheckouton) + 1, ' ' || r.cm_checkincheckouton);
  dbms_lob.writeappend (p_clob, length (r.cm_attachmentcount) + 1, ' ' || r.cm_attachmentcount);
  dbms_lob.writeappend (p_clob, length (r.cm_traceabilitycount) + 1, ' ' || r.cm_traceabilitycount);
  dbms_lob.writeappend (p_clob, length (r.cm_currentpartyname) + 1, ' ' || r.cm_currentpartyname);
  dbms_lob.writeappend (p_clob, length (r.cm_currentstagename) + 1, ' ' || r.cm_currentstagename);
  dbms_lob.writeappend (p_clob, length (r.actualeffort) + 1, ' ' || r.actualeffort);
  dbms_lob.writeappend (p_clob, length (r.estimatedeffort) + 1, ' ' || r.estimatedeffort);
  dbms_lob.writeappend (p_clob, length (r.application_level_1) + 1, ' ' || r.application_level_1);
  dbms_lob.writeappend (p_clob, length (r.application_level_2) + 1, ' ' || r.application_level_2);
  dbms_lob.writeappend (p_clob, length (r.business_units) + 1, ' ' || r.business_units);
  dbms_lob.writeappend (p_clob, length (r.cams_id) + 1, ' ' || r.cams_id);
  dbms_lob.writeappend (p_clob, length (r.cams_ticket_type) + 1, ' ' || r.cams_ticket_type);
  dbms_lob.writeappend (p_clob, length (r.category_level_1) + 1, ' ' || r.category_level_1);
  dbms_lob.writeappend (p_clob, length (r.category_level_2) + 1, ' ' || r.category_level_2);
  dbms_lob.writeappend (p_clob, length (r.country) + 1, ' ' || r.country);
  dbms_lob.writeappend (p_clob, length (r.location) + 1, ' ' || r.location);
  dbms_lob.writeappend (p_clob, length (r.mq_comments) + 1, ' ' || r.mq_comments);
  dbms_lob.writeappend (p_clob, length (r.mq_infrastructure) + 1, ' ' || r.mq_infrastructure);
  dbms_lob.writeappend (p_clob, length (r.mq_spg) + 1, ' ' || r.mq_spg);
  dbms_lob.writeappend (p_clob, length (r.pm_smart_ticket_type) + 1, ' ' || r.pm_smart_ticket_type);
  dbms_lob.writeappend (p_clob, length (r.pm_smart_track) + 1, ' ' || r.pm_smart_track);
  dbms_lob.writeappend (p_clob, length (r.severity) + 1, ' ' || r.severity);
  dbms_lob.writeappend (p_clob, length (r.user_requested_end_date) + 1, ' ' || r.user_requested_end_date);
  dbms_lob.writeappend (p_clob, length (r.workflow_assignee) + 1, ' ' || r.workflow_assignee);
  dbms_lob.writeappend (p_clob, length (r.priority) + 1, ' ' || r.priority);
  dbms_lob.writeappend (p_clob, length (r.site) + 1, ' ' || r.site);
  dbms_lob.writeappend (p_clob, length (r.routed) + 1, ' ' || r.routed);
  dbms_lob.writeappend (p_clob, length (r.reopen) + 1, ' ' || r.reopen);
  dbms_lob.writeappend (p_clob, length (r.task_type) + 1, ' ' || r.task_type);
  dbms_lob.writeappend (p_clob, length (r.date_created_in_cams) + 1, ' ' || r.date_created_in_cams);
  dbms_lob.writeappend (p_clob, length (r.subject) + 1, ' ' || r.subject);
  dbms_lob.writeappend (p_clob, length (r.customer_name) + 1, ' ' || r.customer_name);
  dbms_lob.writeappend (p_clob, length (r.customer_phone) + 1, ' ' || r.customer_phone);
  dbms_lob.writeappend (p_clob, length (r.customer_email) + 1, ' ' || r.customer_email);
  dbms_lob.writeappend (p_clob, length (r.customer_guid) + 1, ' ' || r.customer_guid);
  dbms_lob.writeappend (p_clob, length (r.number_of_attachments) + 1, ' ' || r.number_of_attachments);
  dbms_lob.writeappend (p_clob, length (r.date_created_in_pm_smart) + 1, ' ' || r.date_created_in_pm_smart);
  dbms_lob.writeappend (p_clob, length (r.pm_smart_status) + 1, ' ' || r.pm_smart_status);
  dbms_lob.writeappend (p_clob, length (r.cams_status) + 1, ' ' || r.cams_status);
  dbms_lob.writeappend (p_clob, length (r.current_ticket_owner) + 1, ' ' || r.current_ticket_owner);
  dbms_lob.writeappend (p_clob, length (r.service_provider_group) + 1, ' ' || r.service_provider_group);
  dbms_lob.writeappend (p_clob, length (r.user_impact) + 1, ' ' || r.user_impact);
  dbms_lob.writeappend (p_clob, length (r.transaction_date) + 1, ' ' || r.transaction_date);
  dbms_lob.writeappend (p_clob, length (r.enterpriseid) + 1, ' ' || r.enterpriseid);
  dbms_lob.writeappend (p_clob, length (r.ownertype) + 1, ' ' || r.ownertype);
  dbms_lob.writeappend (p_clob, length (r.ownerid) + 1, ' ' || r.ownerid);
  dbms_lob.writeappend (p_clob, length (r.cm_dateidentified) + 1, ' ' || r.cm_dateidentified);
  dbms_lob.writeappend (p_clob, length (r.cm_etvx) + 1, ' ' || r.cm_etvx);
  dbms_lob.writeappend (p_clob, length (r.cm_phaseid) + 1, ' ' || r.cm_phaseid);
  dbms_lob.writeappend (p_clob, length (r.cm_createdby) + 1, ' ' || r.cm_createdby);
  dbms_lob.writeappend (p_clob, length (r.cm_release) + 1, ' ' || r.cm_release);
  dbms_lob.writeappend (p_clob, length (r.cm_priority) + 1, ' ' || r.cm_priority);
  dbms_lob.writeappend (p_clob, length (r.cm_site) + 1, ' ' || r.cm_site);
  dbms_lob.writeappend (p_clob, length (r.cm_dateclosed) + 1, ' ' || r.cm_dateclosed);
  dbms_lob.writeappend (p_clob, length (r.cm_creationdate) + 1, ' ' || r.cm_creationdate);
  dbms_lob.writeappend (p_clob, length (r.cm_accesscontrol) + 1, ' ' || r.cm_accesscontrol);
  -- add other unstrucutred columns to list
end loop;
end test_proc;

begin
ctx_ddl.drop_preference ('test_datastore');
end;

begin
ctx_ddl.create_preference ('test_datastore', 'user_datastore');
ctx_ddl.set_attribute ('test_datastore', 'procedure', 'test_proc');
end;


drop index test_idx

create index test_idx
on hclt_parking_lot ((cm_itemcode))
indextype is ctxsys.context parameters ('datastore test_datastore');

begin
dbms_stats.gather_table_stats(user, 'hclt_parking_lot');
end;

select count(*) from hclt_parking_lot where 
CONTAINS((cm_itemcode),'%TCK%') > 0

but this select count(*) from hclt_parking_lot where
CONTAINS((cm_itemcode),'%TCK%') > 0 query returning 0 records while
select count(*) from hclt_parking_lot where cm_itemcode LIKE '%TCK%' =76044

Report message to a moderator

Re: query with clob [message #474017 is a reply to message #474006]

Thu, 02 September 2010 01:47

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Here is a simple example that first shows that without the triggers the index is not synchronized, then shows that with the triggers the index is synchronized and updated columns in both tables are searchable. Notice that I created a dummy column and created the index on that. You could name your dummy column anything you want instead of dummy. If you use something like search_cols or all_cols instead of dummy, it makes the query make more sense, since it shows that you are searching the search_cols or all_cols instead of searching dummy.

SCOTT@orcl_11gR2> create table table1
  2    ( id		    number,
  3  	 text_data	    clob,
  4  	 dummy		    varchar2 (1))
  5  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into table1 values (1, 'test1', null)
  3  into table1 values (2, 'test2', null)
  4  into table1 values (3, 'test4', null)
  5  select * from dual
  6  /

3 rows created.

SCOTT@orcl_11gR2> create table table2
  2    ( id		    number,
  3  	 text_data	    clob )
  4  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into table2 values (1, 'test3')
  3  into table2 values (2, 'test1')
  4  into table2 values (3, 'test5')
  5  select * from dual
  6  /

3 rows created.

SCOTT@orcl_11gR2> create or replace procedure test_proc
  2    (p_rowid in rowid,
  3  	p_clob	in out nocopy clob)
  4  as
  5  begin
  6    for r in
  7  	 (select t1.text_data as t1_text_data,
  8  		 t2.text_data as t2_text_data
  9  	  from	 table1 t1, table2 t2
 10  	  where  t1.rowid = p_rowid
 11  	  and	 t1.id = t2.id)
 12    loop
 13  	 dbms_lob.append (p_clob, r.t1_text_data);
 14  	 dbms_lob.writeappend (p_clob, 1, ' ');
 15  	 dbms_lob.append (p_clob, r.t2_text_data);
 16    end loop;
 17  end test_proc;
 18  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> 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_11gR2> create index test_idx
  2  on table1 (dummy)
  3  indextype is ctxsys.context
  4  parameters
  5    ('datastore test_datastore
  6  	 sync	   (on commit)')
  7  /

Index created.

SCOTT@orcl_11gR2> variable search_string varchar2 (100)
SCOTT@orcl_11gR2> column t1_text_data format a12
SCOTT@orcl_11gR2> column t2_text_data format a12
SCOTT@orcl_11gR2> exec :search_string := 'test1'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select t1.id as t1_id, t1.text_data as t1_text_data,
  2  	    t2.id as t2_id, t2.text_data as t2_text_data
  3  from   table1 t1, table2 t2
  4  WHERE  CONTAINS (t1.dummy, :search_string) > 0
  5  and    t1.id = t2.id
  6  /

     T1_ID T1_TEXT_DATA      T2_ID T2_TEXT_DATA
---------- ------------ ---------- ------------
         1 test1                 1 test3
         2 test2                 2 test1

2 rows selected.

SCOTT@orcl_11gR2> -- without triggers:
SCOTT@orcl_11gR2> update table1
  2  set    text_data = 'test6'
  3  where  id = 2
  4  /

1 row updated.

SCOTT@orcl_11gR2> update table2
  2  set    text_data = 'test6'
  3  where  id = 1
  4  /

1 row updated.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> exec :search_string := 'test6'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select t1.id as t1_id, t1.text_data as t1_text_data,
  2  	    t2.id as t2_id, t2.text_data as t2_text_data
  3  from   table1 t1, table2 t2
  4  WHERE  CONTAINS (t1.dummy, :search_string) > 0
  5  and    t1.id = t2.id
  6  /

no rows selected

SCOTT@orcl_11gR2> -- with triggers:
SCOTT@orcl_11gR2> create or replace trigger table1_bur
  2    before update on table1
  3    for each row
  4  begin
  5    :new.dummy := :old.dummy;
  6  end table1_bur;
  7  /

Trigger created.

SCOTT@orcl_11gR2> create or replace trigger table2_bur
  2    before update on table2
  3    for each row
  4  begin
  5    update table1
  6    set dummy = dummy
  7    where table1.id = :new.id;
  8  end table2_bur;
  9  /

Trigger created.

SCOTT@orcl_11gR2> update table1
  2  set    text_data = 'test6'
  3  where  id = 2
  4  /

1 row updated.

SCOTT@orcl_11gR2> update table2
  2  set    text_data = 'test6'
  3  where  id = 1
  4  /

1 row updated.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> exec :search_string := 'test6'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select t1.id as t1_id, t1.text_data as t1_text_data,
  2  	    t2.id as t2_id, t2.text_data as t2_text_data
  3  from   table1 t1, table2 t2
  4  WHERE  CONTAINS (t1.dummy, :search_string) > 0
  5  and    t1.id = t2.id
  6  /

     T1_ID T1_TEXT_DATA      T2_ID T2_TEXT_DATA
---------- ------------ ---------- ------------
         1 test1                 1 test6
         2 test6                 2 test1

2 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: query with clob [message #474023 is a reply to message #474016]

Thu, 02 September 2010 02:01

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

It looks like we were posting at the same time. I don't see anything wrong with what you posted, except that you left off the / after each pl/sql block. Please post a copy and paste of a run of the code from SQL*Plus with results and line numbers as I did.

Report message to a moderator

Re: query with clob [message #474027 is a reply to message #474023]

Thu, 02 September 2010 02:17

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I tried running your code with just the first few columns in the procedure, since I don't have your table structure, and it worked fine for me, as shown below.

SCOTT@orcl_11gR2> create table hclt_parking_lot
  2    ( cm_itemcode	 clob
  3    , cm_duedate	 date
  4    , cm_seqnumber	 number
  5    , cm_name	 varchar2 (18)
  6    , cm_description  varchar2 (30)
  7    )
  8  /

Table created.

SCOTT@orcl_11gR2> insert into hclt_parking_lot values
  2  ('word1 TCK68072 word2', sysdate, 1, 'TCK_f', 'word3 TCK68072 word4')
  3  /

1 row created.

SCOTT@orcl_11gR2> create or replace procedure test_proc(p_rowid in rowid,p_clob in out nocopy clob)
  2  as
  3  begin
  4  for r in
  5  (select * from hclt_parking_lot where rowid = p_rowid)
  6  loop
  7    dbms_lob.append (p_clob, r.cm_itemcode);
  8  --  dbms_lob.writeappend (p_clob, length (r.search_item) + 1, ' ' || r.search_item);
  9    dbms_lob.writeappend (p_clob, length (r.cm_duedate) + 1, ' ' || r.cm_duedate);
 10    dbms_lob.writeappend (p_clob, length (r.cm_seqnumber) + 1, ' ' || r.cm_seqnumber);
 11    dbms_lob.writeappend (p_clob, length (r.cm_name) + 1, ' ' || r.cm_name);
 12    dbms_lob.writeappend (p_clob, length (r.cm_description) + 1, ' ' || r.cm_description);
 13  /*
 14    dbms_lob.writeappend (p_clob, length (r.cm_overallstatus) + 1, ' ' || r.cm_overallstatus);
 15    dbms_lob.writeappend (p_clob, length (r.itemid) + 1, ' ' || r.itemid);
 16    dbms_lob.writeappend (p_clob, length (r.cams_notes) + 1, ' ' || r.cams_notes);
 17    dbms_lob.writeappend (p_clob, length (r.itemtype) + 1, ' ' || r.itemtype);
 18    dbms_lob.writeappend (p_clob, length (r.cm_wbscode) + 1, ' ' || r.cm_wbscode);
 19    dbms_lob.writeappend (p_clob, length (r.cm_containertype) + 1, ' ' || r.cm_containertype);
 20    dbms_lob.writeappend (p_clob, length (r.cm_containerid) + 1, ' ' || r.cm_containerid);
 21    dbms_lob.writeappend (p_clob, length (r.cm_cfmstatus) + 1, ' ' || r.cm_cfmstatus);
 22    dbms_lob.writeappend (p_clob, length (r.cm_checkincheckoutby) + 1, ' ' || r.cm_checkincheckoutby);
 23    dbms_lob.writeappend (p_clob, length (r.cm_checkincheckouton) + 1, ' ' || r.cm_checkincheckouton);
 24    dbms_lob.writeappend (p_clob, length (r.cm_attachmentcount) + 1, ' ' || r.cm_attachmentcount);
 25    dbms_lob.writeappend (p_clob, length (r.cm_traceabilitycount) + 1, ' ' || r.cm_traceabilitycount);
 26    dbms_lob.writeappend (p_clob, length (r.cm_currentpartyname) + 1, ' ' || r.cm_currentpartyname);
 27    dbms_lob.writeappend (p_clob, length (r.cm_currentstagename) + 1, ' ' || r.cm_currentstagename);
 28    dbms_lob.writeappend (p_clob, length (r.actualeffort) + 1, ' ' || r.actualeffort);
 29    dbms_lob.writeappend (p_clob, length (r.estimatedeffort) + 1, ' ' || r.estimatedeffort);
 30    dbms_lob.writeappend (p_clob, length (r.application_level_1) + 1, ' ' || r.application_level_1);
 31    dbms_lob.writeappend (p_clob, length (r.application_level_2) + 1, ' ' || r.application_level_2);
 32    dbms_lob.writeappend (p_clob, length (r.business_units) + 1, ' ' || r.business_units);
 33    dbms_lob.writeappend (p_clob, length (r.cams_id) + 1, ' ' || r.cams_id);
 34    dbms_lob.writeappend (p_clob, length (r.cams_ticket_type) + 1, ' ' || r.cams_ticket_type);
 35    dbms_lob.writeappend (p_clob, length (r.category_level_1) + 1, ' ' || r.category_level_1);
 36    dbms_lob.writeappend (p_clob, length (r.category_level_2) + 1, ' ' || r.category_level_2);
 37    dbms_lob.writeappend (p_clob, length (r.country) + 1, ' ' || r.country);
 38    dbms_lob.writeappend (p_clob, length (r.location) + 1, ' ' || r.location);
 39    dbms_lob.writeappend (p_clob, length (r.mq_comments) + 1, ' ' || r.mq_comments);
 40    dbms_lob.writeappend (p_clob, length (r.mq_infrastructure) + 1, ' ' || r.mq_infrastructure);
 41    dbms_lob.writeappend (p_clob, length (r.mq_spg) + 1, ' ' || r.mq_spg);
 42    dbms_lob.writeappend (p_clob, length (r.pm_smart_ticket_type) + 1, ' ' || r.pm_smart_ticket_type);
 43    dbms_lob.writeappend (p_clob, length (r.pm_smart_track) + 1, ' ' || r.pm_smart_track);
 44    dbms_lob.writeappend (p_clob, length (r.severity) + 1, ' ' || r.severity);
 45    dbms_lob.writeappend (p_clob, length (r.user_requested_end_date) + 1, ' ' || r.user_requested_end_date);
 46    dbms_lob.writeappend (p_clob, length (r.workflow_assignee) + 1, ' ' || r.workflow_assignee);
 47    dbms_lob.writeappend (p_clob, length (r.priority) + 1, ' ' || r.priority);
 48    dbms_lob.writeappend (p_clob, length (r.site) + 1, ' ' || r.site);
 49    dbms_lob.writeappend (p_clob, length (r.routed) + 1, ' ' || r.routed);
 50    dbms_lob.writeappend (p_clob, length (r.reopen) + 1, ' ' || r.reopen);
 51    dbms_lob.writeappend (p_clob, length (r.task_type) + 1, ' ' || r.task_type);
 52    dbms_lob.writeappend (p_clob, length (r.date_created_in_cams) + 1, ' ' || r.date_created_in_cams);
 53    dbms_lob.writeappend (p_clob, length (r.subject) + 1, ' ' || r.subject);
 54    dbms_lob.writeappend (p_clob, length (r.customer_name) + 1, ' ' || r.customer_name);
 55    dbms_lob.writeappend (p_clob, length (r.customer_phone) + 1, ' ' || r.customer_phone);
 56    dbms_lob.writeappend (p_clob, length (r.customer_email) + 1, ' ' || r.customer_email);
 57    dbms_lob.writeappend (p_clob, length (r.customer_guid) + 1, ' ' || r.customer_guid);
 58    dbms_lob.writeappend (p_clob, length (r.number_of_attachments) + 1, ' ' || r.number_of_attachments);
 59    dbms_lob.writeappend (p_clob, length (r.date_created_in_pm_smart) + 1, ' ' || r.date_created_in_pm_smart);
 60    dbms_lob.writeappend (p_clob, length (r.pm_smart_status) + 1, ' ' || r.pm_smart_status);
 61    dbms_lob.writeappend (p_clob, length (r.cams_status) + 1, ' ' || r.cams_status);
 62    dbms_lob.writeappend (p_clob, length (r.current_ticket_owner) + 1, ' ' || r.current_ticket_owner);
 63    dbms_lob.writeappend (p_clob, length (r.service_provider_group) + 1, ' ' || r.service_provider_group);
 64    dbms_lob.writeappend (p_clob, length (r.user_impact) + 1, ' ' || r.user_impact);
 65    dbms_lob.writeappend (p_clob, length (r.transaction_date) + 1, ' ' || r.transaction_date);
 66    dbms_lob.writeappend (p_clob, length (r.enterpriseid) + 1, ' ' || r.enterpriseid);
 67    dbms_lob.writeappend (p_clob, length (r.ownertype) + 1, ' ' || r.ownertype);
 68    dbms_lob.writeappend (p_clob, length (r.ownerid) + 1, ' ' || r.ownerid);
 69    dbms_lob.writeappend (p_clob, length (r.cm_dateidentified) + 1, ' ' || r.cm_dateidentified);
 70    dbms_lob.writeappend (p_clob, length (r.cm_etvx) + 1, ' ' || r.cm_etvx);
 71    dbms_lob.writeappend (p_clob, length (r.cm_phaseid) + 1, ' ' || r.cm_phaseid);
 72    dbms_lob.writeappend (p_clob, length (r.cm_createdby) + 1, ' ' || r.cm_createdby);
 73    dbms_lob.writeappend (p_clob, length (r.cm_release) + 1, ' ' || r.cm_release);
 74    dbms_lob.writeappend (p_clob, length (r.cm_priority) + 1, ' ' || r.cm_priority);
 75    dbms_lob.writeappend (p_clob, length (r.cm_site) + 1, ' ' || r.cm_site);
 76    dbms_lob.writeappend (p_clob, length (r.cm_dateclosed) + 1, ' ' || r.cm_dateclosed);
 77    dbms_lob.writeappend (p_clob, length (r.cm_creationdate) + 1, ' ' || r.cm_creationdate);
 78    dbms_lob.writeappend (p_clob, length (r.cm_accesscontrol) + 1, ' ' || r.cm_accesscontrol);
 79  */
 80  end loop;
 81  end test_proc;
 82  /

Procedure created.

SCOTT@orcl_11gR2> begin
  2  ctx_ddl.drop_preference ('test_datastore');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: test_datastore
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 40
ORA-06512: at line 2


SCOTT@orcl_11gR2> 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_11gR2> 
SCOTT@orcl_11gR2> drop index test_idx
  2  /
drop index test_idx
           *
ERROR at line 1:
ORA-01418: specified index does not exist


SCOTT@orcl_11gR2> create index test_idx
  2  on hclt_parking_lot ((cm_itemcode))
  3  indextype is ctxsys.context parameters ('datastore test_datastore');

Index created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> begin
  2  dbms_stats.gather_table_stats(user, 'hclt_parking_lot');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> variable search_string varchar2 (100)
SCOTT@orcl_11gR2> select count(*) from hclt_parking_lot where
  2  CONTAINS((cm_itemcode),'%TCK%') > 0
  3  /

  COUNT(*)
----------
         1

1 row selected.

SCOTT@orcl_11gR2> select count(*) from hclt_parking_lot where cm_itemcode LIKE '%TCK%'
  2  /

  COUNT(*)
----------
         1

1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: query with clob [message #485280 is a reply to message #473817]

Tue, 07 December 2010 04:18

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Hi Barbara ,

Its working very fine with 'multi_column_datastore', but how can i specify the tablespace name with these clause. Could you please help me out. if I am not putting any tablespace name, its creating in system tablespace.

Thanks
Prashant

begin
ctx_ddl.create_preference ('test_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute
('test_datastore',
'columns',
'cams_notes, cm_description, user_impact'); -- add other unstrucutred columns to list
end;
/

create index test_idx
on hclt_parking_lot (search_columns)
indextype is ctxsys.context
parameters ('datastore test_datastore')

Report message to a moderator

Re: query with clob [message #485335 is a reply to message #485280]

Tue, 07 December 2010 11:28

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Click on the link for information and syntax for storage:

http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cdatadic.htm#sthref809

example:

SCOTT@orcl_11gR2> create table test_tab (test_col  clob)
  2  /

Table created.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
  3    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
  4  			       'tablespace users storage (initial 1K)');
  5    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
  6  			       'tablespace users storage (initial 1K)');
  7    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
  8  			       'tablespace users storage (initial 1K) lob
  9  				(data) store as (disable storage in row cache)');
 10    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
 11  			       'tablespace users storage (initial 1K)');
 12    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
 13  			       'tablespace users storage (initial 1K) compress 2');
 14    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
 15  			       'tablespace users storage (initial 1K)');
 16  end;
 17  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index test_idx
  2  on test_tab (test_col)
  3  indextype is ctxsys.context
  4  parameters
  5    ('storage  mystore')
  6  /

Index created.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: query with clob [message #485412 is a reply to message #485335]

Tue, 07 December 2010 22:56

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Thanks.

But I am not able to understand how can in corporate those chnages with my existing code which is like

begin
ctx_ddl.create_preference ('test_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute
('test_datastore',
'columns',
'cams_notes, cm_description, user_impact'); -- add other unstrucutred columns to list
end;
/

create index test_idx
on hclt_parking_lot (search_columns)
indextype is ctxsys.context
parameters ('datastore test_datastore')

So how can i create the other preferences with 'BASIC_STORAGE' clause and incorpoarte with multi_colulm_datastore. Could you please tell me considering my existing code.

Report message to a moderator

Re: query with clob [message #485467 is a reply to message #485412]

Wed, 08 December 2010 06:25

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> create table hclt_parking_lot
  2    (cams_notes	varchar2(15),
  3  	cm_description	varchar2(15),
  4  	user_impact	varchar2(15),
  5  	search_columns	varchar2(1))
  6  /

Table created.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_preference ('test_datastore', 'multi_column_datastore');
  3    ctx_ddl.set_attribute
  4  	 ('test_datastore',
  5  	  'columns',
  6  	  'cams_notes, cm_description, user_impact');
  7    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
  8    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
  9  			       'tablespace users storage (initial 1K)');
 10    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
 11  			       'tablespace users storage (initial 1K)');
 12    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
 13  			       'tablespace users storage (initial 1K) lob
 14  				(data) store as (disable storage in row cache)');
 15    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
 16  			       'tablespace users storage (initial 1K)');
 17    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
 18  			       'tablespace users storage (initial 1K) compress 2');
 19    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
 20  			       'tablespace users storage (initial 1K)');
 21  end;
 22  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index test_idx
  2  on hclt_parking_lot (search_columns)
  3  indextype is ctxsys.context
  4  parameters
  5    ('datastore  test_datastore
  6  	 storage    mystore')
  7  /

Index created.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: query with clob [message #485469 is a reply to message #485467]

Wed, 08 December 2010 06:51

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Thanks.

I done this

begin
ctx_ddl.create_preference ('hpls_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute('hpls_datastore','columns','cams_notes,search_item');
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE','tablespace digite_index storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace digite_index storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace digite_index storage (initial 1K) lob(data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace digite_index storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace digite_index storage (initial 1K) compress 2');
ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE','tablespace digite_index storage (initial 1K)');
end;
/

create index idx_parking_lot_search
on hclt_parking_lot (search_item)
indextype is ctxsys.context
parameters ('datastore hpls_datastore
storage mystore
sync(on commit)')

But when I am runing this query
select * FROM user_indexes where index_name='IDX_PARKING_LOT_SEARCH'
then the tablesapce column is coming null, why its not showing the tablespace name, pl advice.

Report message to a moderator

Re: query with clob [message #485485 is a reply to message #485469]

Wed, 08 December 2010 09:17

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Oracle Text domain indexes are not like regular indexes. The index consists of four domain index tables:

dr$your_index_name$i
dr$your_index_name$k
dr$your_index_name$r
dr$your_index_name$n

So, you should be looking for the tablespace_name's that correspond to those tables, as shown below.

SCOTT@orcl_11gR2> create table hclt_parking_lot
  2    (cams_notes	varchar2(15),
  3  	cm_description	varchar2(15),
  4  	user_impact	varchar2(15),
  5  	search_item  varchar2(1))
  6  /

Table created.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_preference ('hpls_datastore', 'multi_column_datastore');
  3    ctx_ddl.set_attribute
  4  	 ('hpls_datastore',
  5  	  'columns',
  6  	  'cams_notes, cm_description, user_impact');
  7    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
  8    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
  9  			       'tablespace users storage (initial 1K)');
 10    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
 11  			       'tablespace users storage (initial 1K)');
 12    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
 13  			       'tablespace users storage (initial 1K) lob
 14  				(data) store as (disable storage in row cache)');
 15    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
 16  			       'tablespace users storage (initial 1K)');
 17    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
 18  			       'tablespace users storage (initial 1K) compress 2');
 19    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
 20  			       'tablespace users storage (initial 1K)');
 21  end;
 22  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index idx_parking_lot_search
  2  on hclt_parking_lot (search_item)
  3  indextype is ctxsys.context
  4  parameters
  5    ('datastore  hpls_datastore
  6  	 storage    mystore
  7  	 sync	    (on commit)')
  8  /

Index created.

SCOTT@orcl_11gR2> select index_name, tablespace_name
  2  FROM   user_indexes
  3  where  index_name='IDX_PARKING_LOT_SEARCH'
  4  /

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
IDX_PARKING_LOT_SEARCH

1 row selected.

SCOTT@orcl_11gR2> select table_name, tablespace_name
  2  from   user_tables
  3  where  table_name like 'DR$IDX_PARKING_LOT_SEARCH$%'
  4  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DR$IDX_PARKING_LOT_SEARCH$R    USERS
DR$IDX_PARKING_LOT_SEARCH$I    USERS
DR$IDX_PARKING_LOT_SEARCH$N
DR$IDX_PARKING_LOT_SEARCH$K

4 rows selected.

SCOTT@orcl_11gR2>

For more information, see:

http://www.oracle.com/technetwork/database/enterprise-edition/text-dml-processing-092316.html#P3

Report message to a moderator

Re: query with clob [message #505087 is a reply to message #485485]

Fri, 29 April 2011 04:57

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Hi,

While performing the search using domain index i am getting this error. Could you please advice why this error is coming and how can i solve it.

select itemtype,itemid
FROM hclt_hm_defect_eform
where CONTAINS(search_item,'%Request%', 1) > 0;

ORA-29902:error in executing
ODCIIndexStart() routine

ora-20000 oracle text error
DRG-51030 wildcard query expansion resulted in too many ITEMS

Report message to a moderator

Re: query with clob [message #505095 is a reply to message #505087]

Fri, 29 April 2011 05:40

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

prashant_ora wrote on Fri, 29 April 2011 02:57

While performing the search using domain index i am getting this error. Could you please advice why this error is coming and how can i solve it.

select itemtype,itemid
FROM hclt_hm_defect_eform
where CONTAINS(search_item,'%Request%', 1) > 0;

ORA-29902:error in executing
ODCIIndexStart() routine

ora-20000 oracle text error
DRG-51030 wildcard query expansion resulted in too many ITEMS

It means that there are too many tokens that have "Request" within them. You can increase the wildcard_maxterms, but that may not be sufficient and may cause your queries to run slower. You need to use some criteria more specific than "%Request%". If you are just searching for the word "Request" within the search_item, then you do not need the % wildcards and that may solve the problem. Oracle Text is based on tokens, so you do not need to use wildcards as you would with LIKE in order to find a word within a string.

Report message to a moderator

Re: query with clob [message #505096 is a reply to message #505095]

Fri, 29 April 2011 05:44

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Actually I have to find all the records where data is matching with "Request", like in sql query

select * from table_name where text like '%Request%'

Report message to a moderator

Re: query with clob [message #505100 is a reply to message #505096]

Fri, 29 April 2011 06:24

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

prashant_ora wrote on Fri, 29 April 2011 03:44

Actually I have to find all the records where data is matching with "Request", like in sql query

select * from table_name where text like '%Request%'

Then you do not need the % wildcards. It should be just:

select itemtype,itemid
FROM   hclt_hm_defect_eform
where  CONTAINS (search_item, 'Request') > 0;

Report message to a moderator

Re: query with clob [message #505102 is a reply to message #505100]

Fri, 29 April 2011 06:37

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

This is the string in which i am trying to find "Request" but not getting using

select itemtype,itemid
FROM hclt_hm_defect_eform
where CONTAINS(search_item,'Service') > 0

while when i use this then it returns data

select itemtype,itemid
FROM hclt_hm_defect_eform
where CONTAINS(search_item,'%Service%') > 0

Fon-- JDE ASIA ONE-Urgent Data Patch to fix integrity issues in SLOn March 10, 2009 at 3--09 PM, "Ravi Krishna" <Ravi.Krishna@fonterra.com> wrote-- Help Desk, Please assign this to be done ASAP as today is a holiday in SL and the item balances are static and good time for fixing integrity issues. Each line will update one record. Thanks and Best Regards, K Ravi +919003025857 (Chennai) +94778009413 (Sri Lanka) Closed976204SRZ14391SRZ_fIn10994610-MAR-09228639772286419922849785Case228497782564519622849904ravi.krishna@fonterra.com659113869910-MAR-09-1NN1ClosedService RequestMedium-1976072TCK_f76129PrjTCK20589Completeddone and informed user accordingly. customer confirmedKRISHNARRavi KrishnaLOCK_10-Mar-2009 13:06:208220927109946Open228498622735495510001Prj7612910-MAR-0910994610-MAR-0910-MAR-09114391

Report message to a moderator

Re: query with clob [message #505128 is a reply to message #505102]

Fri, 29 April 2011 11:17

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You are not going to find "Request" by searching for 'Service'. The other problem is that there is a space missing between "Service" and "Medium", so you would need to search for 'Request%' to find "RequestMedium". The more wildcards that you can eliminate the better. Leading wildcards, in particular, tend to slow down queries. You may still have a problem with there being too many tokens that begin with "Request", in which case you need to either increase the wildcard_maxterms parameter or add some additional criteria to your search.

Report message to a moderator

Previous Topic:	Oracle Text Index using Soundex
Next Topic:	Error trying to index a PDF file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 23:39:30 CST 2024