Inserting objects in pl sql, PL-SQL Programming

Inserting Objects:

You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:



VALUES ('Jenifer', 'Lapidus', ...);

Or else, you can use the constructor for the object type Person to insert an object into the object table persons:



VALUES (Person('Albert', 'Brooker', ...));

In the later illustration, you use the RETURNING clause to store the Person refs in local variables. Note that how the clause mimics a SELECT statement. The RETURNING clause can also use in the UPDATE and DELETE statements.


p1_ref REF Person;

p2_ref REF Person;



INSERT INTO persons p

VALUES (Person('Paul', 'Chang', ...))


INSERT INTO persons p

VALUES (Person('Ana', 'Thorne', ...))


To insert the objects into an object table, you can use a sub query which returns objects of the similar type. The illustration is as shown below:


INSERT INTO persons2

SELECT VALUE(p) FROM persons p

WHERE p.last_name LIKE '%Jones';

The rows copied to another object table persons2 are given the new object identifiers. The object identifiers are not copied from the object table persons. The scripts below create a relational table named department that has a column of the type Person, and then inserts a row into the table. Note that how constructor Person () gives a value for the column manager.

CREATE TABLE department (

dept_name VARCHAR2(20),

manager Person,

location VARCHAR2(20))


INSERT INTO department

VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles')


Posted Date: 10/6/2012 8:23:59 AM | Location : United States

Related Discussions:- Inserting objects in pl sql, Assignment Help, Ask Question on Inserting objects in pl sql, Get Answer, Expert's Help, Inserting objects in pl sql Discussions

Write discussion on Inserting objects in pl sql
Your posts are moderated
Related Questions
Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

%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

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa