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));
/
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation