Re: Intermedia indexing ?

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Tue, 21 Sep 1999 16:16:25 GMT
Message-ID: <37e7a90b.15052103@newshost.us.oracle.com>

On Tue, 21 Sep 1999 17:05:39 +0200, "Dr. Louis Coetzee" <lcoetzee_at_subversion.mikom.csir.co.za> wrote:

>Hi,
>
>I need to upload Word documents to a blob field. I also require an index
>associated with
>this blob column in order to perform queries on the blob data (word
>documents).
>
>I created the following table:
>
>create table BlobTryT (
> id NUMBER primary key,
> blobname varchar2(160),
> blobdata BLOB);
>
>with the following index:
>CREATE INDEX blobdata_idx ON BlobTryT(blobdata) indextype IS
>ctxsys.context parameters ('FILTER CTXSYS.INSO_FILTER');
>
>I am able to upload the word documents, but am unable to perform a
>select (i.e. the select always returns 0).
> select id from blobtryt where contains (blobdata,'data') >0;
>

This is probably happening for one of two reasons:

The document isn't loaded properly (missing data), but I doubt this is the case.
More likely, external procedures are not configured correctly on your system. External procedures are used for the filtering process with interMedia Text.

Run a query against CTX_USER_INDEX_ERRORS. If you see any messages like 'failed to open file \drgit3', this is a sure sign that the document is begin filtered properly.

If you can't "TNSPING EXTPROC_CONNECTION_DATA" from a DOS window, then you don't have this working properly.

Your files on the server should look something like:

listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my-pc)(PORT = 1521))

)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =

      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Oracle\Ora81)
      (PROGRAM = extproc)

)
(SID_DESC =

      (ORACLE_HOME = D:\Oracle\Ora81)
      (SID_NAME = your_SID_here)

)
)

tnsnames.ora:

your_alias_here =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my-pc)(PORT = 1521))   )
  (CONNECT_DATA =
    (SERVICE_NAME = your_SID_here)
  )
)

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (SOURCE_ROUTE = OFF)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC0))   )
  (CONNECT_DATA =
    (SID = PLSExtProc)
  )
)

2) If your server is Windows NT, the System (not User) Environment Variable in Windows NT, ORACLE_HOME, must be set to your Oracle8i home. Note that I'm not referring to a registry entry. You will need to reboot after setting this for it to take effect.

>Any advice on how to create and use indexes on blob datafields ?
>
>Much appreciated.
>
>Dr. Louis Coetzee
>
>--
>----------------------------------------------------------------------
>Louis Coetzee (PhD)
>+I tried to drown my sorrows with booze... who knew they could swim !+
>"Love means nothing to a tennis player"
>*My mind not only wanders, sometimes it leaves completely.*
>
>Email: lcoetzee_at_subversion.mikom.csir.co.za
>Homepage: http://subversion.mikom.csir.co.za/~lcoetzee/
>Phone: +27 12 841 3036 (w)
>Building 43, Office C330,
>Software Engineering, Mikomtek, CSIR, Pretoria, 0001, South Africa
>----------------------------------------------------------------------
>
>
>

Thanks!

Joel

Joel R. Kallman Oracle Service Industries

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Tue Sep 21 1999 - 11:16:25 CDT