5.5.5.1 Handling Data Types on Oracle Text

Text indexes using Oracle Text are created over the K and V text columns of the property graph tables. In order to provide text indexing capabilities on all available data types, Oracle populates the V column with a string representation of numeric, spatial, and date time key/value pairs.

To specify the date time and numeric formats used when populating the V column, you can use the methods setNumberToCharSqlFormatString and setTimeToCharSqlFormatString. The following code snippet shows how to set the date time and numeric formats in a property graph instance.

OraclePropertyGraph opg = OraclePropertyGraph.getInstance(args,       
                                                          szGraphName);
opg.setNumberToCharSqlFormatString("TM9");
opg.setTimeToCharSqlFormatString("SYYYY-MM-DD\"T\"HH24:MI:SS.FF9TZH:TZM");

When executing a text search query over a numeric or date time value, you should use a text expression using the format associated to the property graph. OraclePropertyGraph includes a utility API opg.parseValueToCharSQLFormatString that lets you parse a numeric or date time object into format used in the V column storage. The following code snippet calls this function with a date value and creates a text query object out of the retrieved text.

Date d = new java.util.Date(100l);
String szDate = opg.parseValueToCharSQLFormatString(d);

// Create the text query object for Oracle Text
OracleTextQueryObject otqo 
               = OracleTextQueryObject.getInstance(szDate /* query body */, 
                                                   1 /* score */, 
                                                   ScoreRange.POSITIVE /* Score range */,   
                                                   Direction.ASC /* order by direction);