REF() function returns "SQL command not properly ended"

Question

I'm trying to familiarize myself with a O/R db, which has led me to try to get object references.

Started out by listing all_objects for a specific user, and just picked one object (CF02) whose type is TABLE i.e. OBJECT_TYPE = 'TABLE'

I then opened the table, and just went with one of the rows whose first field (OBJECT_ID) is 9142055040413031761.

Then I tried to get the ref() on that row.

  SELECT ref(cf) FROM rdbmgr.CF02 as cf
  WHERE OBJECT_ID = 9142055040413031761

Hoping to get a similar result to that in the Oracle books, which is an internalnumber for the location of the object...

Nothing to do with ref - remove the `as` from `as cf`. [You can't use `as` for table aliases in Oracle](https://stackoverflow.com/q/21145028/266304). — Alex Poole, Apr 09 '19 at 16:34
i tried that, it then gives me the error "CF":invalid identifier — jayykaa, Apr 09 '19 at 16:35
Is `CF02` a table of an object type, or a normal table with a object column? Please include the relevant DDL in your question. — Alex Poole, Apr 09 '19 at 16:37
in the ALL_OBJECTS table, under the OBJECT_TYPE column, it comes up as 'TABLE' — jayykaa, Apr 09 '19 at 16:40
i'm also not sure how i would get the DDL - i didn't make this db. - but i would assume that SELECT * ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' would only list the Tables of an object type... — jayykaa, Apr 09 '19 at 16:50
`select dbms_metadata.get_ddl('TABLE', 'CF02', 'RDBMGR') from dual;` but you'd either need powerful privs or be logged in as `rdbmgr`. And you might need to `set long 5000` or similar to see it all. And they you need the DDL for the object type(s) too. — Alex Poole, Apr 09 '19 at 16:54

score 2 · Accepted Answer · answered Apr 09 '19 at 18:22

you can use a ref() function if your table was created from object type.

REF takes as its argument a correlation variable (table alias) associated with a row of an object table or an object view.

e.g. you have an object type:

create or replace TYPE t_pos AS OBJECT 
( 
  x number,
  y number
)
/

you can create a table

 CREATE TABLE position OF t_pos ;

-- insert some data for test
insert into position values (1,2);

so you have a table position now you can so a select with a ref function

select ref(t) from  position  t;

Result:

REF(E)
--------------------------------------------------------------------------------
0000280209587CADBD96F74009BBF01C1596D74E72E7986EC7F3AF40B4A264DA1BE6FE27D30040B2
790000

if you table was created as create table position(x number, y number) you can't use ref function in your select

Have a look at documentation here

thank! so,an object table has to have been created using an object type for oracle to offer any kind of references on the objects within the table... i guess because even though they are similar objects, they're not technically the same object type? — jayykaa, Apr 09 '19 at 18:45

REF() function returns "SQL command not properly ended"

1 Answers1