I am working in JDEVELOPER using JDBC. This program is working with objects. I am getting this error on a SQL statement: "java.sql.SQLException: ORA-00904: "R": invalid identifier." The select statement is:
SELECT VALUE(r) FROM Rectangles r
I do not understand why I am getting an error on the select. How do I fix this? It seems like object r is not connected to anything somehow. Thanks for your help.
I have provided the supporting documentation below. The create of the objects and tables are as follows:
CREATE TYPE point_type AS OBJECT ( x NUMBER, y NUMBER); / CREATE TABLE circles ( radius NUMBER, center point_type); / CREATE TABLE bloom_data OF point_type; / INSERT INTO bloom_data values(2,3); INSERT INTO bloom_data values(3,15); INSERT INTO circles values (3,point_type(5,5)); / CREATE TYPE rectangle_type AS OBJECT ( top_left point_type, width NUMBER, height NUMBER, MAP MEMBER FUNCTION getarea RETURN NUMBER, MEMBER FUNCTION CONTAINS(pt IN point_type) RETURN NUMBER); / CREATE OR REPLACE TYPE BODY rectangle_type AS MAP MEMBER FUNCTION getarea RETURN NUMBER AS BEGIN RETURN width * height; END; MEMBER FUNCTION CONTAINS (pt IN point_type) RETURN NUMBER AS is_inside NUMBER := 0; BEGIN IF (pt.x > top_left.x AND pt.x < top_left.x + width AND pt.y > top_left.y AND pt.y < top_left.y + height) THEN is_inside := 1; END IF; RETURN is_inside; END; END; / CREATE TABLE rectangles ( label VARCHAR2(25), rectangle rectangle_type); / INSERT INTO rectangles VALUES ('ONE', rectangle_type(point_type(10,50),40,20)); / INSERT INTO rectangles VALUES ('TWO', rectangle_type(point_type(0,0),10,10)); /
Why not just do the following:
SELECT * FROM rectangles;You do not need the VALUE function here. If you created your table as an object table, then you could use the VALUE function. To do that, your table creation would look similar to the following:
- Create a type to hold the entire row's values:
CREATE rectangle_type AS OBJECT( label VARCHAR2(25), rectangle rectangle_type);
- Create the object table:
CREATE TABLE rectangle OF rectangle_type;
An object table is a table with only one column, the object type. From this, the VALUE function can be properly used as you have written it.
On that note, if you are writing tables for points, lines and polygons, why not use Oracle Spatial? It looks to me like you're reinventing the wheel.
This was first published in April 2006