Oracle Text and Friendly Search Expressions

Oracle Text is a feature of the Oracle database that allows developers to index large portions of data and provide users a search-engine like view into that data. It comes with a powerful set of functionality that allows the experienced or knowledgeable user to craft very specific queries. But what about everybody else? Most users want the search functionality to be basic and/or follow what they have become accustom to with the various online-search engines.

Today I will show you how to provide a sub-set of Oracle text features (specifically using the context index and contains clause) that mimic the major features of most online search tools. This will allow you to use Oracle Text in your application without forcing users into a new set of search expressions.

Before we get started with the search expressions, we need to build and populate a test table. This test table uses a clob, but you could also index a blob column or search against a multi-column index like the one I detail here.

create table simple_search_ex (  ident number,
content clob, );

insert into simple_search_ex (ident, content) values (1, 'This is the first row, it is red');
insert into simple_search_ex (ident, content) values (2, 'This is the second row, it is blue');
insert into simple_search_ex (ident, content) values (3, 'This is the third row, it is red');
insert into simple_search_ex (ident, content) values (4, 'This is the fourth row, it is green');

Now that we have the table built, we need to add the Oracle text index. I'm going to use the sync on commit parameter so that updating the column will update the index

create index simple_search_ex_idx on simple_search_ex(content)
indextype is ctxsys.context PARAMETERS ('SYNC ( ON COMMIT )');

Once we have the test data setup, we can get down to business. Oracle Text has a ton of complex search options and operators that you can exploit (see the documentation for the full list). To keep it simple for my users, I'm going to stick with the basics. My search will allow the user of  "AND", "OR", "NOT",  "()" and expressions in double quotes  or surrounded by braces "{}" will be evaluated as a single word. Finally spaces will translate to "AND" in my algorithm.

Limiting myself to the simple options means we can transform the user's input into the appropriate expression for the CONTAINS clause with a few regexp_replace calls. We simply want to transform the user's English search expression into one that Oracle Text will allow for. I've commented the code below, the only really tricky part is ensuring that we do not replace characters inside of quotes. That requires a few extra replacements to work properly.

Before we can create the function, we must create a pl/sql table to hold the tokenized input:

create or replace type vc_lst as table of varchar2(4000);

Now we can create the function:

