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
Synonyms You can create the synonyms to provide location transparency for the remote schema objects like tables, views, sequences, stand-alone subprograms, and packages. Though,

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Example of Cast Operator So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this giv

Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check