Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Roland.

Asked: May 24, 2000 - 6:02 am UTC

Last updated: November 23, 2010 - 3:41 pm UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Hello,

When you use CREATE INDEX without explicitly specifying parameters, the system assumes that the text to be indexed is stored directly in a text column. The text column can be of type CLOB, BLOB, BFILE, VARCHAR2, or CHAR. The columns types LONG and LONG RAW are supported for migrating Oracle7 systems to Oracle8i. The column types NCLOB, DATE and NUMBER cannot be indexed

I am storing my data in a BLOB format in a table:

SQL> descr attachment
Name Null? Type
----------------------------- -------- --------------
ID NOT NULL NUMBER(38)
NAME NOT NULL VARCHAR2(100)
DATA NOT NULL BLOB
DATE_CREATED NOT NULL DATE
DATE_MODIFIED NOT NULL DATE
MODIFIED_BY NOT NULL VARCHAR2(30)
CREATED_BY NOT NULL VARCHAR2(30)


In order to search content within the BLOB I create an index:

CREATE index myindex on attachment(DATA) indextype is ctxsys.context;

I have a sql file uploaded in the BLOB which is the sreate script for the table.

When I search for a word in the BLOB I always retrieve 0 rows, even though the word is in the file.

SQL> SELECT ID from attachment
2 where contains(
3 DATA,'create')
4 >0;

no rows selected

How must I index a BLOB?



and Tom said...

By default, if you do not explicity start the ctxsrv process, indexes are updated only when you tell them to be. Intermedia text queues updates to the index and performs them in batch on demand (using the alter index <iname> rebuild parameters ('sync') command).

If you run the ctxsrv process alongside the server, these batched requests are processed in much closer to real time. They are processed after you commit and may take a second or two to occur but the data is almost immediately indexed.

Here is an example I did against a server that was not running the ctxsrv process. It shows that the data "appears" to not be there until we rebuild and then demonstrates the behaviour you can expect if the ctxsrv process is running. It behaves the same regardless of the datatype of the text column -- since blobs are hard to work with when using just sqlplus and static sql, I am using a CLOB but the same would apply for a BLOB.


ops$tkyte@dev8i> create table ctx_demo
2 ( id number primary key,
3 text clob
4 )
5 /
Table created.

ops$tkyte@dev8i> create index search_idx
2 on ctx_demo(text)
3 indextype is ctxsys.context
4 /
Index created.

ops$tkyte@dev8i> insert into ctx_demo ( id, text )
2 values ( 1, 'How Now Brown Cow' );
1 row created.

ops$tkyte@dev8i> commit;
Commit complete.

ops$tkyte@dev8i> select *
2 from ctx_demo
3 where contains( text, 'Now' ) > 0
4 /

no rows selected

That shows the newly inserted data is not indexed. In fact it will not be indexed until we either sync the index or start the ctxsrv process. The following demonstrates how to sync the index manually.

ops$tkyte@dev8i> alter index search_idx
2 rebuild parameters( 'sync' )
3 /
Index altered.

ops$tkyte@dev8i> select *
2 from ctx_demo
3 where contains( text, 'Now' ) > 0
4 /

ID TEXT
---------- ----------------------------------------
1 How Now Brown Cow


Now we can query the data. Many people schedule their indexes to sync in the background using DBMS_JOBS. In this fashion, they batch the updates to the table for some period (say 1 hour) and sync the index hourly. Others will run the ctxsrv process as below (for example, I run the ctxsrv process on osi.oracle.com so that articles in this forum are indexed as soon as I publish them) for more immediate indexing.

ops$tkyte@dev8i> host ctxsrv -user ctxsys/ctxsys>&/tmp/ctx.log&

ops$tkyte@dev8i> insert into ctx_demo (id, text)
2 values (2, 'The fork ran away with the spoon');
1 row created.

ops$tkyte@dev8i> commit;
Commit complete.

ops$tkyte@dev8i> exec dbms_lock.sleep(10)
PL/SQL procedure successfully completed.

I must wait a bit to give interMedia text the time to get the request, retrieve the document, parse it and update the index. This time varies but will only be a few seconds at most for such small documents.

ops$tkyte@dev8i> select *
2 from ctx_demo
3 where contains( text, 'spoon' ) > 0
4 /

ID TEXT
---------- ----------------------------------------
2 The fork ran away with the spoon

So now I can query up the newly inserted document without doing a "sync"


It should be noted that if you run the ctxsrv process and index individual documents, the indexes will be generally "worst case" indexes. interMedia text uses the concept of an inverted list index -- a word points to many documents. If we index individual documents, our index will become very sparse over time as each word will point to a single document and the words will occur many times in the index. We will have to schedule an online "optimization" of these indexes periodically to reduce their space and level of fragmentation.

