oracle exception while using CONTAINS function

The "$" is a stemming shortcut and "*" is for weight.

Where to look is always in the documentation.  All the operators are there:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/oracle-text-CONTAINS-query-operators.html#GUID-6410B783-FC9A-4C99-B3AF-9E0349AA43D1

It isn't just special characters.  There are function operators you need to be concerned with.

Use your search for PT.  My guess it will error as well since it is a Partial Term function that expects paramaters.

I wrote my own function, no I cannot provide it, to clean the passed in string to ensure I had complete control of what was actually searched.

Another problem you have is how the Text index was created.  By default non-word characters cause token separation.  I had to search for email addresses in the text but by default "username@foo.bar" was indexed as three individual tokens "username", "foo" and "bar".  If I searched for the string "username@foo.bar", it was never found.

If you want them kept together when indexed, you need PRINTJOINS when you created the index.

@slightwv - thanks for your comment. I am going to read the oracle documentation.
but thinking about this more, how do we query , if we actually want to search for "*" or say "$" characters then?

Those characters aren't indexed unless you set up printjoins so you cannot search them per say.

You can escape the special characters and sort of find the string but it isn't because it searches those characters.  It ignores them.

Set up a quick test and see.  As you can see, the special characters aren't part of the Text index but the query doesn't syntax out when you escape them BUT it also returns both rows.

SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(col1 varchar2(50));

Table created.

SQL> insert into tab1 values('Hello*World$Test');

1 row created.

SQL> insert into tab1 values('Hello#World^Test');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create index tab1_idx on tab1(col1) indextype is ctxsys.context;

Index created.

SQL>
SQL> select token_text from dr$tab1_idx$i;
HELLO
TEST
WORLD

SQL>
SQL> select * from tab1 where contains(col1,'Hello\*World\$Test') > 0;
Hello*World$Test
Hello#World^Test

Select allOpen in new window

You can also just remove the special characters from the search and find it:

select * from tab1 where contains(col1,'Hello World Test') > 0;
Hello*World$Test
Hello#World^Test

Select allOpen in new window

You still have the issue of strings that are also the same as a Text function.  I double quote those.

SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(col1 varchar2(50));

Table created.

SQL> insert into tab1 values('PT Barnum');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create index tab1_idx on tab1(col1) indextype is ctxsys.context;

Index created.

SQL>
SQL> select token_text from dr$tab1_idx$i;
BARNUM
PT

SQL>
SQL> select * from tab1 where contains(col1,'PT Barnum') > 0;
select * from tab1 where contains(col1,'PT Barnum') > 0
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 4


SQL> select * from tab1 where contains(col1,'"PT" Barnum') > 0;
PT Barnum

Select allOpen in new window

So far we are doing exact string search.  For the above example, if you search 'PT Barnum', what if the indexed text is 'PT Bob Barnum', do you want to find that as well?

Thanks again.

yes, I would like to be able to find, 'PT Bob Barnum' if i search for 'PT Barnum'.  

you mentioned that in sample code you posted,  it is doing exact string search.  is the reason , you are escaping all the special characters and also any text function is within double quotes?

I'm new to oracle.  so I'm not sure what PRINTJOINS  is. I will research this. but for my basic search,

I am planning to use regular expression, to escape any special characters .
and also parse the user input for any text function and escape them as well, by using double quotes or use {} curly braces around them.

does that sound like right direction to go?

thank you so much for your insight.