Q

ORA-00904 error on select statement

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." How do I fix this? It seems like object r is not connected to anything somehow.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close