Rating

  (35 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to defrag

Rukshan Soza, April 30, 2002 - 8:11 am UTC

Tom,

I read your response with great interest. We are running ctxsrv background process. But, according to your article with this method, it is likely that fragmentation will occur. How can I periodically, defrag/optimise these indexes ?

Thanks in advance

Rukshan

Tom Kyte
April 30, 2002 - 11:32 am UTC

You can rebuild them if you find it necessary. (see the intermedia guide, the process is well documented)

Make sure to gather METRICS before and after doing this -- so you can see if the rebuild did anything positive or negative with regards to performance and size. Only rebuild in the future if you see POSITIVE payback.

ctxsrv deprecated

Wynette Richards, April 30, 2002 - 12:53 pm UTC

Tom, When working with Oracle on a TAR about a year ago we were told that ctxsrv has been deprecated and that we should not use it. They said we should not call "alter index ... rebuild ... ('sync')" but, instead, that we should only do a sync by calling ctx_ddl.sync_index('index_name'). This is discussed in </code> http://technet.oracle.com/products/text/x/Tech_Overviews/imt_816.html <code>

I was surprised to see you suggest using ctxsrv. Has anything changed in the past year?

Thanks for your outstanding web site. Thanks for the explanation re. need to run optimize.


Tom Kyte
April 30, 2002 - 2:11 pm UTC

The quote is:

<quote>
PL/SQL Sync and Optimize

PL/SQL functions for sync and optimize have been added. The preferred method for sync and optimize is alter index rebuild online. Ideally, these could be called in a dbms_job. However, separate sync and optimize jobs can conflict because two alter index operations cannot be run at the same time for a single index.

The new PL/SQL functions resolve both problems by proving an alternate PL/SQL API for sync and optimize:

ctx_ddl.sync_index(<idx_name>)

idx_name is the name of the index to sync. The effective user must be the owner of the index or ctxsys. If effective user is ctxsys, idx_name can be NULL, which will cause this call to examine all context indexes for pending DML. This uses the same code as ctxsrv. We now recommend processing pending DML through a dbms_job -- an individual job for each index, a ctxsys job for all indexes, or some combination of the two. This eliminates the need for ctxsrv, and thus eliminates the problem of starting ctxsrv each time the database starts. The ctxsrv program itself is now deprecated, and may be removed in some future release.
</quote>

It is still there in 9i.

You know, I read and re-read and read again my answer. No where do I "suggest running ctxsrv as being best practice"


I do suggest that it can lead to fragmented indexes.
That many people use dbms_job.
That you can use ctxsrv (and it is there, it is supported -- even in 9i)....


Additionally -- this question was against version 815 -- there was no ctx_ddl.sync_index call in that release (always look at the version in the subject for version info - i don't update questions just because of version changes!)

vs

Scott, December 16, 2002 - 2:41 am UTC

G'day

This information is fantastic, thankyou.

In the current release, and/or 817, would you suggest using ctxsrv or sync index with the occasional optimise?

The reason I ask is because one wonders what the optimal period is for the DBMS_JOBs.
How labour intensive is a synchronisation of all ctx indexes on a production database (using ctx_ddl.sync_index(NULL))?
Or does ctxsrv use similar decision making?

Tom Kyte
December 16, 2002 - 7:56 am UTC

On asktom -- I use ctxsrv so the index is "up to date" immediately after I publish a question or a review is added by you or whatever...

I perdiodically rebuild the index when I notice the "approximately so many hits" gets way off on the search page (dead entries from modified documents in the index).

You would have to benchmark using YOUR scenario (your "production" system might be less loaded them my "development" system. "production" infers nothing as far as load -- activity -- etc. production only infers you have tested and benchmarked the code that is running in order to know that it'll work ok).

sync and optimise

Scott, February 25, 2003 - 12:05 am UTC

G'day Tom

I'd like to clarify my understanding of the difference between ctx_ddl.sync_index and ctx_ddl.optimize_index.

Sync_index could be used in lieu of ctxsrv and would typically be executed during set intervals using DBMS_JOB. This ensures pending DML is available when querying the index.
Optimize_index is used periodically to reduce fragmentation of the index, regardless of how you synchronize. This would be executed less often than sync_index.
Are the statements accurate?

Does the optimize_index procedure issue the alter index statement as "online", to allow a non-blocking operation?

And our DBAs and I couldn't figure out what you meant by
"Make sure to gather METRICS before and after doing this -- so you can see if the rebuild did anything positive or negative with regards to performance and size. Only rebuild in the future if you see POSITIVE payback. "
ie - what METRICS?
And how you would determine positive payback?

Thankyou to your other answers in regards to Oracle Text, they've helped a lot.

Thanks.

Tom Kyte
February 25, 2003 - 9:27 am UTC

yes, one adds newly added documents to the index (sync)
the other in effect rebuilds it

You can use alter index rebuild online to get "non blocking", it'll allow concurrent access to the index during the rebuild.


What I mean by metrics (it is sad the dba's do not know how to do this ;( )
would be to collect metrics about the queries that use this index - say all of the TEXT queries. Save the average LIO's per query. Now rebuild. After a while, come back and make sure that they are "better"

Text indexes are more like Bitmaps -- not like B*Tree's. they do require a bit more care and feeding then B*Trees.

Response from our DBA

&quot;Gavin&quot;, March 26, 2003 - 3:11 am UTC

G'day Tom.

This is what our DBA has to say in response to your comments...

<quote>
This Tom guy... how many are there, I'm assuming Tom1, Tom2 and at least Tom3...

His whole purpose in life is to get questions, research, and respond... He's not out in the field, trying to keep up with the flood of new technology that keeps coming out.

We don't have time to research new technology, especially when it's not being used in production. Obviously as intermedia/text/context (whatever the hell they're calling it this week) goes into production, we will learn to administer and tune it. For this Tom guy(s) to give this comment shows the lack of real-world experience he/they have...

My 2 cents.

Gavin
</quote>


Tom Kyte
March 26, 2003 - 7:41 am UTC

Your DBA needs a vacation....

I don't spend too much time researching actually. Sometimes -- let me show you an example:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8760267539329 <code>

that one I had to research -- you can see in the comments:

...
first of all I have to apologize for being so impatient ( when I saw some
answers on newer questions). ....


You see -- I answered the 10 "no brainers" and went on a camping trip. Saved the one to research for later.


Your DBA is the "wild west, I'm a hero DBA" -- obviously. There are many of those types. Just whip it out there -- don't think about what'll happen. We'll fix things as they happen. We don't need to think about planning for anything. We are too smart for that.

I usually get called into work with your DBA to fix things. Going to a customer this morning in fact to talk about "what the heck happened to our systems, why were we slow for a week". ('real world' DBA's decided to 'tune' a production box. Changed tons of things all at once -- didn't record WHAT they changed -- performance went down the tubes. No metrics from "when it was OK" to compare against. Not even numbers like "average response time needs to be X". Just "system is really slow -- whats wrong with Oracle??"... So after a week of unraveling what these real worlders did -- putting files back where they came from, unfixing all of their fixes, they think -- repeat THINK -- they are back where they started.)

There is one of me.
I live in the real world.
I actually run a couple of systems -- day to day.

I don't understand his comment about "new technology" as this technology has been part of Oracle since 7.3 days.

You need a new DBA. My 2 cents.

sync oracle text index

Sean, July 18, 2003 - 11:11 pm UTC

Hi Tom,

I am sorry that I posted this question on the wrong thread last time.
I want to setup job for sync Oracle text index.  I got this error when I try to 
create stored procedure or job:

PLS-00201: identifier 'CTX_DDL' must be declared  
    

Thanks so much for your help.

Sean 

--This one works.
SQL>exec ctx_ddl.sync_index('blob_index');

-- This onw works.
SQL> begin 
  2   ctx_ddl.sync_index('blob_index');
  3  end;
  4  /

PL/SQL procedure successfully completed.


--But this one does not work
SQL> create or replace procedure p1
  2   IS
  3       begin 
  4         ctx_ddl.sync_index('blob_index');
  5       end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- ----------------------------------------------------
4/8      PLS-00201: identifier 'CTX_DDL' must be declared
4/8      PL/SQL: Statement ignored
SQL> 


-- The job wakes up every minute.  But there is error in log file
SQL> exec dbms_job.submit( :l_job, 'ctx_ddl.sync_index(''blob_index'');', sysdate+5/1440, 'sysdate+1
/1440' );

PL/SQL procedure successfully completed.

-- Here is the error in alert.log file
Fri Jul 18 22:36:34 2003
Errors in file c:\oracle\admin\winter\udump\winter_j000_1064.trc:
ORA-12012: error on auto execute of job 22
ORA-06550: line 1, column 96:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
 

Tom Kyte
July 19, 2003 - 11:31 am UTC

you have the ability to execute ctx_ddl via a role, you need a direct grant for the procedure to work. see

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>



sync oracle text index

sean, July 18, 2003 - 11:23 pm UTC

Hi Tom,

I am sorry that I posted this question on the wrong thread last time.
I want to setup job for sync Oracle text index.  I got this error when I tried to 
create stored procedure or job:

PLS-00201: identifier 'CTX_DDL' must be declared  
    

Thanks so much for your help.

Sean 

--This one works.
SQL>exec ctx_ddl.sync_index('blob_index');

-- This onw works.
SQL> begin 
  2   ctx_ddl.sync_index('blob_index');
  3  end;
  4  /

PL/SQL procedure successfully completed.


--But this one does not work
SQL> create or replace procedure p1
  2   IS
  3       begin 
  4         ctx_ddl.sync_index('blob_index');
  5       end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- ----------------------------------------------------
4/8      PLS-00201: identifier 'CTX_DDL' must be declared
4/8      PL/SQL: Statement ignored
SQL> 


-- The job wakes up every minute.  But there is error in log file
SQL> exec dbms_job.submit( :l_job, 'ctx_ddl.sync_index(''blob_index'');', sysdate+5/1440, 'sysdate+1
/1440' );

PL/SQL procedure successfully completed.

-- Here is the error in alert.log file
Fri Jul 18 22:36:34 2003
Errors in file c:\oracle\admin\winter\udump\winter_j000_1064.trc:
ORA-12012: error on auto execute of job 22
ORA-06550: line 1, column 96:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored 

sync index using dbms_job

Sean, July 19, 2003 - 9:03 pm UTC

Hi Tom,

Here is the answer I found.  It is strange that commit is needed in the stored procedure.

Thanks for your help.


Sean 


SQL>connect system/password
SQL>grant execute on ctx_ddl to scott
SQL>connect scott/password
SQL>create or replace procedure p10
IS
begin
    ctx_ddl.sync_index('blob_index');
    commit;
end;
/

SQL>exec dbms_job.submit( :l_job, 'p10;', sysdate+5/1440, 'sysdate+5/1440' );

-- The following does not work though the job wakes up and there is no error in alter.log.
SQL> exec dbms_job.submit( :l_job, 'ctx_ddl.sync_index(''blob_index'');', sysdate+5/1440, 'sysdate+5/1440' );
 

Diff between ctx_ddl & dbms_utility

Sami, March 19, 2004 - 5:01 pm UTC

Dear Tom,

Thanks for all your help/support in the past. I visit this site everyday.

Q1) What is the difference between method (A) & (B)?
Q2) Which is the better/efficient way to go in 8i?
Q3) How to identify "exec ctx_ddl.optimize_index" is required?
Q4) Do you prefer to keep all the DOMAIN index in a tablespace which is created with NOLOGGING option?

(A)
exec ctx_ddl.sync_index('USER1.DOMAIN_INDEX1');
exec ctx_ddl.optimize_index('USER1.DOMAIN_INDEX1','FAST');

(B)
exec dbms_utility.exec_ddl_statement('alter index USER1.DOMAIN_INDEX1 rebuild online parameters(''sync'')');
exec dbms_utility.exec_ddl_statement('alter index USER1.DOMAIN_INDEX1 rebuild online parameters(''optimize fast'')');



Tom Kyte
March 20, 2004 - 9:50 am UTC

q1) one is easier to code then the other.

q2) see #1

q3) if you maintain statspacks over time and you see your lios going up measurably for you text queries, time to take a look. if you have a system whereby you bulk add lots of stuff at once -- that would be a good time to look as well.

