Complex CONTEXT index

From: Bill Zakrzewski <bill_at_intactus.com>
Date: Fri, 23 Jan 2009 10:37:06 -0500
Message-Id: <47323F15-26F7-4BE9-9421-DFC81F5734E4_at_intactus.com>



Listers -

Oracle 10.2.0.4.0
RH Linux

I have a table (see below) that I would like to create a Context/ Intermedia index on the title, short_desc, long_desc and the document (BLOB column). I have created a similar index on a different table that contained a CLOB by concatenating all of the fields into a single CLOB and creating the CONTEXT index using the pl/sql package/ procedure (see below). I would like to do the same thing using the BLOB column, but not sure what values to use in the parameters for the DBMS_LOB.CONVERTTOCLOB procedure, specifically the BLOB_CSID and LANG_CONTEXT. My concern is the defaults will cause it to copy the data in binary format and not convert correctly, as the document may be a PDF or WORD Document or Excel Spreadsheet, etc. Thanks in advance for your help.

  Name Null? Type

  • --------
    MITSS_ID NOT NULL NUMBER(10) ID NOT NULL NUMBER(10) IS_IMAGE NOT NULL CHAR(1) FILENAME_LOW NOT NULL VARCHAR2(255) FILE_SIZE_LOW NOT NULL NUMBER(38) CONTENT_TYPE NOT NULL VARCHAR2(100) TITLE VARCHAR2(100) SHORT_DESC VARCHAR2(150) LONG_DESC VARCHAR2(4000) CREATED_DATE DATE MODIFIED_DATE DATE IS_APPROVED NOT NULL CHAR(1) APPROVED_BY VARCHAR2(255) LAST_UPDATE_BY VARCHAR2(100) PHOTOGRAPHER_NAME VARCHAR2(100) DOCUMENT BLOB
....
--- listing of just the procedure that appends the data into a single CLOB

procedure append(
     p_lob    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
     p_append IN VARCHAR2 CHARACTER SET p_lob%CHARSET
)
is
begin

     dbms_lob.writeappend(p_lob, length(p_append), p_append); end append;

PROCEDURE CONVERTTOCLOB
  Argument Name Type In/Out Default?

  • ----------------------- ------ -------- DEST_LOB CLOB IN/OUT SRC_BLOB BLOB IN AMOUNT NUMBER(38) IN DEST_OFFSET NUMBER(38) IN/OUT SRC_OFFSET NUMBER(38) IN/OUT BLOB_CSID NUMBER IN LANG_CONTEXT NUMBER(38) IN/OUT WARNING NUMBER(38) OUT
-Bill
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 23 2009 - 09:37:06 CST

Original text of this message