create or replace function translate_search_expression(p_input varchar2) return varchar2 is v_procedure_c constant varchar2(30) := 'translate_search_expression'; v_location_i integer; -- This variable tracks our location within this procedure. v_input varchar2(31000); v_word_lst vc_lst := vc_lst(); v_return varchar2(31000); l_n number; begin -- Set Tracing Information. v_location_i := 500; dbms_application_info.set_client_info('Session="'||v('APP_SESSION')||'"'); dbms_output.put_line(v_procedure_c); v_location_i := 1000; v_return := p_input; v_location_i := 1500; --Replace "some text" with {some text} v_return := regexp_replace(v_return, '"(.*?)"', '{\1}',1,0,'i'); v_return := regexp_replace(v_return, '"', '', 1,0,'i'); v_location_i := 1600; --Replace ( and ) with ' ( ' and ' ) ' if not wrapped in spaces v_return := regexp_replace(v_return, '([^\s])\(([^\s])', '\1 ( \2',1,0); v_return := regexp_replace(v_return, '([^\s])\)([^\s])', '\1 ) \2',1,0); v_location_i := 2000; --Split the search expression on spaces loop l_n := instr( v_return, ' ' ); exit when (nvl(l_n,0) = 0); v_word_lst.extend; v_word_lst( v_word_lst.count ) := ltrim(rtrim(substr(v_return,1,l_n-1))); v_return := substr( v_return, l_n+1 ); end loop; --Replace non-supported oracle text whitespace or operators if (v_word_lst.count > 0) then for i in v_word_lst.first..v_word_lst.last loop --Underscore is seen as a wildcard usually, wrap it so it is literal if (instr(v_word_lst(i), '_') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --the dash (-) is normally minus (lowers score if second word exists) elsif (instr(v_word_lst(i), '-') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure the comma is literal, not accumulate elsif (instr(v_word_lst(i), ',') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure equal sign is literal not equivilance elsif (instr(v_word_lst(i), '=') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure semi-colon is literal, not near elsif (instr(v_word_lst(i), ';') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure explination point is literal not soundex elsif (instr(v_word_lst(i), '!') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure dolar sign is literal not stem elsif (instr(v_word_lst(i), '$') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; end if; end loop; end if; v_location_i := 3000; --Put string back together with spaces v_return := ''; if (v_word_lst is not null and v_word_lst.count > 0) then for i in v_word_lst.first..v_word_lst.last loop v_return := v_return || ' ' || v_word_lst(i); end loop; --trim off excess delimiters at beginning and end v_return := ltrim(rtrim(v_return, ' '), ' '); end if; v_location_i := 3500; --Replace keyword " AND " with & unless it exists inside of {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+AND\s+|&', '\1&',1,0, 'i'); --Now replace "(&" with " (" and "&)" with ") " and remove any leading or trailing spaces from the string v_return := regexp_replace(v_return, '\s*\((&|\s)*', ' (',1,0); v_return := regexp_replace(v_return, '(&|\s)*\)\s*', ') ',1,0); v_return := ltrim(rtrim(v_return, ' ')); --dbms_output.put_line('after & replacement: '||v_return); --Replace keyword " OR " with | unless it is inside {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+OR\s+|\|', '\1|',1,0, 'i'); --dbms_output.put_line('after | replacement: '||v_return); --Replace keyword " NOT " with ~ unless it is inside {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+NOT\s+|~', '\1~',1,0, 'i'); --dbms_output.put_line('after ~ replacement: '||v_return); --Now replace any remaining spaces with &, unless they are inside {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+', '\1&',1,0, 'i'); --dbms_output.put_line('after " " replacement: '||v_return); --Here we must cleanup the condition where we'll get }~|& due to above regex v_return := regexp_replace(v_return, '}[&~\|]+&', '}&',1,0,'i'); --dbms_output.put_line('after && replacement: '||v_return); --Here we must cleanup the condition where we'll get }~| due to above regex v_return := regexp_replace(v_return, '}[~\|]+\|', '}|',1,0,'i'); --In some cases we'll see &|& or &~&, so fix those v_return := regexp_replace(v_return, '&\|&', '|',1,0,'i'); v_return := regexp_replace(v_return, '&~&', '~',1,0,'i'); --In some cases we'll see &&( or )&&, so fix those v_return := regexp_replace(v_return, '&&\(', '&(',1,0,'i'); v_return := regexp_replace(v_return, '\)&&', ')&',1,0,'i'); --In some cases we'll see |&( or )&|, so fix those v_return := regexp_replace(v_return, '\|&\|\(', '|(',1,0,'i'); v_return := regexp_replace(v_return, '\)&\|', ')|',1,0,'i'); --In some cases we'll see ~&( or )&~, so fix those v_return := regexp_replace(v_return, '~&\(', '~(',1,0,'i'); v_return := regexp_replace(v_return, '\)&~', ')~',1,0,'i'); --Finally ensure we don't have any operatiors at the beginning or end and return the expression v_return := regexp_replace(v_return, '^[~&\|]+', '',1,1,'i'); v_return := regexp_replace(v_return, '[~&\|]+$', '',1,1,'i'); return v_return; EXCEPTION when OTHERS then return p_input; END translate_search_expression;
/

The following examples demonstrate how this code will translate your users' search expressions into the actual expression.

SQL> select translate_search_expression('hello world') results from dual; RESULTS -------------------------------------------------------------------------------- hello&world
SQL> select translate_search_expression('"hello world"') results from dual; RESULTS -------------------------------------------------------------------------------- {hello world}
SQL> select translate_search_expression('this~(is history) OR demo') results from dual; RESULTS -------------------------------------------------------------------------------- this~(is&history)|demo

To use this in your query, simply use the results of the function as the search expression portion of the CONTAINS clause like so:

SQL> select ident, content from simple_search_ex where contains(content, translate_search_expression('blue or green'), 1);
IDENT CONTENT --------------- ---------------------------------------------- 2 This is the second row, it is blue 4 This is the fourth row, it is green

Now you should be able to fully enable Oracle Text searches in your pl/sql or Apex application while allowing your users to search as they are used-to, while still providing the power of AND/OR/NOT expressions. I hope you have found this useful.

---

add_months() to timestamps in Oracle

Oracle has two classes of date/time types, "date" and "timestamp". The "date" type is very mature and has a variety of useful manipulation functions. Some of these functions are lacking in the more precise "timestamp" data types.

Today I'm going to provide one way of implementing the "date" type workhourse "add_months" for timestamps.

Why would you need this? Well, in my case, I'm writing a global application where users can request data for the next N months. All dates and times are implemented as timestamps with local time zone to present dates/times in a user's timezone. This is done so the user doesn't have to try and translate times from halfway around the world.

My problem is that Oracle's add_months() will work on a timestamp, but it doesn't reliably save and return the timezone data. I want to be sure timezone data is preserved, so I wrote my own add_months_tz().

My function performs the following basic steps:

• Extract seconds (and fractions of a second) and time zone data from incoming timestamp
• Alter the session time zone to match the incoming timestamp's time zone
• Use Oracle's built-in add_months (which returns a date)
• Use conversion functions to convert the date into a timestamp using the saved seconds
• Restore the original session time zone
• Return the newly built timestamp

In testing  I've noticed that sometimes (and with no visible consistency) I got the following error:

ORA-01862: the numeric value does not match the length of the format item

To prevent this from breaking my application, I wrapped the timestamp creation logic in a block and used the exception handler to create the timestamp without the fractions of a second.

Now that you have the theory and reasoning, here is the code:

create or replace function add_months_tz ( p_timestamp in timestamp with time zone, p_added_months in number) return timestamp with time zone is
--Created by Matthew Cox
--Please feel free to copy, use or alter as you see fit
--I'd appreciate it if you give me credit in your application comments
v_location_i number := 100; o_secs number; o_region varchar2(128); t_date date; r_timestamp timestamp with local time zone; v_session_timezone varchar2(128); begin --save session timezone so we can put it back v_session_timezone := sessiontimezone; v_location_i := 1000; --Extract the seconds and timezone region from the incoming timestamp o_secs := extract(second from p_timestamp); o_region := extract (timezone_region from p_timestamp); --If there is no region name specified, use the offsets if (o_region = 'UNKNOWN') then o_region := extract(timezone_hour from p_timestamp)||':'||extract(timezone_minute from p_timestamp); end if; --Alter the session timezone to ensure proper translation in our cast functions execute immediate 'alter session set time_zone = '''||o_region||''''; v_location_i := 3000; --We convert the timestamp to a date to do the month math t_date := trunc(p_timestamp, 'MI'); --Debug output --dbms_output.put_line('Extracted seconds: '||o_secs); --dbms_output.put_line('Extracted region: '||o_region); --dbms_output.put_line('converted date: '||t_date); v_location_i := 4000; t_date := add_months(t_date, p_added_months); --dbms_output.put_line('after add_months date: '||t_date); v_location_i := 5000; --Now we build a new timestamp with our manipulated date and extracted seconds begin r_timestamp := to_timestamp(to_char(t_date, 'DD-MON-YYYY HH24:MI')||o_secs, 'DD-MON-YYYY HH24:MI:SS.FF'); exception when others then v_location_i := 5100; --Use the simple date to the second r_timestamp := to_timestamp(to_char(t_date,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'); end; --Debugging output --dbms_output.put_line('final timestamp: '||r_timestamp); --dbms_output.put_line('final timestamp region name: '||extract(timezone_region from r_timestamp)); --dbms_output.put_line('final timestamp region offset: '||extract(timezone_hour from r_timestamp)||':'||extract(timezone_minute from r_timestamp)); --Put the session time zone back execute immediate 'alter session set time_zone = '''||v_session_timezone||''''; return r_timestamp; exception when others then dbms_output.put_line('Error at '||v_location_i||': '||sqlerrm); return null; end add_months_tz; /

Feel free to use this code as you see fit, but please be courteous and give me credit (even if only in your pl/sql comments).

---

Keyword Search via Oracle Text

Oracle text is a feature available in the Oracle Database and is used to provide keyword search indexing to large blocks of text and even binary formatted files like Word and PDF files.

As part of a project I am working on, I need to create a keyword search index that spans multiple columns. This will allow my users to search for keywords in the title, abstract and content of a note entered into the system. The note could be in the form of an uploaded file, or it could be manually entered through the interface.

This post will take you step-by-step through the process of building the back-end Oracle Text index and then using the Oracle SQL Contains clause to leverage that index for full text search across multiple columns.

Before we can create the search indexes, we need a table:

create table note_content ( TITLE VARCHAR2(256 BYTE) --Note Title , ABSTRACT VARCHAR2(4000 BYTE) --Short Description , TEXT_CONTENT CLOB --Content for text-based notes , FILE_CONTENT BLOB --Storage for binary file , FILE_NAME VARCHAR2(400 BYTE) --File name for binary file , FILE_MIME_TYPE VARCHAR2(256 BYTE) --Mime type for binary file , INDEX_FLAG VARCHAR2(1 BYTE) --Column used to control index --sync (more later) , NOTE_TYPE VARCHAR2(1 BYTE) --Flag indicating text/binary );


insert into note_content(title, abstract, text_content)
values ('foo', 'bar', 'this is a test');


insert into note_content(title, abstract, text_content)
values ('abc', 'def', 'foo is the source of bar');

insert into note_content(title, abstract, text_content)
values ('123', '456', '7890');

commit;

Now that we have our table and data, we need to setup the multi-column Oracle Text index. This is where the magic happens as we'll allow the users to search for keywords in the notes title, abstract or content (text or binary).

First we need to setup a multi-column data store that includes each of the columns we want to be searchable:

begin ctx_ddl.create_preference('note_multi_store','MULTI_COLUMN_DATASTORE'); ctx_ddl.set_attribute('note_multi_store', 'columns', 'title,abstract,text_content,file_content' ); end; /

After creating our data store, we need to create a CONTEXT type index. Since we'll be specifying our custom data store, we can create the index on any column we want. In this example we'll choose the INDEX_FLAG column:

CREATE INDEX note_content_search_idx ON note_content(index_flag) INDEXTYPE IS ctxsys.context PARAMETERS('DATASTORE note_multi_store sync (on commit)');

Note: Context indexes require special handling to ensure the content is indexed and available for search. While the create statement above will sync on commit (10g+), the INDEX_FLAG column must be updated as part of the transaction to flag the record for synchronization. Without updating INDEX_FLAG the sync won't happen and new content will not be searchable. We can automate this update by using a trigger:

create or replace TRIGGER note_content_audit BEFORE INSERT or update ON note_content FOR EACH ROW BEGIN :new.index_flag := 'Y'; END;
/

Now that we have the trigger, let's update our records to ensure index_flag is set and the context index is updated

update note_content
set title = title;

select title, index_flag
from note_content;


TITLE INDEX_FLAG
----------------------
foo Y abc Y 123 Y

It is also possible to manually synchronize the index or create a dbms_scheduler_job to do so on an interval. This is advisable if the sync on commit is causing performance problems, or you do not need the note content indexed immediately. It is also advisable to optimize the index periodically to  eliminate data fragmentation. The relevant commands are as follows:

--Synchronize Oracle Text indexes for full-text search using 2MB of memory CTX_DDL.SYNC_INDEX('note_content_search_idx', '2M'); --Optimize the oracle text indexes for full-text search --Full indexing (compact + remove) maximum 60 minutes each ctx_ddl.optimize_index('note_content_search_idx', 'FULL', 60);

To leverage the oracle text index we need to use the contains clause in our sql statements. We can use any column as the first argument. In this example I am going to use the same INDEX_FLAG column we are using to control synchronization. Because the Index uses our custom data store, the indicated column doesn't matter:

Select title, abstract, text_content from note_content where contains (index_flag, 'foo and bar', 1) > 0;

TITLE ABSTRACT TEXT_CONTENT
----------------------------------------- foo bar this is a test abc def foo is the source of bar

The above query will return any record where foo and bar occur in the title, abstract, text_content of file_content columns. Note that 'foo' may occur in one column while 'bar' can be in a separate column. This query also demonstrates the use of boolean AND/OR operators to target keyword searches.

This article should provide a solid foundation for using Oracle Text to implement full-text search for a table. Oracle text is a robust product that allows for a high degree of customization. It also has tools to rank results and/or highlight and display resulting documents. For a more general introduction, please see Oracle's Introduction to Oracle Text article.

---