q4) no, i do not use nologging very much. it is the grand EXCEPTION, not the rule. I use it when it is necessary (really big load that I can schedule a backup after)

"alter index rebuild online" locking rows

Tri, April 02, 2004 - 1:02 pm UTC

Hi Tom,

Thank you for this wonderful site.

Some background info:

We have an intermedia index (Oracle version 8.1.7.4) created like this:

create index members_im_search_idx
on members_intermedia(intermedia_dummy_indicator)
indextype is ctxsys.context
parameters('datastore members_con_mcd
storage members_con_bs
filter ctxsys.null_filter
section group members_con_bsg
wordlist members_con_wl
memory 50M');

Please let me know if you need to see how some of these parameters are set (I assume you don't need them for my questions below). This "members_intermedia" table is pretty much a shadow table for the real "members" table (btw, each of these tables contains millions of rows). It has a few columns from the real members table that we want to search upon, plus a dummy column that the index is built upon (as you can see in the create statement above). Rows in this members_intermedia table is synched with the real members table via update and insert triggers, and the dummy column value is reset/set for such rows. We chose to go with this shadowing approach so we can deploy the index without any downtime (we basically didn't turn on the update/insert triggers until the index creation is done initially) and if we decide to add more columns to the 2 tables in the future, we don't need to rebuild the index from scratch again.

We currently synch the index every hour via a dbms_job with:

dbms_utility.exec_ddl_statement('ALTER INDEX MEMBERS_IM_SEARCH_IDX REBUILD ONLINE PARAMETERS(''SYNC MEMORY 50M'')');

No other work has been done to the index. The index has been up and running for 3+ months now.

Sometimes, there are thousands of new or updated rows to be synched with. What we are seeing is during the index resynch process, a few handful of transactions into the real members table are being blocked. Also, the synch is taking a lot of gets (about 1.5 millions), which may or may not be a big deal given we have lots of rows for the index to work with.

My questions:

1) How are the rows being blocked and is the blocking that we are seeing preventable at all? (if so, please tell)

