Database Software

Hello,

I seem to be getting an error when using any type of SDO function (SDO_RELATE, SDO_FILTER, SDO_ NN, ... ) on my spatially indexed data. The error I'm getting is this:

ERROR at line 1:
ORA-29903: error in executing ODCIIndexFetch() routine
ORA-13236: internal error in R-tree processing: []
ORA-13236: internal error in R-tree processing: [Recursive fetch error]

The interesting part of this is that I only get this error if I insert more than 31 entries into the table. All the relevant code is posted below, what I do is create the table, then create the metadata from the sqlplus interface. After that I run a java program that does Clear() then Insert() then Create_Indices(). All these run just fine (at least run without telling me about any errors). But as soon as that is all done and I do an SDO query on the data, for example:

SELECT location stop_range_poly_area
FROM stops
WHERE SDO_FILTER
(
location,
SDO_GEOMETRY
(
2003, null, null,
SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(300,300, 600,300, 600,600, 300,600, 300,300)
)
) = 'TRUE';

I get the above mentioned error. But if I change my data so that it doesn't have more than 31 entries (doesn't matter which 31 entries, just so long as it doesn't exceed that number) that query (and all of my other SDO test queries on the stops table) seems to work just fine.

Thanks for looking at this,

Brad

---- SQLPLUS Code -----------------------------------------------------------------------------------------------------------

CREATE TABLE stops
(
stop_id VARCHAR2(4) PRIMARY KEY,
address VARCHAR2(256),
location sdo_geometry
);

INSERT INTO user_sdo_geom_metadata VALUES
(
'stops',
'location',
MDSYS.SDO_DIM_ARRAY
(
MDSYS.SDO_DIM_ELEMENT('X', 0, 600, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 800, 0.005)
),
null
);

----- Java Code -----------------------------------------------------------------------------------------------------------------

//-------------------------------------------------------------------------
// Clear
//
// Description:
// This function clears all the data in the stop tables in the database
// given in the connection.
//
//-------------------------------------------------------------------------

public static void Clear (Connection connection)
{
Statement statement;

try
{
statement = connection.createStatement();
statement.executeUpdate("DROP INDEX stops_index");
statement.executeUpdate("DELETE stops");
statement.close();
}
catch (SQLException e)
{
System.err.println("SQLExcpetion (Stop.Clear()): " + e.getMessage());
}
}

//-------------------------------------------------------------------------
// Create_Indices
//
// Description:
// Create indices for stops table
//
//-------------------------------------------------------------------------

public static void Create_Indices (Connection connection)
{
Statement statement;
String sql_query;

sql_query = "CREATE INDEX stops_index ON stops(location) " +
"INDEXTYPE IS MDSYS.SPATIAL_INDEX";

try
{
statement = connection.createStatement();
statement.executeUpdate(sql_query);
statement.close();
}
catch (SQLException e)
{
System.err.println("SQLExcpetion (Stop.Create_Indices()): " + e.getMessage());
}
}

//-------------------------------------------------------------------------
// Insert
//
// Description:
// This function inserts this object into the stops table in the database
// given in the connection.
//
//-------------------------------------------------------------------------

public void Insert (Connection connection)
{
Statement statement;
String sql_query;

sql_query = "INSERT INTO stops VALUES ('" +
stop_id + "', '" +
address + "', " +
"sdo_geometry(2001, null, sdo_point_type(" +
location.x + "," +
location.y + ",null), null, null)" +
")";

try
{

statement = connection.createStatement();
statement.executeUpdate(sql_query);
statement.close();

}
catch (SQLException e)
{
System.err.println("SQLException (Stop.Insert()):" + e.getMessage());
}
}

Message was edited by: loos to include the changes proposed by the second poster.