#4 lcontains on unindexed columns

[gennff]

here, i\'m trying to figure out iff this is expected behaviour?! i wouldn\'t vote, iff asked, to allow lcontains calls against unindexed columns. in my opinion this should result in an error as oracle text does. however, someone else may argue that any other column in the database may be subject to a where clause, no matter whether the column has been index or not. this is a valuable point but doing like this, also any number value, see below, must be searchable without a ldi index as well (using some datatype conversion etc.).

SQL> create table t1 (
2 f1 number primary key,
3 f2 CLOB,
4 f3 varchar2(3));
Tabelle wurde erstellt.

SQL> select f1, to_char(f2), f3, lscore(1) from t1 where lcontains(f2, \'ravi\', 1) > 0;
Es wurden keine Zeilen ausgewõhlt

SQL> select f1, to_char(f2), f3, lscore(1) from t1 where lcontains(f3, \'ravi\', 1) > 0;
Es wurden keine Zeilen ausgewõhlt

SQL> select f1, to_char(f2), f3, lscore(1) from t1 where lcontains(f1, \'ravi\', 1) > 0;
select f1, to_char(f2), f3, lscore(1) from t1 where lcontains(f1, \'ravi\', 1) > 0
*
FEHLER in Zeile 1:
ORA-29900: Operator-Bindung ist nicht vorhanden
ORA-06553: PLS-307: Zu viele Deklarationen von \'LCONTAINS\' entsprechen diesem Aufruf

[gennff]

(MO)
If your index creation fails for any reason this index is not used on your execution plans. So if lcontains is not bound to a domain index it is routed to a different argument call:

public static java.math.BigDecimal TextContains(String text, String keyStr,
String sortBy, ODCIIndexCtx ctx, LuceneDomainIndex[] sctx,
java.math.BigDecimal scanflg) throws SQLException, IOException, ParseException {

In previous releases it uses a simple Java RegExp functionality to evaluate lcontains, but it was eliminated in latest release. The mysterious behavior here is why is not thrown a SQL exception, probably the scan flag in 10g is not the same as in 11g :(There is a commented line in LuceneDomainIndex.java source (lines 1073-1074) which should be uncommented to see which scan flag values
is passed by the RDBMS when lcontains is used outside the where and is not bound to any index. Note that if the scan flags is (flag == 1 && sctx != null &&
sctx[0] != null) it will return 1 which caused that all the rows are considered as positive hits.