2) From what I've read on this site, it looks like we need to "optimize" the index in order to lower the LIO for future synchs?

Any other observations you may have is greatly appreciated.

Tom Kyte
April 02, 2004 - 1:53 pm UTC

1) to diagnose the blocking, we'd need to see the implementation - how is that shadow table that the index is really on maintained. what operations are being blocked on the main table -- and are all operations of that type being blocked.

2) given your batch sizes -- maybe, maybe not (i tend to sync much less data much more often -- leading to a break down over time, that and I UPDATE the indexed data leading to false positives that are discovered after fetching the source document). I would do this -- get a measure of the average LIOs for your big queries over time (stats pack would be a good way) and compare the before/afters.


Additional Information

Tri, April 02, 2004 - 9:20 pm UTC

Here's the implementation you requested:

CREATE TABLE members_intermedia AS
SELECT
member_id,
'A' as intermedia_dummy_indicator,
SYSDATE AS last_update,
last_name,
first_names,
email
FROM
members
;

ALTER TABLE members_intermedia add constraint members_intermedia_pk primary key (member_id);

This shadow table members_intermedia is maintained by 2 triggers on the real members table. Here's the meat of the update trigger:

-- ... lengthly logic (see description below)

IF intermedia_bool
THEN

UPDATE members_intermedia
SET
intermedia_dummy_indicator = 'A',
last_update = SYSDATE,
last_name = :NEW.last_name,
first_names = :NEW.first_names,
email = :NEW.email
WHERE
member_id = :NEW.member_id
;

END IF;


intermedia_bool is set to true when :OLD.last_name <> :NEW.last_name, or :OLD.first_names <> :NEW.first_names, or :OLD.email <> :NEW.email. There are other unrelated logic happening in the trigger to insert into a separate audit table when these and other column values are changed. I assume that's all irrelevant to this discussion.

The meat of the insert trigger:
INSERT INTO members_intermedia (
member_id,
intermedia_dummy_indicator,
last_update,
last_name,
first_names,
email
) VALUES (
:NEW.member_id,
'A',
SYSDATE,
:NEW.last_name,
:NEW.first_names,
:NEW.email
);

We notice the blocking transactions seems to be exactly the update statement in the trigger above:

UPDATE MEMBERS_INTERMEDIA
SET INTERMEDIA_DUMMY_INDICATOR='A',LAST_UPDATE=SYSDATE,
LAST_NAME=:b4,FIRST_NAMES=:b5,EMAIL=:b6 WHERE MEMBER_ID = :b7

These update statements are blocked while this SQL is executing:
insert into "GV2"."DR$MEMBERS_IM_SEARCH_IDX$I"
values (:token, :ttype, :first, :last, :count, :data)
This SQL is not in our application source code, but I
guess that it is part of what Oracle itself does during the index rebuid.

