Release 2 (8.1.6)
Part Number A76976-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Object-Relational Features Release 2 (8.1.6) Part Number A76976-01 |
|
This chapter has an extended example of how to use user-defined datatypes (Oracle objects). The example shows how a relational model might be transformed into an object-relational model that better represents the real-world entities that are managed by an application.
This chapter contains the following sections:
User-defined types are schema objects in which users formalize the data structures and operations that appear in their applications.
The example in this chapter illustrates the most important aspects of defining and using user-defined types. One important aspect of using user-defined types is creating methods that perform operations on objects. In the example, definitions of object type methods use the PL/SQL language. Other aspects of using user-defined types, such as defining a type, use SQL.
PL/SQL and Java provide additional capabilities beyond those illustrated in this chapter, especially in the area of accessing and manipulating the elements of collections.
Client applications that use the Oracle Call Interface (OCI), Pro*C/C++, or Oracle Objects for OLE (OO4O) can take advantage of its extensive facilities for accessing objects and collections, and manipulating them on clients.
|
See Also:
|
This example is based on a typical business activity: managing customer orders. We demonstrate how the application might evolve from relational to object-relational, and how you could write it from scratch using a pure object-oriented approach.
The basic entities in this example are:
As you can see from Figure 8-1, a customer has contact information, so that the address and set of telephone numbers is exclusive to that customer. The application does not allow different customers to be associated with the same address or telephone numbers. If a customer changes her address, the previous address ceases to exist. If someone ceases to be a customer, the associated address disappears.
A customer has a one-to-many relationship with a purchase order: a customer can place many orders, but a given purchase order is placed by one customer. Because a customer can be defined before they place an order, the relationship is optional rather than mandatory.
Similarly, a purchase order has a many-to-many relationship with a stock item. Because this relationship does not show which stock items appear on which purchase orders, the entity-relationship has the notion of a line item. A purchase order must contain one or more line items. Each line item is associated only with one purchase order.
The relationship between line item and stock item is that a stock item can appear on zero, one, or many line items, but each line item refers to exactly one stock item.
The relational approach normalizes everything into tables. The table names are Customer_reltab, PurchaseOrder_reltab, and Stock_reltab.
Each part of an address becomes a column in the Customer_reltab table.
Structuring telephone numbers as columns sets an arbitrary limit on the number of telephone numbers a customer can have.
The relational approach separates line items from their purchase orders and puts each into its own table, named PurchaseOrder_reltab and LineItems_reltab. As depicted in Figure 8-1, a line item has a relationship to both a purchase order and a stock item. These are implemented as columns in LineItems_reltab table with foreign keys to PurchaseOrder_reltab and Stock_reltab.
The relational approach results in the following tables:
The Customer_reltab table has the following definition:
CREATE TABLE Customer_reltab ( CustNo NUMBER NOT NULL, CustName VARCHAR2(200) NOT NULL, Street VARCHAR2(200) NOT NULL, City VARCHAR2(200) NOT NULL, State CHAR(2) NOT NULL, Zip VARCHAR2(20) NOT NULL, Phone1 VARCHAR2(20), Phone2 VARCHAR2(20), Phone3 VARCHAR2(20), PRIMARY KEY (CustNo) ) ;
This table, Customer_reltab, stores all the information about customers, which means that it fully contains information that is intrinsic to the customer (defined with the NOT NULL constraint) and information that is not as essential. According to this definition of the table, the application requires that every customer have a shipping address.
Our Entity-Relationship (E-R) diagram showed a customer placing an order, but the table does not make allowance for any relationship between the customer and the purchase order. This suggests that the relationship must be managed by the purchase order.
The PurchaseOrder_reltab table has the following definition:
CREATE TABLE PurchaseOrder_reltab ( PONo NUMBER, /* purchase order no */ Custno NUMBER references Customer_reltab, /* Foreign KEY referencing customer */ OrderDate DATE, /* date of order */ ShipDate DATE, /* date to be shipped */ ToStreet VARCHAR2(200), /* shipto address */ ToCity VARCHAR2(200), ToState CHAR(2), ToZip VARCHAR2(20), PRIMARY KEY(PONo) ) ;
As expected, PurchaseOrder_reltab manages the relationship between the customer and the purchase order by means of the foreign key (FK) column CustNo, which references the CustNo key of the PurchaseOrder_reltab. Because the table makes no allowance for the relationship between the purchase order and its line items, the list of line items must handle this.
The LineItems_reltab table has the following definition:
CREATE TABLE LineItems_reltab ( LineItemNo NUMBER, PONo NUMBER REFERENCES PurchaseOrder_reltab, StockNo NUMBER REFERENCES Stock_reltab, Quantity NUMBER, Discount NUMBER, PRIMARY KEY (PONo, LineItemNo) ) ;
|
Note:
The |
The table name is in the plural form LineItems_reltab to emphasize to someone reading the code that the table holds a collection of line items.
As shown in the E-R diagram, the list of line items has relationships with both the purchase order and the stock item. These relationships are managed by LineItems_reltab by means of two foreign key columns:
PONo, which references the PONo column in PurchaseOrder_reltab
StockNo, which references the StockNo column in Stock_reltab
The Stock_reltab table has the following definition:
CREATE TABLE Stock_reltab ( StockNo NUMBER PRIMARY KEY, Price NUMBER, TaxRate NUMBER ) ;
In our application, statements like these insert data into the tables:
INSERT INTO Stock_reltab VALUES(1004, 6750.00, 2) ; INSERT INTO Stock_reltab VALUES(1011, 4500.23, 2) ; INSERT INTO Stock_reltab VALUES(1534, 2234.00, 2) ; INSERT INTO Stock_reltab VALUES(1535, 3456.23, 2) ;
INSERT INTO Customer_reltab VALUES (1, 'Jean Nance', '2 Avocet Drive', 'Redwood Shores', 'CA', '95054', '415-555-1212', NULL, NULL) ; INSERT INTO Customer_reltab VALUES (2, 'John Nike', '323 College Drive', 'Edison', 'NJ', '08820', '609-555-1212', '201-555-1212', NULL) ;
INSERT INTO PurchaseOrder_reltab VALUES (1001, 1, SYSDATE, '10-MAY-1997', NULL, NULL, NULL, NULL) ; INSERT INTO PurchaseOrder_reltab VALUES (2001, 2, SYSDATE, '20-MAY-1997', '55 Madison Ave', 'Madison', 'WI', '53715') ;
INSERT INTO LineItems_reltab VALUES(01, 1001, 1534, 12, 0) ; INSERT INTO LineItems_reltab VALUES(02, 1001, 1535, 10, 10) ; INSERT INTO LineItems_reltab VALUES(01, 2001, 1004, 1, 0) ; INSERT INTO LineItems_reltab VALUES(02, 2001, 1011, 2, 1) ;
The application can execute queries like these:
SELECT C.CustNo, C.CustName, C.Street, C.City, C.State, C.Zip, C.phone1, C.phone2, C.phone3, P.PONo, P.OrderDate, L.StockNo, L.LineItemNo, L.Quantity, L.Discount FROM Customer_reltab C, PurchaseOrder_reltab P, LineItems_reltab L WHERE C.CustNo = P.CustNo AND P.PONo = L.PONo AND P.PONo = 1001 ;
SELECT P.PONo, SUM(S.Price * L.Quantity) FROM PurchaseOrder_reltab P, LineItems_reltab L, Stock_reltab S WHERE P.PONo = L.PONo AND L.StockNo = S.StockNo GROUP BY P.PONo ;
SELECT P.PONo, P.CustNo, L.StockNo, L.LineItemNo, L.Quantity, L.Discount FROM PurchaseOrder_reltab P, LineItems_reltab L WHERE P.PONo = L.PONo AND L.StockNo = 1004 ;
The application can execute statements like these to update the data:
UPDATE LineItems_reltab SET Quantity = 20 WHERE PONo = 1001 AND StockNo = 1534 ;
The application can execute statements like these to delete data:
DELETE FROM LineItems_reltab WHERE PONo = 1001 ; DELETE FROM PurchaseOrder_reltab WHERE PONo = 1001 ;
The Relational Database Management System (RDBMS) is a very powerful and efficient form of information management. Why then should you even consider another approach? If you examine the application as developed under the relational model in comparison to the real world of the application domain, then certain shortcomings become evident.
Database tables are excellent for modeling a structure of relationships, but they fail to capture the way that objects in the real world are naturally bundled with operations on the data. For example, when you operate on a purchase order in the real world, you expect to be able to sum the line items to find the total cost to the customer. Similarly, you expect that you should be able to retrieve information about the customer who placed the order -- such as name, reference number, address, and so on. More complexly, you may want to determine the customer's buying history and payment pattern.
An RDBMS provides very sophisticated structures for storing and retrieving data, but each application developer must craft the operations needed for each application. This means that you must recode operations often, even though they may be very similar to operations already coded for applications within the same enterprise.
Relational tables do not capture compositions. For example, an address may be a composite of number, street, city, state, and zip code, but in a relational table, the notion of an address as a structure composed of the individual columns is not captured.
Relational tables have difficulty dealing with complex part-whole relationships. A piston and an engine have the same status as columns in the Stock_reltab, but there is no easy way to describe the fact that pistons are part of engines, except by creating multiple tables with primary key-foreign key relationships. Similarly, there is no easy way to implement the complex interrelationships between collections.
There is no easy way to capture the relationship of generalization-specification (inheritance). If we abstract the base requirements of a purchase order and write code to capture the relationships, then there is no way to develop purchase orders that use this code and then further specialize it for different domains. Instead, we will have duplicated the code in every implementation of a purchase order.
So why not create applications using a third-generation language (3GL)?
First, an RDBMS provides functionality that would take millions of person-hours to replicate.
Second, one of the problems of information management using 3GLs is that they are not persistent; or, if they are persistent, then they sacrifice security to obtain the necessary performance by way of locating the application logic and the data logic in the same address space. Neither trade-off is acceptable to users of an RDBMS, for whom both persistence and security are basic requirements.
This leaves the application developer working under the relational model with the problem of simulating complex types by some form of mapping into SQL. Apart from the many person-hours required, this approach involves serious problems of implementation. You must:
Obviously, there is heavy traffic back and forth between the client address space and that of the server, with the accompanying decrement in performance. And, if client and server are on different machines, then the toll on performance from network roundtrips may be considerable.
Object-relational (O-R) technology solves these problems. This chapter and the following chapter present examples that implement this new functionality.
The object-relational (O-R) approach begins with the same entity relationships as in "Entities and Relationships". Viewing these from the object-oriented perspective, as in the class diagram above, allows us to translate more of the real-world structure into the database schema.
Rather than breaking up addresses or multiple phone numbers into unrelated columns in relational tables, the O-R approach defines types to represent them. Rather than breaking line items out into a separate table, the O-R approach allows them to stay with their respective purchase orders as nested tables.
The main entities -- customers, stock, and purchase orders -- become objects. Object references express the relationships between them. Collection types model their multi-valued attributes.
There are two approaches to an object-relational implementation:
The remainder of this chapter develops the O-R schema and shows how to implement it with object tables. Chapter 4, "Applying an Object Model to Relational Data" implements the same schema with object views.
The following statements set the stage by defining incomplete object types:
CREATE TYPE StockItem_objtyp; CREATE TYPE LineItem_objtyp; CREATE TYPE PurchaseOrder_objtyp;
The incomplete definitions notify Oracle that full definitions are coming later. Oracle can compile other types that refer to these incomplete types. Incomplete type declarations are like forward declarations in C and other programming languages.
The following statement defines an array type:
CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20);
The preceding statement defines the type PhoneList_vartyp. Any data unit of type PhoneList_vartyp is a varray of up to 10 telephone numbers, each represented by a data item of type VARCHAR2.
A list of phone numbers could occupy a varray or a nested table. In this case, the list is the set of contact phone numbers for a single customer. A varray is a better choice than a nested table for the following reasons:
In general, if ordering and bounds are not important design considerations, then designers can use the following rule of thumb for deciding between varrays and nested tables: If you need to query the collection, then use nested tables; if you intend to retrieve the collection as a whole, then use varrays.
|
See Also: Chapter 5, "Design Considerations for Oracle Objects" for more information about the design considerations for varrays and nested tables. |
The following statement defines the object type Address_objtyp to represent addresses:
CREATE TYPE Address_objtyp AS OBJECT ( Street VARCHAR2(200), City VARCHAR2(200), State CHAR(2), Zip VARCHAR2(20) ) /
All of the attributes of an address are character strings, representing the usual parts of a simplified mailing address.
The following statement defines the object type Customer_objtyp, which uses other user-defined types as building blocks.
CREATE TYPE Customer_objtyp AS OBJECT ( CustNo NUMBER, CustName VARCHAR2(200), Address_obj Address_objtyp, PhoneList_var PhoneList_vartyp, ORDER MEMBER FUNCTION compareCustOrders(x IN Customer_objtyp) RETURN INTEGER ) /
Instances of the type Customer_objtyp are objects that represent blocks of information about specific customers. The attributes of a Customer_objtyp object are a number, a character string, an Address_objtyp object, and a varray of type PhoneList_vartyp.
Every Customer_objtyp object also has an associated order method, one of the two types of comparison methods. Whenever Oracle needs to compare two Customer_objtyp objects, it invokes the compareCustOrders method to do so.
The two types of comparison methods are map methods and order methods. This application uses one of each for purposes of illustration.
An ORDER method must be called for every two objects being compared, whereas a MAP method is called once per object. In general, when sorting a set of objects, the number of times an ORDER method is called is more than the number of times a MAP method would be called.
|
See Also:
|
The following statement completes the definition of the incomplete object type LineItem_objtyp declared at the beginning of this section.
CREATE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER ) /
Instances of type LineItem_objtyp are objects that represent line items. They have three numeric attributes and one REF attribute. The LineItem_objtyp models the line item entity and includes an object reference to the corresponding stock object.
The following statement defines the nested table type LineItemList_ntabtyp, which will represent an arbitrary set of line items inside a purchase order:
CREATE TYPE LineItemList_ntabtyp AS TABLE OF LineItem_objtyp /
A data unit of this type is a nested table, each row of which contains an object of type LineItem_objtyp. A nested table of line items is a better choice to represent the multivalued line item list than a varray of LineItem_objtyp objects, because:
The following statement completes the definition of the incomplete object type PurchaseOrder_objtyp declared at the beginning of this section:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT ( PONo NUMBER, Cust_ref REF Customer_objtyp, OrderDate DATE, ShipDate DATE, LineItemList_ntab LineItemList_ntabtyp, ShipToAddr_obj Address_objtyp, MAP MEMBER FUNCTION getPONo RETURN NUMBER, MEMBER FUNCTION sumLineItems RETURN NUMBER ) /
The preceding statement defines the object type PurchaseOrder_objtyp. Instances of this type are objects representing purchase orders. They have six attributes, including a REF to Customer_objtyp, an Address_objtyp object, and a nested table of type LineItemList_ntabtyp, which is based on type LineItem_objtyp.
Objects of type PurchaseOrder_objtyp have two methods: getPONo and sumLineItems. One, getPONo, is a MAP method, one of the two kinds of comparison methods. A MAP method returns the relative position of a given record within the order of records within the object. So, whenever Oracle needs to compare two PurchaseOrder_objtyp objects, it implicitly calls the getPONo method to do so.
The two pragma declarations provide information to PL/SQL about what sort of access the two methods need to the database.
The statement does not include the actual PL/SQL programs implementing the methods getPONo and sumLineItems. That appears in "Method Definitions".
The following statement completes the definition of StockItem_objtyp, the last of the three incomplete object types declared at the beginning of this section.
CREATE TYPE StockItem_objtyp AS OBJECT ( StockNo NUMBER, Price NUMBER, TaxRate NUMBER ) /
Instances of type StockItem_objtyp are objects representing the stock items that customers order. They have three numeric attributes.
This section shows how to specify the methods of the PurchaseOrder_objtyp and Customer_objtyp object types. The following statement defines the body of the PurchaseOrder_objtyp object type (the PL/SQL programs that implement its methods):
CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS MAP MEMBER FUNCTION getPONo RETURN NUMBER is BEGIN RETURN PONo; END; MEMBER FUNCTION sumLineItems RETURN NUMBER is i INTEGER; StockVal StockItem_objtyp; Total NUMBER := 0; BEGIN FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP UTL_REF.SELECT_OBJECT(LineItemList_ntab(i).Stock_ref,StockVal); Total := Total + SELF.LineItemList_ntab(i).Quantity * StockVal.Price; END LOOP; RETURN Total; END; END; /
The getPONo method is simple; use it to return the purchase order number of its associated PurchaseOrder_objtyp object. Such "get" methods allow you to avoid reworking code that uses the object if its internal representation changes.
The sumLineItems method uses a number of object-relational features:
sumLineItems method is to return the sum of the values of the line items of its associated PurchaseOrder_objtyp object. The keyword SELF, which is implicitly created as a parameter to every function, lets you refer to that object.
COUNT gives the count of the number of elements in a PL/SQL table or array. Here, in combination with LOOP, the application iterates through all the elements in the collection -- in this case, the items of the purchase order. In this way SELF.LineItemList_ntab.COUNT counts the number of elements in the nested table that match the LineItemList_ntab attribute of the PurchaseOrder_objtyp object, here represented by SELF.
UTL_REF is used in the implementation. The UTL_REF methods are necessary because Oracle does not support implicit dereferencing of REFs within PL/SQL programs. The UTL_REF package provides methods that operate on object references. Here, the SELECT_OBJECT method is called to obtain the StockItem_objtyp object corresponding to the Stock_ref.
AUTHID CURRENT_USER syntax specifies that the PurchaseOrder_objtyp is defined using invoker-rights: the methods are executed under the rights of the current user, not under the rights of the user who defined the type.
StockVal is of type StockItem_objtyp. The UTL_REF.SELECT_OBJECT sets it to the object whose reference is the following:
(LineItemList_ntab(i).Stock_ref)
This object is the actual stock item referred to in the currently selected line item.
StockVal.Price, the Price attribute of the StockItem_objtyp object. But to compute the cost of the item, you also need to know the quantity of items ordered. In the application, the term LineItemList_ntab(i).Quantity represents the Quantity attribute of the currently selected LineItem_objtyp object.
The remainder of the method program is a loop that sums the extended values of the line items, and the method returns the total as its value.
The following statement defines the compareCustOrders method of the Customer_objtyp object type.
CREATE OR REPLACE TYPE BODY Customer_objtyp AS ORDER MEMBER FUNCTION compareCustOrders (x IN Customer_objtyp) RETURN INTEGER IS BEGIN RETURN CustNo - x.CustNo; END; END; /
As mentioned earlier, the order method compareCustOrders operation compares information about two customer orders. It takes another Customer_objtyp object as an input argument and returns the difference of the two CustNo numbers. The return value is:
CustNo
CustNo
CustNo--in which case it is referring to itself.
Whether the return value is positive, negative, or zero signifies the relative order of the customer numbers. For example, perhaps lower numbers are created earlier in time than higher numbers. If either of the input arguments (SELF and explicit) to an ORDER method is NULL, Oracle does not call the ORDER method and simply treats the result as NULL.
This completes the definition of the user-defined types used in the purchase order application. None of the declarations creates tables or reserves data storage space.
To this point, the example is the same whether you plan to create and populate object tables or implement the application with object views on top of the relational tables that appear in "Implementing the Application Under The Relational Model". The remainder of this chapter continues the example using object tables. Chapter 4, "Applying an Object Model to Relational Data", picks up from this point and continues the example with object views.
Generally, you can think of the relationship between the "objects" and "object tables" in the following way:
Viewed in this way, each object table is an implicit type whose objects (specific rows) each have the same attributes (column values). The creation of explicit user-defined datatypes and object tables introduces a new level of functionality.
The following statement defines an object table Customer_objtab to hold objects of type Customer_objtyp:
CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY) OBJECT ID PRIMARY KEY ;
As you can see, the term "OF" makes the create statement different for object tables as opposed to relational tables. We earlier defined the attributes of Customer_objtyp objects as:
CustNo NUMBER CustName VARCHAR2(200) Address_obj Address_objtyp PhoneList_var PhoneList_vartyp
This means that the object table Customer_objtab has columns of CustNo, CustName, Address_obj, and PhoneList_var, and that each row is an object of type Customer_objtyp. As you will see, this notion of row object offers a significant advance in functionality.
Because there is a type Customer_objtyp, you could create numerous object tables of the same type. For example, you could create an object table Customer_objtab2 also of type Customer_objtyp. Without this ability, you would need to define each table individually.
You can introduce variations when creating multiple tables. The statement that created Customer_objtab defined a primary key constraint on the CustNo column. This constraint applies only to this object table. Another object table of the same type might not have this constraint.
Customer_objtab contains customer objects, represented as row objects. Oracle allows row objects to be referenceable, meaning that other row objects or relational rows may reference a row object using its object identifier (OID). For example, a purchase order row object may reference a customer row object using its object reference. The object reference is an opaque system-generated value represented by the type REF and is composed of the row object's unique OID.
Oracle requires every row object to have a unique OID. You may specify the unique OID value to be system-generated or specify the row object's primary key to serve as its unique OID. You indicate this when you execute the CREATE TABLE statement by specifying OBJECT ID PRIMARY KEY or OBJECT ID SYSTEM GENERATED, the latter serving as the default. The choice of primary key as the object identifier may be more efficient in cases where the primary key value is smaller than the default 16 byte system-generated identifier. For our example, the choice of primary key as the row object identifier has been made.
Examining the definition of Customer_objtab, you can see that the Address_obj column contains Address_objtyp objects. In other words, an object type may have attributes that are themselves object types. These embedded objects represent composite or structured values, and are also referred to as column objects. They differ from row objects because they are not referenceable and can be NULL.
Address_objtyp objects have attributes of built-in types, which means that they are leaf-level scalar attributes of Customer_objtyp. Oracle creates columns for Address_objtyp objects and their attributes in the object table Customer_objtab. You can refer to these columns using the dot notation. For example, if you want to build an index on the Zip column, then you can refer to it as Address.Zip.
The PhoneList column contains varrays of type PhoneList_vartyp. We defined each object of type PhoneList_vartyp as a varray of up to 10 telephone numbers, each represented by a data item of type VARCHAR2:
CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20) /
Because each varray of type PhoneList_vartyp can contain no more than 200 characters (10 x 20), plus a small amount of overhead, Oracle stores the varray as a single data unit in the PhoneList_var column. Oracle stores varrays that exceed 4000 bytes in "inline" BLOBs, which means that a portion of the varray value could potentially be stored outside the table.
The next statement creates an object table for StockItem_objtyp objects:
CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY) OBJECT ID PRIMARY KEY ;
Each row of the table is a StockItem_objtyp object having three numeric attributes:
StockNo NUMBER Price NUMBER TaxRate NUMBER
Oracle assigns a column for each attribute, and the CREATE TABLE statement places a primary key constraint on the StockNo column, and specifies that the primary key be used as the row object's identifier.
The next statement defines an object table for PurchaseOrder_objtyp objects:
CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp ( /* Line 1 */ PRIMARY KEY (PONo), /* Line 2 */ FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab) /* Line 3 */ OBJECT ID PRIMARY KEY /* Line 4 */ NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab ( /* Line 5 */ (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo)) /* Line 6 */ ORGANIZATION INDEX COMPRESS) /* Line 7 */ RETURN AS LOCATOR /* Line 8 */ /
The preceding CREATE TABLE statement creates the PurchaseOrder_objtab object table. The significance of each line is as follows:
CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (
This line indicates that each row of the table is a PurchaseOrder_objtyp object. Attributes of PurchaseOrder_objtyp objects are:
PONo NUMBER Cust_ref REF Customer_objtyp OrderDate DATE ShipDate DATE LineItemList_ntab LineItemList_ntabtyp ShipToAddr_obj Address_objtyp
PRIMARY KEY (PONo),
This line specifies that the PONo attribute is the primary key for the table.
FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
This line specifies a referential constraint on the Cust_ref column. This referential constraint is similar to those specified for relational tables. When there is no constraint, the REF column allows you to reference any row object. However, in this case, the Cust_ref REFs can refer only to row objects in the Customer_objtab object table.
OBJECT ID PRIMARY KEY
This line indicates that the primary key of the PurchaseOrder_objtab object table be used as the row's OID.
NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab ( (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo)) ORGANIZATION INDEX COMPRESS) RETURN AS LOCATOR
These lines pertain to the storage specification and properties of the nested table column, LineItemList_ntab. The rows of a nested table are stored in a separate storage table. This storage table is not directly queryable by the user but can be referenced in DDL statements for maintenance purposes. A hidden column in the storage table, called the NESTED_TABLE_ID, matches the rows with their corresponding parent row. All the elements in the nested table belonging to a particular parent have the same NESTED_TABLE_ID value. For example, all the elements of the nested table of a given row of PurchaseOrder_objtab have the same value of NESTED_TABLE_ID. The nested table elements that belong to a different row of PurchaseOrder_objtab have a different value of NESTED_TABLE_ID.
In the CREATE TABLE example above, Line 5 indicates that the rows of LineItemList_ntab nested table are to be stored in a separate table (referred to as the storage table) named PoLine_ntab. The STORE AS clause also allows you to specify the constraint and storage specification for the storage table. In this example, Line 7 indicates that the storage table is an index-organized table (IOT). In general, storing nested table rows in an IOT is beneficial, because it provides clustering of rows belonging to the same parent. The specification of COMPRESS on the IOT saves storage space because, if you do not specify COMPRESS, the NESTED_TABLE_ID part of the IOT's key is repeated for every row of a parent row object. If, however, you specify COMPRESS, the NESTED_TABLE_ID is stored only once for each row of a parent row object.
The SCOPE FOR constraint on a REF is not allowed in a CREATE TABLE statement. Therefore, to specify that Stock_ref can reference only the object table Stock_objtab, issue the following ALTER TABLE statement on the PoLine_ntab storage table:
ALTER TABLE PoLine_ntab ADD (SCOPE FOR (Stock_ref) IS stock_objtab) ;
Note that this statement is executed on the storage table, not the parent table.
|
See Also: "Nested Table Storage" for information about the benefits of organizing a nested table as and IOT and specifying nested table compression, and for more information about nested table storage. |
In Line 6, the specification of NESTED_TABLE_ID and LineItemNo attribute as the primary key for the storage table serves two purposes: first, it serves as the key for the IOT; second, it enforces uniqueness of a column (LineItemNo) of a nested table within each row of the parent table. By including the LineItemNo column in the key, the statement ensures that the LineItemNo column contains distinct values within each purchase order.
Line 8 indicates that the nested table, LineItemList_ntab, is returned in the locator form when retrieved. If you do not specify LOCATOR, the default is VALUE, which indicates that the entire nested table is returned instead of just a locator to the nested table. When the nested table collection contains many elements, it may not be very efficient to return the entire nested table whenever the containing row object or the column is selected.
Specifying that the nested table's locator is returned enables Oracle to send to the client only a locator to the actual collection value. An application can find whether a fetched nested table is in the locator or value form by calling the OCICollIsLocator or UTL_COLL.IS_LOCATOR interfaces. Once you know that the locator has been returned, the application can query using the locator to fetch only the desired subset of row elements in the nested table. This locator-based retrieval of the nested table rows is based on the original statement's snapshot, to preserve the value or copy semantics of the nested table. That is, when the locator is used to fetch a subset of row elements in the nested table, the nested table snapshot reflects the nested table when the locator was first retrieved.
Recall the implementation of the sumLineItems method of PurchaseOrder_objtyp in "Method Definitions". That implementation assumed that the LineItemList_ntab nested table would be returned as a VALUE. In order to handle large nested tables more efficiently, and to take advantage of the fact that the nested table in the PurchaseOrder_objtab is returned as a locator, the sumLineItems method must be rewritten as follows:
CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS MAP MEMBER FUNCTION getPONo RETURN NUMBER is BEGIN RETURN PONo; END; MEMBER FUNCTION sumLineItems RETURN NUMBER IS i INTEGER; StockVal StockItem_objtyp; Total NUMBER := 0; BEGIN IF (UTL_COLL.IS_LOCATOR(LineItemList_ntab)) -- check for locator THEN SELECT SUM(L.Quantity * L.Stock_ref.Price) INTO Total FROM TABLE(CAST(LineItemList_ntab AS LineItemList_ntabtyp)) L; ELSE FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP UTL_REF.SELECT_OBJECT(LineItemList_ntab(i).Stock_ref,StockVal); Total := Total + SELF.LineItemList_ntab(i).Quantity * StockVal.Price; END LOOP; END IF; RETURN Total; END; END; /
The rewritten sumLineItems method checks whether the nested table attribute, LineItemList_ntab, is returned as a locator using the UTL_COLL.IS_LOCATOR function. When the condition evaluates to TRUE, the nested table locator is queried using the TABLE expression.
The querying of the nested table locator results in a more efficient processing of the large line item list of a purchase order. The previous code that iterates over the LineItemList_ntab is kept to deal with the case where the nested table is returned as a VALUE.
After the table is created, the following ALTER TABLE statement is issued:
ALTER TABLE PoLine_ntab ADD (SCOPE FOR (Stock_ref) IS stock_objtab);
This statement specifies that the Stock_ref column of the nested table is scoped to Stock_objtab. This indicates that the values stored in this column must be references to row objects in Stock_objtab. The SCOPE constraint is different from the referential constraint, because the SCOPE constraint has no implication on the referenced object. For example, any referenced row object in Stock_objtab may be deleted, even if it is referenced in the Stock_ref column of the nested table. Such a deletion renders the corresponding reference in the nested table a DANGLING REF.
Oracle does not support referential constraint specification for storage tables. In this situation, specifying the SCOPE clause for a REF column is useful. In general, specifying scope or referential constraints for REF columns has a few benefits:
REF value in the column.
REF column.
REFs as joins involving the referenced table.
At this point, all of the tables for the purchase order application are in place. The next section shows how to operate on these tables.
Here is how to insert the same data into the object tables as we did earlier for relational tables. Notice how some of the values are actually calls to the constructors for object types.
INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) ; INSERT INTO Stock_objtab VALUES(1011, 4500.23, 2) ; INSERT INTO Stock_objtab VALUES(1534, 2234.00, 2) ; INSERT INTO Stock_objtab VALUES(1535, 3456.23, 2) ;
INSERT INTO Customer_objtab VALUES ( 1, 'Jean Nance', Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'), PhoneList_vartyp('415-555-1212') ) ; INSERT INTO Customer_objtab VALUES ( 2, 'John Nike', Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'), PhoneList_vartyp('609-555-1212','201-555-1212') ) ;
INSERT INTO PurchaseOrder_objtab SELECT 1001, REF(C), SYSDATE, '10-MAY-1999', LineItemList_ntabtyp(), NULL FROM Customer_objtab C WHERE C.CustNo = 1 ;
The preceding statement constructs a PurchaseOrder_objtyp object with the following attributes:
PONo 1001 Cust_ref REF to customer number 1 OrderDate SYSDATE ShipDate 10-MAY-1999 LineItemList_ntab an empty LineItem_ntabtyp ShipToAddr_obj NULL
The statement uses a query to construct a REF to the row object in the Customer_objtab object table that has a CustNo value of 1.
The following statement uses a TABLE expression to identify the nested table as the target for the insertion, namely the nested table in the LineItemList_ntab column of the row object in the PurchaseOrder_objtab table that has a PONo value of 1001.
INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 1001 ) SELECT 01, REF(S), 12, 0 FROM Stock_objtab S WHERE S.StockNo = 1534 ;
The preceding statement inserts a line item into the nested table identified by the TABLE expression. The inserted line item contains a REF to the row object with a StockNo value of 1534 in the object table Stock_objtab.
The following statements follow the same pattern as the previous ones:
INSERT INTO PurchaseOrder_objtab SELECT 2001, REF(C), SYSDATE, '20-MAY-1997', LineItemList_ntabtyp(), Address_objtyp('55 Madison Ave','Madison','WI','53715') FROM Customer_objtab C WHERE C.CustNo = 2 ; INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 1001 ) SELECT 02, REF(S), 10, 10 FROM Stock_objtab S WHERE S.StockNo = 1535 ; INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 2001 ) SELECT 10, REF(S), 1, 0 FROM Stock_objtab S WHERE S.StockNo = 1004 ; INSERT INTO TABLE ( SELECT P.LineItemList_ntab FROM PurchaseOrder_objtab P WHERE P.PONo = 2001 ) VALUES(11, (SELECT REF(S) FROM Stock_objtab S WHERE S.StockNo = 1011), 2, 1) ;
The following query statement implicitly invokes a comparison method. It shows how Oracle orders objects of type PurchaseOrder_objtyp using that type's comparison method:
SELECT p.PONo FROM PurchaseOrder_objtab p ORDER BY VALUE(p) ;
Oracle invokes the map method getPONo for each PurchaseOrder_objtyp object in the selection. Because that method returns the object's PONo attribute, the selection produces a list of purchase order numbers in ascending numerical order.
The following queries correspond to the queries executed under the relational model.
SELECT DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo, p.OrderDate, LineItemList_ntab FROM PurchaseOrder_objtab p WHERE p.PONo = 1001 ;
SELECT p.PONo, p.sumLineItems() FROM PurchaseOrder_objtab p ;
SELECT po.PONo, po.Cust_ref.CustNo, CURSOR ( SELECT * FROM TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004 ) FROM PurchaseOrder_objtab po ;
The above query returns a nested cursor for the set of LineItem_obj objects selected from the nested table. The application can fetch from the nested cursor to get the individual LineItem_obj objects. The above query can also be expressed by unnesting the nested set with respect to the outer result:
SELECT po.PONo, po.Cust_ref.CustNo, L.* FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L WHERE L.Stock_ref.StockNo = 1004 ;
The above query returns the result set as a "flattened" form (or First Normal Form). This type of query is useful when accessing Oracle collection columns from relational tools and APIs, such as ODBC. In the above unnesting example, only the rows of the PurchaseOrder_objtab object table that has any LineItemList_ntab rows are returned. To fetch all rows of the PurchaseOrder_objtab table, regardless of the presence of any rows in their corresponding LineItemList_ntab, then the (+) operator is required:
SELECT po.PONo, po.Cust_ref.CustNo, L.* FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) (+) L WHERE L.Stock_ref.StockNo = 1004 ;
This request requires querying the rows of all nested tables, LineItemList_ntab, of all PurchaseOrder_objtab rows. Again, unnesting is required:
SELECT AVG(L.DISCOUNT) FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L ;
The following example has the same effect as the two deletions needed in the relational case (see "Deleting Data Under The Relational Model"). In this case, Oracle automatically deletes all line items belonging to the deleted purchase order. The relational case requires a separate step.
DELETE FROM PurchaseOrder_objtab WHERE PONo = 1001 ;
Using the schema that we have already defined for the purchase order example, we can manipulate objects within the database through the Java Database Connectivity (JDBC) API or by using embedded SQL with SQLJ. Although we use JDBC in this example, the coding for both is similar, and you can use either technique for object-oriented programs.
The first decision you do have to make is how closely you want to map the object types in the database to Java classes. The following sections show the two choices.
In this example:
This technique lets us essentially write a procedural Java program that can easily interact with a particular class, as long as the definition of that class stays the same.
import java.sql.*; import oracle.sql.*; public class DefaultMappingDemo { public static void main(String[] args) { System.out.println("*** JAVA OBJECTS DEMO ***"); try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@stpc90.us.oracle.com:1521:stpc90", "scott", "tiger") ; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery ("select value(c) from CUSTOMER_TAB c order by value(c)"); while (rs.next ()) { // retrieve the STRUCT oracle.sql.STRUCT cust_struct = (STRUCT)rs.getObject(1); oracle.sql.Datum cust_attrs[] = cust_struct.getOracleAttributes(); oracle.sql.NUMBER num = (NUMBER)cust_attrs[0]; // string attribute in Object oracle.sql.CHAR name = (CHAR) cust_attrs[1]; // embedded object oracle.sql.STRUCT address_struct = (STRUCT)cust_attrs[2]; oracle.sql.Datum address_attrs[] = address_struct.getOracleAttributes(); oracle.sql.CHAR street = (CHAR) address_attrs[0]; oracle.sql.CHAR city = (CHAR) address_attrs[1]; oracle.sql.CHAR state = (CHAR) address_attrs[2]; oracle.sql.CHAR zip = (CHAR) address_attrs[3]; System.out.println("Number: " + num.stringValue() + ", Name: " + name + ", Address: " + street + ", " + city + ", " + state + ", " + zip); //embedded array oracle.sql.ARRAY phone_list = (ARRAY)cust_attrs[3]; } rs.close(); stmt.close(); } catch (SQLException exn) { System.out.println("SQLException: "+exn); } } }
If you want to model the database object types using multiple Java classes, you can construct a strongly typed model. The classes all implement some common behavior to do the underlying database operations. Now, you have another choice: do you want to model the classes on the JDBC 2.0 API (the SQLData interface) or on Oracle's API (the CustomDatum interface)?
The SQLData interface is standards-based and potentially offers portability between different database systems. The CustomDatum interface is derived from JDBC, but offers additional enhancements; it can encapsulate REFs, collection types, and other object-oriented features not supported by JDBC.
You can generate wrapper classes for either interface by using JPublisher with different options.
In the strongly typed model, we need a Java class for each object type in the schema. The easiest way to get these classes is to let Oracle read the type definitions from the database and generate the Java code for us. To do this, we can use the following file as input to the JPublisher tool:
SQL SCOTT."ADDRESS_OBJTYP" AS JAddress SQL SCOTT."CUSTOMER_OBJTYP" AS JCustomerInfo SQL SCOTT."LINEITEMLIST_NTABTYP" AS JLineItemList SQL SCOTT."LINEITEM_OBJTYP" AS JLineItem SQL SCOTT."PHONELIST_VARTYP" AS JPhoneList SQL SCOTT."PURCHASEORDER_OBJTYP" AS JPurchaseOrder SQL SCOTT."STOCKITEM_OBJTYP" AS JStockInfo
The wrapper classes all look much like the one below, JCustomer which corresponds to the CUSTOMER_INFO_T type in the database schema. For our example, we would also need the JAddress wrapper class because one of the attributes of JCustomer is a JAddress object.
You can read or write instances of this type using regular Java I/O streams. To implement additional member functions, you can subclass JCustomer, so that your code is preserved whenever that class is regenerated.
import java.sql.*; import oracle.jdbc2.*; import oracle.sql.*; public class JCustomer implements SQLData { private String sql_type; public int custNo; public String custName; public JAddress address; public Array phoneList; public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL (SQLInput stream, String typeName) throws SQLException { sql_type = typeName; custNo = stream.readInt(); custName = stream.readString(); address = (JAddress) stream.readObject(); phoneList= stream.readArray(); } public void writeSQL (SQLOutput stream) throws SQLException { stream.writeInt(custNo); stream.writeString(custName); stream.writeObject(address); stream.writeArray(phoneList); } }
In this example, we do not show member functions being derived from the method functions of the database type. Calling such member functions causes traffic as object data is passed back and forth to the database server, and you must follow certain conventions for input and output parameters. For information on this subject, see Oracle8i SQLJ Developer's Guide and Reference (Objects and Collections) and Oracle8i JDBC Developer's Guide and Reference (Working with Oracle Object Types).
In the following program:
import java.sql.*; import oracle.sql.*; import oracle.jdbc.driver.*; import oracle.jdbc2.*; import java.util.*; public class SQLDataDemo { public static void main(String[] args) throws Exception, SQLException { System.out.println("*** JAVA OBJECTS DEMO : USING SQLData INTERFACE ***"); DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); OracleConnection conn = (OracleConnection) DriverManager.getConnection ("jdbc:oracle:thin:@stpc90.us.oracle.com:1521:stpc90", "scott", "tiger"); Statement stmt = conn.createStatement(); //put an entry in the typemap try { Dictionary map = conn.getTypeMap(); map.put("CUSTOMER_INFO_T", Class.forName("JCustomer")); map.put("ADDRESS_T", Class.forName("JAddress")); } catch (ClassNotFoundException exn) { } ResultSet rs = stmt.executeQuery("select VALUE (p) from CUSTOMER_TAB p"); while (rs.next()) { //retrieve the object using standard API JCustomer jc = (JCustomer) rs.getObject(1); int custNo = jc.custNo; String custName = jc.custName; jc.custName = "Geoff Lee"; PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO CUSTOMER_TAB VALUES (?)"); pstmt.setObject(1, jc); pstmt.executeUpdate(); rs.close(); stmt.close(); } } }
On Windows systems, you can use Oracle Objects for OLE (OO4O) to write object-oriented database programs in Visual Basic or other environments that support the COM protocol, such as Excel.
The following examples all begin with a similar header section that connects to the database, then each shows how to perform a different operation on object data.
Here is an event handler for a button that performs a SELECT operation.
Private Sub obj_select_Click() Dim OO4OSession As OraSession Dim InvDB As OraDatabase Dim PurchaseOrder As OraDynaset Dim CustomerInfo As OraRef Dim LineItemsList As OraCollection Dim LineItem As OraObject Dim ShipToAddr As OraObject Dim StockInfo As OraRef Dim CustomerAddr As OraObject 'Create the OraSession Object. Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set InvDB = OO4OSession.OpenDatabase("exampledb", "scott/tiger", 0&) 'Select from purchase_tab Set PurchaseOrder = InvDB.CreateDynaset("select * from purchase_tab", 0&) 'Get the custref attribute from PurchaseOrder Set CustomerInfo = PurchaseOrder.Fields("custref").Value ' Accessing attributes CustomerInfo object 'Display custno,custname,phonelist attibutes of CustomerInfo MsgBox CustomerInfo.custno MsgBox CustomerInfo.custname 'Get address and phonelist attibutes of CustomerInfo Set CustomerAddr = CustomerInfo.Address 'Display all the atributes of CustomerAddr MsgBox CustomerAddr.Street MsgBox CustomerAddr.State MsgBox CustomerAddr.Zip ' Accessing elements of LineItemsList Object 'Get line_item_list attribute from PurchaseOrder Set LineItemsList = PurchaseOrder.Fields("line_item_list").Value 'Get LineItem object element from LineItemList collection Set LineItem = LineItemsList(1) 'Display lineitemno,quantity,discount attibutes MsgBox LineItem.lineitemno MsgBox LineItem.quantity MsgBox LineItem.discount 'Access stockref attribute of LineItem Set StockInfo = LineItem.Stockref 'Display stockno,cost,tax_code of StockInfo MsgBox StockInfo.stockno MsgBox StockInfo.cost MsgBox StockInfo.tax_code End Sub
Here is a program that retrieves a set of rows from the database, then adds a new row.
Dim OO4OSession As OraSession Dim InvDB As OraDatabase Dim PurchaseOrder As OraDynaset Dim CustomerInfo As OraRef Dim LineItemsList As OraCollection Dim LineItem As OraObject Dim ShipToAddr As OraObject Dim StockInfo As OraRef Dim CustomerAddr As OraObject 'Create the OraSession Object. Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set InvDB = OO4OSession.OpenDatabase("exampledb", "scott/tiger", 0&) 'Select from purchase_tab Set PurchaseOrder = InvDB.CreateDynaset("select * from purchase_tab", 0&) ' Step 1 - Creating CustomerInfo ref object 'select a ref from customer_tab for custono 2 Set CustomerDyn = InvDB.CreateDynaset("select REF(C) from customer_tab c where c.custno = 2", 0&) 'get the CustomerInfo ref object Set CustomerInfo = CustomerDyn.Fields(0).Value ' Step 2 - Creating LineItemsList object ' Create a new line_items_list object Set LineItemsList = InvDB.CreateOraObject("line_item_list_t") ' Create a new line_items object Set LineItem = InvDB.CreateOraObject("line_item_t") 'set attributes of LineItem object LineItem.lineitemno = 2 LineItem.quantity = 15 LineItem.discount = 30 LineItem.Stockref = Null 'set the LineItem to first element of LineItemList LineItemsList(1) = LineItem ' Step 3 - Creating ShipToAddr object ' create a shiptoaddr object Set ShipToAddr = InvDB.CreateOraObject("address_t") 'set the attributes of ShipToAddr Object ShipToAddr.city = "Belmont" ShipToAddr.Street = "Continentals way" ShipToAddr.Zip = "94002" ShipToAddr.State = "CA" ' Start the AddNew operation on PurchaseOrder dynaset PurchaseOrder.AddNew PurchaseOrder.Fields("pono").Value = 1002 PurchaseOrder.Fields("orderdate").Value = "5/15/99" PurchaseOrder.Fields("shipdate").Value = "6/15/99" 'set the custref field to CustomerInfo object created in step1 PurchaseOrder.Fields("custref").Value = CustomerInfo 'set the line_item_list field to LineItemslist object created in step2 PurchaseOrder.Fields("line_item_list").Value = LineItemsList 'set the shiptoaddr field to ShipToAddr object created in step3 PurchaseOrder.Fields("shiptoaddr").Value = ShipToAddr ' Call the update method on Purchaseorder Dynaset which inserts a new row ' in purchase_tab table PurchaseOrder.Update
Here is a program that retrieves some rows from the database, then updates a specific one.
Dim OO4OSession As OraSession Dim InvDB As OraDatabase Dim PurchaseOrder As OraDynaset Dim CustomerInfo As OraRef Dim LineItemsList As OraCollection Dim LineItem As OraObject Dim ShipToAddr As OraObject Dim StockInfo As OraRef Dim CustomerAddr As OraObject 'Create the OraSession Object. Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set InvDB = OO4OSession.OpenDatabase("exampledb", "scott/tiger", 0&) 'Select from purchase_tab for pono 1002 Set PurchaseOrder = InvDB.CreateDynaset("select * from purchase_tab where pono = 1002", 0&) 'Create a StockInfo from stock_tab for stockno 1535 Set StockDyn = InvDB.CreateDynaset("select REF(s) from stock_tab s where s.stockno = 1535", 0&) Set StockInfo = StockDyn.Fields(0).Value 'Get line_item_list attribute from PurchaseOrder Set LineItemsList = PurchaseOrder.Fields("line_item_list").Value 'Get LineItem object element from LineItemList collection Set LineItem = LineItemsList(1) 'Start the edit operation on PurchaseOrder dynaset PurchaseOrder.Edit ' Set the StockInfo object created in Step1 to stockref attribute ' of LineItem LineItem.Stockref = StockInfo PurchaseOrder.Update
Here is a program that retrieves a purchase order, and calls its member function TOTAL_VALUE to sum the cost of the line items that are part of the purchase order.
Dim OO4OSession As OraSession Dim InvDB As OraDatabase Dim PurchaseOrderObj As OraDynaset 'Create the OraSession Object. Set OO4OSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set InvDB = OO4OSession.OpenDatabase("exampledb", "scott/tiger", 0&) 'Select from purchase_tab Set PurchaseOrderDyn = InvDB.CreateDynaset("select VALUE(p) from purchase_tab p where p.pono = 1001", 0&) 'Get the PurchaseOrderObj Set PurchaseOrderObj = PurchaseOrderDyn.Fields(0).Value 'Create a OraParameter object for purchase_order_t object and set it to PurchaseOrder InvDB.Parameters.Add "PURCHASEORDER", PurchaseOrderObj, ORAPARM_BOTH, ORATYPE_OBJECT, "PURCHASE_ORDER_T" 'Create a parameter for total_value return InvDB.Parameters.Add "TOTALVALUE", "", ORAPARM_OUTPUT 'Execute a member method InvDB.ExecuteSQL ("BEGIN :TOTALVALUE := PURCHASE_ORDER_T.TOTAL_VALUE(:PURCHASEORDER); END;") 'Display the totalvalue MsgBox InvDB.Parameters("TOTALVALUE").Value
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|