A client of mine wanted to use the power of Oracle Text to search for keywords in BLOBs stored within their database, using APEX as the front-end.
I’d never worked with Oracle Text before, and did not find too many posts about it’s integration with APEX, so I thought I’d write up how I went about it, as it may be of interest to others.
Step 1: Privileges for schema owner
The schema owner where your app resides will need some grants before being able to use the Oracle Text packages. Ensure you’ve granted the following:
GRANT CTXAPP to ;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO ;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO ;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO ;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO ;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO ;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO ;
GRANT EXECUTE ON CTXSYS.CTX_THES TO ;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO ;
Step 2: Create the index on your BLOB table
create index hdocsx on ) indextype is ctxsys.context
parameters ('filter ctxsys.auto_filter');
I used the auto-filter parameter as this is recommended for PDF documents, which is what we happened to be working with. Read the Oracle documentation to ensure you use the filter that is right for your requirements.
Important: ensure your table to be indexed has a primary key defined before you create your index or it will be unusable for some of the Oracle Text functions such as MARKUP or SNIPPET, for example.
Step 3: Create an APEX report page, with a regular Report Region on your BLOB table
You can now include Oracle Text functions such as CONTAINS. For example, with a blob field ‘CONTENTS’, and a form search field ‘:P1_SEARCH’, you could use
select file_name, doc_id, xxx, yyy
from BLOB_TABLE
where CONTAINS(contents,’:P1_SEARCH’)>0
Such a query would return any documents that contain the […]