Tom Kyte
April 03, 2004 - 8:48 am UTC

that would indicate that Oracle Text is indexing a row that someone it trying to update at that point in time.

The indexing routine has most likely locked the rows it is working on -- this can be confirmed by running a sync.  In fact, I just did and


ops$tkyte@ORA9IR2> create index t_idx on t(y) indextype is ctxsys.context;
Index created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 'how now brown cow' );
1 row created.
 
ops$tkyte@ORA9IR2> commit;
Commit complete.
 
ops$tkyte@ORA9IR2> @trace
Session altered.
 
ops$tkyte@ORA9IR2> alter index t_idx rebuild online parameters( 'sync memory 50m' );


the trace file ultimately shows:

...
select 1
from
 "OPS$TKYTE"."T" where rowid = :RID for update nowait


So, the syncing is locking the rows it is processing to ensure they are stable (so we don't miss a change made during this sync - we wouldn't be able to see the uncommitted changes)

Now, if this is ongoing problem -- one thing we can do is "background" the shadow table maintenance.  since the index is updated infrequently in the first place, deferring the shadow table maintainance should not be an issue.

You can either

a) put the information needed to do the update into a 'scratch job table', and submit a job to process it, eg:
 
    dbms_job.submit( l_job, 'update_shadow(JOB);' );
    insert into scratch (pk, otherdata....) values ( l_job, ..... );

and have update_shadow:

procedure shadow( p_job in number )
is
   l_rec scratch%rowtype;
begin
   select * into l_rec from scratch where pk =p_job;
   do the update, so what if it blocks for a bit, no humans involved;
   delete from scratch where pk = p_job;
   commit;
end;

b) have the trigger do a select for update on the row it wants to update and if it gets it -- do the update, else do the above. (so you are using jobs as little as you need to)



 

Additional Information

Tri, April 02, 2004 - 9:22 pm UTC

Forgot to mention that the insert trigger is:
AFTER INSERT ON members FOR EACH ROW
while the update trigger is:
BEFORE UPDATE ON members FOR EACH ROW


Gr8

Kiran Kumar Sabbisetti, April 05, 2004 - 2:31 am UTC

This thread is very useful to understand the CTX_DDL.

A reader, May 18, 2004 - 7:35 pm UTC

Hi Tom,
please,
how can we show text from pdf documents

This queyr
select * from web.test where contains( test,'Oracle') > 0
Returns three pdfs archives but you can't see too much in blob binary, how can you show
50 characters after where the word was found and 50 characters before, in text readable.

If you can please

Tom Kyte
May 19, 2004 - 7:44 am UTC

we do not have a pdf "parser".

you would/could use the inso filter to extract the text, index the clear text instead of the pdf. get the hit on the text (which of course would point to the pdf) and ask oracle text to mark up the clear text -- but to rewrite the pdf itself, that won't be happening inside the database like that.

this approach would be akin to what google, the gold standard for searching, does.

A reader, May 19, 2004 - 9:15 am UTC

Thanks Tom
this is the link
</code> http://otn.oracle.com/products/text/htdocs/altfilters.htm <code>

A reader, May 19, 2004 - 10:44 am UTC

HI Tom sorry,
I searched again the documentation, and didn't found what you say.
"you would/could use the inso filter to extract the text"

Previous to asking the only thing I found was this

declare
resarr ctx_query.browse_tab;
begin
ctx_query.browse_words('SEARCH_IDX','Database',resarr,10,CTX_QUERY.BROWSE_AROUND);
for i in 1..resarr.count loop
dbms_output.put_line(resarr(i).word || ':' || resarr(i).doc_count);
end loop;
end;
/

But this didn't work because return this kind of output
Ð:2
DATABASEADMINISTRATORÕS:1
ÆÊÍ:1

Could you please giveme the command to extract the text from a blob column where there is a pdf, doc, etc. document.

Thank you

Tom Kyte
May 19, 2004 - 11:09 am UTC

ctx_doc.filter


read about the ctx_doc package.

A reader, May 19, 2004 - 1:10 pm UTC

Thanks Tom I should be blind, that was the answer :)
You are the man!

When export....

A reader, June 08, 2004 - 11:12 am UTC

Hi Tom,
This CREATE INDEX.... Query on CLOB, create some tables. with some data.
for example
table name: DR$SEARCH_IDX$R
data :
row_no data
-------- ------
1 (ORABLOB)
2 (ORABLOB)

My qns is
when i am exporting the SCHEMA, these tables are also
exported.
when i am importing to another SCHEMA these tables
imported with data.
Even after, ALTER INDEX.... I cant able to find this.
Kindly explain about this
thanks


Tom Kyte
June 08, 2004 - 12:56 pm UTC

not sure what you want explained?

When export....

A reader, June 09, 2004 - 8:20 am UTC

Hi Tom,
        

When I am using
    " create index search_idx on ctx_demo(text) indextype is ctxsys.context" 
this Query oracle creates some tables automatically as i said above.
          
    When i am exporting this is also exported.
    When i am importing this tablles only imported not data.( some other schema)
When I am using
    " alter index search_idx rebuild parameters( 'sync' )"
    The tables data are not filled as old schema.
    
    SQL> select * from DR$SEARCH_IDX$I;

    SP2-0678: Column or attribute type can not be displayed by SQL*Plus

    SQL> select * from DR$SEARCH_IDX$K;

    no rows selected

    SQL> select * from DR$SEARCH_IDX$N;

    no rows selected

    SQL> select * from DR$SEARCH_IDX$R;

    SP2-0678: Column or attribute type can not be displayed by SQL*Plus

    
