Complex CONTEXT index
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 23 2009 - 09:37:06 CST