This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,056 Users
  • 2,269,775 Discussions


Oracle Text como comenzar

e:// Member Posts: 45
edited Apr 23, 2010 8:31AM in Text
- Como comenzar a usar Oracle text?
- Que es lo primero que debo saber?
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
- How to start using Oracle text?
- What is the first thing I should know?

Best Answer

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    Answer ✓
    If you want to search for special characters, then you have to do both. You have to declare special characters as printjoins, so that they are tokenized and indexed and you have to enclose the special characters in {} to search for them, so that Oracle treats them as characters to search for instead of their special meanings.


  • Herald ten Dam
    Herald ten Dam Member Posts: 1,254

    start reading the manuals. There are 2:
    1: Text Application Developer's Guide, see
    2: Text Reference, see

    I gave you the 10.2 manuals, but they also exist for 11.1 and 11.2. But the basics for text are the same for all versions.

    Herald ten Dam
  • e://
    e:// Member Posts: 45
    I index my table_X :


    and then
    run the search query:

    SELECT column_X
    FROM table_x
    where CONTAINS( column, 'ZZZ') >= 0;

    This is enough?
  • Herald ten Dam
    Herald ten Dam Member Posts: 1,254

    the syntax is correct if you use contains(column_x,'ZZZ') > 0 and give the index a name.

    You have to look at the synch mechanisme for a context index. Normally if you insert in the table table_X, Oracle will not synchronize the index on commit. You can make this work in 2 manners:
    1. You make a job and call for example every hour DBCTX_DDL.Sync_Index.
    2. You include in your index a Synch clause:
    CREATE INDEX TEST_TXT_INDEX ON table_X (column_X) INDEXTYPE IS CTXSYS.CONTEXT parameters ('sync (on commit)');
    This example will synch the index on a commit.

    Herald ten Dam
  • e://
    e:// Member Posts: 45
    then I need to consider synchronizing my table so that the new content is indexed..
  • Herald ten Dam
    Herald ten Dam Member Posts: 1,254

    you have to consider the synchronization of the index. If you add a record to the table, when is it necessary to see the newly added data in the index. Do you need it when it is committed (directly) or can you wait to see it, for example an hour later? Synchronizing the index will put the data of the column from the table in the index.

    The penalty for "synch on commit" is that a insert/update on the table has to wait for the index to synchronize.

    Herald ten Dam
  • e://
    e:// Member Posts: 45

    I understand!

    3 Tips important
    . Index table with (CTXCAT - CONTEXT etc...)
    . Synchronize on the Insert / Update
    . And run the query

    What type of indexing should I use for blob columns ??
    Some examples?
  • Herald ten Dam
    Herald ten Dam Member Posts: 1,254

    for blob columns use the CONTEXT, CTXCAT is more used for short text and for mixed indexing, the use of a text field and a other normal database field.
    For examples see the hereabove mentioned documentation. All your questions are also answered in these manuals. It is definitily the best start to learn TEXT. Further you have on Technet some examples, see The first item gives also an introduction to TEXT.


    Herald ten Dam
  • e://
    e:// Member Posts: 45
    Hi !!

    Indexing a blob field I, now search the contents of those documents.

    I have to look for 2 columns:

    The dog is wonderful---------the_dog1.pdf
    The dog is dog------------------the_dog2.pdf
    The dog---------------------------the_dog3.pdf
    The cat-----------------------------the_cat.pdf



    select TITLE, SCORE(1) s
    from IDX_BOOKS
    where contains (TITLE, :P9999_SEARCH,1) > 0
    order by s desc;

    SEARCH DOC (blob):

    select TITLE, SCORE(1) s
    from IDX_BOOKS
    where contains (DOC, :P9999_SEARCH,1) > 0
    order by s desc;

    as mix 2 query in 1 query??

    I have to use CATSEARCH?
  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    You can use a multi_column_datastore, as demonstrated below.
    [email protected]_11g> CREATE TABLE idx_books
      2    (title	      VARCHAR2 (30),
      3  	doc	      BLOB,
      4  	title_or_doc  VARCHAR2 (1))
      5  /
    Table created.
    [email protected]_11g> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle11g'
      2  /
    Directory created.
    [email protected]_11g> DECLARE
      2    v_blob	BLOB;
      3    v_bfile	BFILE;
      4  BEGIN
      5    INSERT INTO idx_books (title, doc)
      6  	 VALUES ('The dog is wonderful', EMPTY_BLOB())
      7  	 RETURNING doc INTO v_blob;
      8    v_bfile := BFILENAME ('MY_DIR', 'the_dog.pdf');
      9    DBMS_LOB.OPEN (v_bfile, DBMS_LOB.LOB_READONLY);
     11  	 (v_blob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
     12    DBMS_LOB.CLOSE (v_bfile);
     13    --
     14    INSERT INTO idx_books (title, doc)
     15  	 VALUES ('The cat', EMPTY_BLOB())
     16  	 RETURNING doc INTO v_blob;
     17    v_bfile := BFILENAME ('MY_DIR', 'the_cat.pdf');
     18    DBMS_LOB.OPEN (v_bfile, DBMS_LOB.LOB_READONLY);
     20  	 (v_blob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
     21    DBMS_LOB.CLOSE (v_bfile);
     22  END;
     23  /
    PL/SQL procedure successfully completed.
    [email protected]_11g> BEGIN
      3  	 ('your_datastore', 'MULTI_COLUMN_DATASTORE');
      5  	 ('your_datastore', 'COLUMNS', 'TITLE, DOC');
      6  END;
      7  /
    PL/SQL procedure successfully completed.
    [email protected]_11g> CREATE INDEX book_index
      2  ON idx_books (title_or_doc)
      4  PARAMETERS ('DATASTORE your_datastore')
      5  /
    Index created.
    [email protected]_11g> VARIABLE p9999_search VARCHAR2(30)
    [email protected]_11g> EXEC :p9999_search := 'dog'
    PL/SQL procedure successfully completed.
    [email protected]_11g> select TITLE, SCORE(1) s
      2  from   IDX_BOOKS
      3  where  contains (title_or_doc, :P9999_SEARCH, 1) > 0
      4  order  by s desc
      5  /
    TITLE                                   S
    ------------------------------ ----------
    The dog is wonderful                    4
    [email protected]_11g> 
  • e://
    e:// Member Posts: 45
    what the use is title_or_doc column ??
This discussion has been closed.