My question are
    1. Should I export these file also or need be excluded?
    2. in the second schema, where i am importing, Whether query is re-created?


 

Tom Kyte
June 09, 2004 - 9:36 am UTC

No, those tables should not be exported/imported.  just the index definition (in fact, in 9ir2 -- i cannot export them at all, don't know what version you have, you never said).  

only the index definition should have been exported.




ops$tkyte@ORA9IR2> create table t as
  2  select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(object_name) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
  2         decode( status, 'INVALID', '*', '' ) status,
  3             decode( object_type,
  4                      'TABLE', (select tablespace_name from user_tables where table_name = object_name),
  5                      'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
  6                      'LOB', (select tablespace_name from user_segments where segment_name = object_name),
  7                                  null ) tablespace_name
  8  from user_objects a
  9  order by object_type, object_name
 10  /
 
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        DR$T_IDX$X                       USERS
             SYS_IOT_TOP_47744                USERS
             SYS_IOT_TOP_47749                USERS
             T_IDX
 
LOB          SYS_LOB0000047741C00006$$        USERS
             SYS_LOB0000047746C00002$$        USERS
 
TABLE        DR$T_IDX$I                       USERS
             DR$T_IDX$K
             DR$T_IDX$N
             DR$T_IDX$R                       USERS
             T                                USERS
 
 
11 rows selected.
 
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> select count(*) from DR$T_IDX$I;
 
  COUNT(*)
----------
     18706
 
ops$tkyte@ORA9IR2> !exp userid=/ 'owner=ops$tkyte'
 
Export: Release 9.2.0.5.0 - Production on Wed Jun 9 09:16:20 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
 
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. . exporting table                              T      32071 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
  2         decode( status, 'INVALID', '*', '' ) status,
  3             decode( object_type,
  4                      'TABLE', (select tablespace_name from user_tables where table_name = object_name),
  5                      'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
  6                      'LOB', (select tablespace_name from user_segments where segment_name = object_name),
  7                                  null ) tablespace_name
  8  from user_objects a
  9  order by object_type, object_name
 10  /
 
no rows selected
 
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> !imp userid=/ full=y
 
Import: Release 9.2.0.5.0 - Production on Wed Jun 9 09:16:38 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"      32071 rows imported
Import terminated successfully without warnings.
 
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
  2         decode( status, 'INVALID', '*', '' ) status,
  3             decode( object_type,
  4                      'TABLE', (select tablespace_name from user_tables where table_name = object_name),
  5                      'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
  6                      'LOB', (select tablespace_name from user_segments where segment_name = object_name),
  7                                  null ) tablespace_name
  8  from user_objects a
  9  order by object_type, object_name
 10  /
 
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        DR$T_IDX$X                       USERS
             SYS_IOT_TOP_47757                USERS
             SYS_IOT_TOP_47762                USERS
             T_IDX
 
LOB          SYS_LOB0000047754C00006$$        USERS
             SYS_LOB0000047759C00002$$        USERS
 
TABLE        DR$T_IDX$I                       USERS
             DR$T_IDX$K
             DR$T_IDX$N
             DR$T_IDX$R                       USERS
             T                                USERS
 
 
11 rows selected.
 
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> select count(*) from DR$T_IDX$I;
 
  COUNT(*)
----------
     18706
 
 

pdf 50 characters

Raj, June 25, 2004 - 10:22 pm UTC

Hi Tom,

To one of the user questions, asked on May 18 2004, on extracting the 50 characters before and after the search word found in the PDF document, similar to what google does
you said

<quote>
we do not have a pdf "parser".

you would/could use the inso filter to extract the text, index the clear text
instead of the pdf. get the hit on the text (which of course would point to the
pdf) and ask oracle text to mark up the clear text -- but to rewrite the pdf
itself, that won't be happening inside the database like that.

this approach would be akin to what google, the gold standard for searching,
does.
</quote>

can you please explain how to extract 50 characters before and after the match word in the document. My application needs to display text with the search word highlighted- similar to google output. They are all PDF's

<quote>
..... and ask oracle text to mark up the clear text -- but to rewrite the pdf ....
</quote>
Wouldn't this give the whole document markup? I just need 50 or so characters surrouding the match word. Do I use CTX_DOC.HIGHLIGHT for this purpose?

In one another question where the user asked how to search on a table containing columns c1, c2 c3 ..cn and the search word can occur in any of these columns, the solutions you suggested is using index on a procedure that returns the lob of combined columns or creating an index on each column. (this is for oralce 8.1.6) </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:440419921146 <code>


In 9i release 2 I can solve the same problem with "multi_column_datastore"
Is one solution better than the other?

Sorry I am a novice in using oracle search and stumped with the problems.

Needless to say, you are a great guy ready to help many others like me. thanks much!!

Tom Kyte
June 26, 2004 - 6:40 pm UTC

you ask Oracle text to mark up the text, you can tell us what to wrap the hits with (eg: "mark up the text with #!" for example, then the terms of interest would be marked up with #!....#1, you can then "substr").


you would filter the text

you would markup the entire document

you would then find all occurences of what you are interested in, looking for your marker.



A multi-column datastore may well be easier than writing a procedure, yes.

can we create in other tablespace?

A reader, July 02, 2004 - 9:17 am UTC

Hi Tom,
Can this index created some other tablesapce ( other than SYSTEM). In my case it has been created in SYSTEM tablespce.
1. How to create other than SYSTEM
2. How to move to other tablespce ( if created in SYSTEM).

Thank you

Tom Kyte
July 02, 2004 - 10:50 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2886881123811 <code>

it is not really in system - -the index is a bunch of tables, you can specify where they go (your default tablespace by default)

How drop interMedia text search Index...

peru, July 13, 2004 - 9:10 am UTC

Hi Tom,
Oracle 8.1.7
I have table with column CLOB.
I have use the following query to create index
CREATE INDEX SEARCH_IDX ON NEWNEUTRON(NCONTENU) INDEXTYPE IS CTXSYS.CONTEXT;
This will create SEARCH_IDX index along with
DR$SEARCH_IDX$X,
SYS_IL0000065942C00008$$,
SYS_IL0000068393C00006$$,
SYS_IL0000068398C00002$$

This size of index will be very high.
If I drop the index using
DROP INDEX SEARCH_IDX FORCE;

SYS_IL0000065942C00008$$ - this can not be dropped.
If I issued
ALTER INDEX SYS_IL0000065942C00008$$

I have got error
ORA-22864: cannot ALTER or DROP LOB indexes

1. How to drop this index.
2. This index occupies very high space. is it true.
Thank you







Tom Kyte
July 13, 2004 - 12:11 pm UTC

that is the index on your lob, you don't want to drop that. you'd destroy the lob.

Intermedia and VPD

Steve Kiteley, September 03, 2004 - 11:34 am UTC

If the schema owning and synchronising the index as part of a dbms_job background process has no access to the data because access is denied via a vpd policy will the index be successfully rebuilt?

Tom Kyte
September 03, 2004 - 1:29 pm UTC

a normal text index (create index i on t(x) indextype is ctxsys.context) will index the entire table successfully.



Rebuild Text Indexes ...

VKOUL, August 15, 2006 - 12:48 pm UTC

Hi Tom,

I was rebuilding some text indexes (just to test how to rebuild text indexes) and I am getting some errors/warnings thrown out as 
********************************************************
SQL> alter index FT_TABLE_M5PIK100 rebuild parameters ('lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');

alter index FT_TABLE_M5PIK100 rebuild parameters ('lexer aspen_lexer storage aspen_storage stoplist 
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX FT_TABLE_M5PIK100 failed
DRG-11000: invalid keyword LEXER
**********************************************************

But when I do the following, it re-creates it successfully.
***********************************************************
SQL> alter index FT_TABLE_M5PIK100 rebuild;

Index altered.

SQL> 
***********************************************************
With the successful rebuilding of index, the parameters column in IND view shows NULL, whereas with index rebuild command with "parameters" string shows the exact string I supplied (which was originally there before I messed up with rebuilding) but it did throw an error/warning ...

Is it a bug or something I am doing wrong ?

Pls. comment ...

SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> 
 

Tom Kyte
August 15, 2006 - 2:09 pm UTC

Rebuild Text Indexes

VKOUL, August 15, 2006 - 2:30 pm UTC

Thanks for the reply ...

After applying REPLACE, now I am getting REPLACE embedded with other parameters ...

*********************************************************
SQL> alter index FT_TABLE_M5PIK100 rebuild parameters ('replace lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist');

Index altered.

SQL> SELECT parameters FROM IND WHERE index_name = 'FT_TABLE_M5PIK100';

PARAMETERS
--------------------------------------------------------------------------------
replace lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist

SQL> 
***********************************************************

Is there a way to get the original perferences 'lexer aspen_lexer storage aspen_storage stoplist aspen_stoplist' ?

Thanks ... 

Tom Kyte
August 15, 2006 - 2:43 pm UTC

short of "substr" or replace() - not that I am aware of.

VKOUL, August 15, 2006 - 3:27 pm UTC

What is this REPLACE doing in there, as per the documentation,it says we have to apply replace but it does not say anything about replace(), I thought replace is a embedded keyword to actually replace the perferences ...

do you think I did something wrong while applying REBUILD on that very index ?

Tom Kyte
August 15, 2006 - 3:46 pm UTC

replace is necessary in the rebuild if you want to - well - specify those things.

that link does seem to go into it?

Using dbms_utility.exec_ddl_statement

A reader, October 30, 2006 - 9:21 am UTC

Tom,

What is a correct way to use dbms_utility.exec_ddl_statement.
I want to use the procedure to submit a job that archive current logfile (to send it to another mashine) by
exec dbms_utility.exec_ddl_statement('alter system archive log current');
I get no error message, but archiving log does not happen.
If I execute alter system archive log current as the same user from sql prompt it works.
What I do wrong. I tried dbms_utility.exec_ddl_statement('alter user XYZ identified by 123'); and this worked.

I use Oracle 10g.

Thanks



Tom Kyte
October 30, 2006 - 9:46 am UTC

you should never use it, it pre-dates the implementation of execute immediate, that is what you should use


But that alter system archive log current did work, unless it raised an error. You would have to prove otherwise. Not sure what you did to say "it did not work"

A reader, October 30, 2006 - 10:32 am UTC

Thanks for quick responce.

I tried this one:

SQL> create procedure archivecurrentlog as
  2  begin
  3  execute immediate 'alter system archive log current';
  4  end;
  5  /
Procedure created.

SQL> exec archivecurrentlog
BEGIN archivecurrentlog; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "*****.ARCHIVECURRENTLOG", line 3
ORA-06512: at line 1

SQL> connect / as sysdba
Enter password:
Connected.

SQL> exec *****.archivecurrentlog

PL/SQL procedure successfully completed.

Why I get "insufficient privileges" error? 
The user has a DBA role and can execute archive log current statement from sqlplus prompt (but not as a procedure...)  

Tom Kyte
October 30, 2006 - 12:44 pm UTC

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>


please don't do things as sysdba, dangerous, dangerous, dangerous... things work entirely differently for sysdba then they do for real users.


and that procedure failed for sysdba by the way - if it was created as you created it.

A reader, October 30, 2006 - 12:12 pm UTC

I think I found the answer myself:

</code> http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html <code>

I need to grant alter system privs direct to my user...

to build, rebuild or optimize

Jay, May 14, 2008 - 3:59 pm UTC

Tom,

Can you briefly explain the difference, pros and cons, of :

ctx_ddl.optimize_index('USER1.DOMAIN_INDEX1','FULL');
and
alter index USER1.DOMAIN_INDEX1 rebuild online;


The reason I ask is that the
alter index DOMAIN_INDEX1 rebuild online;
happens much quicker than the
ctx_ddl.optimize_index('DOMAIN_INDEX1','FULL');
or
ctx_ddl.optimize_index('DOMAIN_INDEX1','FAST');
for that matter.

Also, and this may be the answer, the syntax I used was:
ALTER INDEX DR$DOMAIN_INDEX1$X REBUILD ONLINE;

I would appreciate it very much if you could clear this up for me.

the back story,

The table that is indexed is heavily transient, 1000's of new rows a day and 1000's of deletes. The assumption is that the indexes are getting highly fragmented, I'm not sure if that is the case.

Right now they have us building two separate indexes and swapping them out at when fulltext searches get "too slow". Building one index, enabling it in the application and deleting the original index. I thought that just optimizing the indexes would be a better solution but it seems to be taking longer than the straight up rebuild on the index. Either alternative solution, optimize or rebuild, seems better than the current solution of building completely new indexes.

the fulltext indexes sync on commit, if that matters.

Any advice?

Thanks,
Jay

Tom Kyte
May 16, 2008 - 11:55 am UTC

...
Also, and this may be the answer, the syntax I used was:
ALTER INDEX DR$DOMAIN_INDEX1$X REBUILD ONLINE;
.....

you did nothing to the context index with that, you rebuilt a conventional b*tree index on the table DR$<domain index name>$I

you didn't touch the context index with that.

Can't execute CTX_DDL

Chris, December 21, 2009 - 2:41 pm UTC

I am unable to execute the CTX_DDL calls on a server we have. I have confirmed that CTXSYS exists and I tried what was said on this list, which was to run this statement:

grant execute on ctx_ddl to myuser

But when I run the grant I get this error:

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:

Why on earth would I be getting that error on a grant of ctx_ddl?
*Action:
Tom Kyte
December 21, 2009 - 4:12 pm UTC

because you are not logged in as CTXSYS I would presume.

grant execute on T to myuser;

would be

grant execute on CURRENTLY_LOGGED_IN_USERNAME.T to myuser;



ctxsys owns that package, become ctxsys.

Can't execute CTX_DDL

Chris Holt, December 23, 2009 - 7:29 am UTC

Found out my problem with this. The issue was that the CTXSYS user account was expired and locked for some reason.
When I removed the lock and reassigned the password all worked.

Thanks for your help.
Tom Kyte
December 31, 2009 - 11:54 am UTC

nope, that by itself would not do anything, you did something more like....

logged in as ctxsys

after doing that.

convert html tags to rtf tags in oracle

Narendra Rawat, June 01, 2010 - 9:25 am UTC

Hi,
I would like to convert the html tags to rtf tags.
Is there any way or some inbuilt package in oracle which support conversion of html tags to rtf tags.
Tom Kyte
June 08, 2010 - 8:27 am UTC

no, not in that direction.

Inexplicable observation

A reader, November 23, 2010 - 11:33 am UTC

Tom,
Recently we implemented text search in a test database 10.2.0.3. I ran only like 600-700 search queries in 15 minutes but in the AWR report there are almost 200k queries of this type:

SELECT/*+DYNAMIC_SAMPLING(0) INDEX(T "DR$DOM_NAME_PERS$X")*/ DISTINCT TOKEN_TEXT FROM "TEST"."DR$DOM_NAME_PERS$I" T WHERE TOKEN_TEXT LIKE :lkexpr and (TOKEN_TYPE = 0 OR TOKEN_TYPE = 4 OR TOKEN_TYPE BETWEEN 16 AND 74);

select /*+ dynamic_sampling(0) */ data from "TEST"."DR$DOM_ADDRESS_PERS$R" where row_no = :row_no;

Are these recursive SQLs? Is the dynamic_sampling telling me something?

Thanks...
Tom Kyte
November 23, 2010 - 1:18 pm UTC

yes, it is recursive sql, sql we did to run your sql

it is telling you that you have not gathered statistics in your database on many tables (looks like the text index tables) and therefore we are doing a sample scan of the tables while hard parsing queries that use them.

Inexplicable observation

A reader, November 23, 2010 - 2:04 pm UTC

Tom,
Thank you so much for responding so quickly. Just to make sure, is DBMS_STATS the right way to gather stats on these indexes?

Thanks...
Tom Kyte
November 23, 2010 - 3:41 pm UTC

http://forums.oracle.com/forums/thread.jspa?threadID=1112987&tstart=0

The commenter Roger Ford is directly related to Text development.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here