Using REF, DEREF and VALUE

The VALUE routine is demonstrated in listing 4. VALUE can be used to assign the value from one instance of an object table to another.  For Normal selection, the standard SELECT works better as is also shown in listing 4.

SQL> select value(d) from dept d where d.dname like 'Home
 
D(DNAME, ADDRESS)
---------------------------------------------------------
DEPT_T('Home', '5055 Forest Run')
 
SQL> select * from dept d where d.dname like 'Home%';
 
DNAME                  ADDRESS
--------------------   --------------------
Home                   5055 Forest Run

Listing 4: Example of use of VALUE Routine.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm