CODEWITHSUNDEEP
sqloracle-databaseref
jayykaa
jayykaa

Reputation: 143

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

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...

Upvotes: 1

Views: 101

Answers (1)

hotfix
hotfix

Reputation: 3396

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

Upvotes: 2

Related Questions