Using operator deref - manipulating objects in pl sql, PL-SQL Programming

Using Operator DEREF:

You cannot navigate through refs within the PL/SQL procedural statements. Rather than, you should use the operator DEREF in the SQL statement. The DEREF take as its argument that reference to an object, and then returns the value of that object. If the ref is dangling, the DEREF returns a null object.

In the illustration below, you dereference the ref to a Person object. Note that you select the ref from dummy table dual. You do not require specifying an object table and search criteria as each object stored in an object table has an immutable, exclusive object identifier that is a part of every ref to that object.


p1 Person;

p_ref REF Person;

name VARCHAR2(15);



/* Suppose that p_ref holds a valid reference

to an object stored in an object table. */

SELECT DEREF(p_ref) INTO p1 FROM dual;

name := p1.last_name;

You can use DEREF in successive SQL statements to dereference refs, as the

following example shows:

CREATE TYPE PersonRef AS OBJECT (p_ref REF Person)



name VARCHAR2(15);

pr_ref REF PersonRef;

pr PersonRef;

p Person;



/* Assume pr_ref holds a valid reference. */

SELECT DEREF(pr_ref) INTO pr FROM dual;

SELECT DEREF(pr.p_ref) INTO p FROM dual;

name := p.last_name;




The later illustration shows that you cannot use the operator DEREF within procedural statements:



p1 := DEREF(p_ref); -- illegal

Within the SQL statements, you can use the dot notation to navigate throughout the object columns to ref attributes and through one ref attribute to the other. You can also navigate through the ref columns to attributes if you use a table alias. For illustration, the syntax below is valid:




Suppose that you have to run the SQL*Plus script below that creates object types Address and Person and object table persons:


street VARCHAR2(35),

city VARCHAR2(15),

state CHAR(2),

zip_code INTEGER)



first_name VARCHAR2(15),

last_name VARCHAR2(15),

birthday DATE,

home_address REF Address, -- shared with other Person objects

phone_number VARCHAR2(15))


CREATE TABLE persons OF Person


The Ref attribute home_address corresponds to a column in the object table persons that holds refs to the Address objects stored in some another table. After populating the tables, you can select a particular address by de-referencing its ref, as shown:


addr1 Address,

addr2 Address,



SELECT DEREF(home_address) INTO addr1 FROM persons p

WHERE p.last_name = 'Derringer';

In the illustration below, you navigate through ref column home_address to attribute the street. In this situation, the table alias is needed.


my_street VARCHAR2(25),



SELECT p.home_address.street INTO my_street FROM persons p

WHERE p.last_name = 'Lucas';

Posted Date: 10/6/2012 8:22:34 AM | Location : United States

Related Discussions:- Using operator deref - manipulating objects in pl sql, Assignment Help, Ask Question on Using operator deref - manipulating objects in pl sql, Get Answer, Expert's Help, Using operator deref - manipulating objects in pl sql Discussions

Write discussion on Using operator deref - manipulating objects in pl sql
Your posts are moderated
Related Questions
Project Description: I want somebody who can help me with an idea that I have been working on for a few months now. The person will require extensive knowledge of warcraft 3 PvP

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=



Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

Map and Order Methods: The values of the scalar datatype like CHAR or REAL have a predefined order that allows them to be compared. While, the instances of an object type has