Querying Object Tables
Object Oriented Terms
Collections Varrays Oracle
Querying Nested Tables
Querying Complex SQL Object
SQL Query Example
Using Oracle Objects
Querying Related Tables
Understanding Related Object Tables
Analyzing Dangling References
Writing deref Queries
Oracle deref Syntax
Creating PL/SQL Block
Querying Nested Tables
Flattening nested Table
Flattening nested Table Example
Simple varray Query
Query to Flatten varray
Query Flattens Nested Table
Procedure retrieves Nested Table
Varrays Nested Table Conclusion
Object Table Modification
Insert into Table
SQL Insert Rows
Write SQL to Update Object Table
Subquery in Update
Delete Rows from object Table
DML Statements Conclusion
Updating Rows Example
Modifying Nested Tables
Insert Rows into Nested Table
Delete Command for Nested varray
Updating Varray Rows
inserting Data in Nested Table(Example)
Varray insert Command
PL/SQL Object Characterization
If you are familiar with object terminology, an object type is similar to a class. An object type is a user-defined composite data type that can be created and maintained using the Data Definition Language (DDL). An object type represents a data structure that encapsulates data, functions and procedures needed to manipulate data.
The variables that constitute the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods. The type is simply a template and holds no data on its own. You can create variables, tables, columns, and other constructs with the object type.
This example defines the physical address of a customer as an object type.
This attributes of the object type are defined as scalar data types..
Object Oriented Programming
An object is an instance of an object type. The object is the location where the actual data resides. There are two types of objects: 1) persistent and 2) transient. An object stored within a table is a persistent object. An object that exists temporarily within PL/SQL variables is a transient object.
The following example defines a customer object.
The object has attributes of its own. Additionally, this object is associated with the address object type.
An attribute is an integral part of an Oracle object, similar to a column within a table. Each attribute must be defined as a single data type, either scalar, such as VARCHAR2 or INTEGER, or composite, such as a user-defined nested-table or another object. In the above example, CUST_ID, FIRST_NAME, LAST_NAME, FULL_ADDRESS, PHONE_LIST, LAST_UPDATE, and UPDATED_BY_USER are the attributes of the object.
A method is a procedure or function, usually implemented within PL/SQL and stored within the database or written in a language such as C and stored externally. A method implements specific operations that an application can perform on the data.
An object table is a table defined on an object or a set of objects. The object table can have its own attributes other than the attributes of the associated object.