Oracle PLSQL Language- P22

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

76
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle plsql language- p22', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PLSQL Language- P22

FUNCTION LAST RETURN BINARY_INTEGER; Example IF my_list.EXISTS(my_list.FIRST) THEN my_list(my_list.FIRST) := 42; ELSE my_list.EXTEND; my_list(my_list.FIRST) := 42; END IF; Returns FIRST returns the lowest index in use in the collection; LAST returns the highest. Applies to Nested tables, index-by tables, VARRAYs. Boundary considerations FIRST and LAST return NULL when applied to initialized collections which have no elements. For VARRAYs which have at least one element, FIRST is always 1, and LAST is always equal to COUNT. Exceptions possible If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. 19.6.6 LIMIT Specification FUNCTION LIMIT RETURN BINARY_INTEGER; Example IF my_list.LAST < my_list.LIMIT THEN my_list.EXTEND; END IF; Returns Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The maximum number of elements that is possible for a given VARRAY. Applies to VARRAYs only. Returns NULL if applied to nested tables or index-by tables. Boundary considerations None Exceptions possible If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. 19.6.7 PRIOR(i), NEXT(i) Specification FUNCTION PRIOR (i BINARY_INTEGER) RETURN BINARY_INTEGER; FUNCTION NEXT (i BINARY_INTEGER) RETURN BINARY_INTEGER; Example This function returns the sum of elements in a List_t collection of numbers: CREATE FUNCTION compute_sum (the_list IN List_t) RETURN NUMBER AS elt BINARY_INTEGER := the_list.FIRST; total NUMBER := 0; BEGIN LOOP EXIT WHEN elt IS NULL; total := total + the_list(elt); elt := the_list.NEXT(elt); END LOOP; RETURN total; END; Returns PRIOR returns the next lower index in use relative to i; NEXT returns the next higher. Applies to Nested tables, index-by tables, VARRAYs. Boundary considerations Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
If applied to initialized collections which have no elements, returns NULL. If i is greater than or equal to COUNT, NEXT returns NULL; if i is less than or equal to FIRST, PRIOR returns NULL. (Currently, if the collection has elements, and i is greater than COUNT, PRIOR returns LAST; if i is less than FIRST, NEXT returns FIRST; however, do not rely on this behavior in future Oracle versions.) Exceptions possible If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. 19.6.8 TRIM [ (n ) ] Specification PROCEDURE TRIM (n BINARY_INTEGER:=1); Example CREATE FUNCTION pop (the_list IN OUT List_t) RETURN VARCHAR2 AS l_value VARCHAR2(30); BEGIN IF the_list.COUNT >= 1 THEN /* Save the value of the last element in the collection || so it can be returned */ l_value := the_list(the_list.LAST); the_list.TRIM; END IF; RETURN l_value; END; Action Removes n elements from the end of a collection. Without arguments, TRIM removes exactly one element. Confusing behavior occurs if you combine DELETE and TRIM actions on a collection; for example, if an element that you are trimming has previously been DELETEd, TRIM "repeats" the deletion but counts this as part of n, meaning that you may be TRIMming fewer actual elements than you think. Applies to Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Nested tables, VARRAYs. Attempting to TRIM an index-by table will produce a compile- time error. Boundary considerations If n is null, TRIM will do nothing. Exceptions possible Will raise SUBSCRIPT_BEYOND_COUNT if you attempt to TRIM more elements than actually exist. If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. Previous: 19.5 Collection Oracle PL/SQL Next: 19.7 Example: PL/ Pseudo-Functions Programming, 2nd Edition SQL-to-Server Integration 19.5 Collection Pseudo- Book Index 19.7 Example: PL/SQL-to- Functions Server Integration The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.6 Collection Chapter 19 Next: 19.8 Collections Built-Ins Nested Tables and Housekeeping VARRAYs 19.7 Example: PL/SQL-to-Server Integration To provide an(other) demonstration of how collections can ease the burden of transferring data between server and PL/SQL application program, let's look at a new example. The main entity in this example is the "apartment complex." We use a nested table of objects in order to hold the list of apartments for each apartment complex. Each apartment is described by the following attributes: CREATE TYPE Apartment_t AS OBJECT ( unit_no NUMBER, square_feet NUMBER, bedrooms NUMBER, bathrooms NUMBER, rent_in_dollars NUMBER ); And we can now define the nested table type which will hold a list of these apartment objects: CREATE TYPE Apartment_tab_t AS TABLE OF Apartment_t; Using this type as the type of a column, here is the definition of our database table: CREATE TABLE apartment_complexes (name VARCHAR2(75), landlord_name VARCHAR2(45), apartments Apartment_tab_t) NESTED TABLE apartments STORE AS apartments_store_tab; If you're curious, the INSERT statements to populate such a table look like the following (note the use of nested constructors to create the collection of objects): INSERT INTO apartment_complexes VALUES Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
('RIVER OAKS FOUR', 'MR. JOHNSON', Apartment_tab_t( Apartment_t(1, 780, 2, 1, 975), Apartment_t(2, 1200, 3, 2, 1590), Apartment_t(3, 690, 1, 1.5, 800), Apartment_t(4, 690, 1, 2, 450), Apartment_t(5, 870, 2, 2, 990) ) ); INSERT INTO apartment_complexes VALUES ('GALLERIA PLACE', 'MS. DODENHOFF', Apartment_tab_t( Apartment_t(101, 1000, 3, 2, 1295), Apartment_t(102, 800, 2, 1, 995), Apartment_t(103, 800, 2, 1, 995), Apartment_t(201, 920, 3, 1.5, 1195), Apartment_t(202, 920, 3, 1.5, 1195), Apartment_t(205, 1000, 3, 2, 1295) ) ); Now, at last, we can show off some wonderful features of storing collections in the database. Imagine that we are the new managers of the River Oaks Four apartments (hardly large enough to qualify as a complex) and we want to demolish any unit that rents for less than $500, and raise the rent on everything else by 15%. DECLARE /* Declare the cursor that will retrieve the collection of || apartment objects. Since we know we're going to update the || record, we can lock it using FOR UPDATE. */ CURSOR aptcur IS SELECT apartments FROM apartment_complexes WHERE name = 'RIVER OAKS FOUR' FOR UPDATE OF apartments; /* Need a local variable to hold the collection of fetched || apartment objects. */ l_apartments apartment_tab_t; which INTEGER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN /* A single fetch is all we need! */ OPEN aptcur; FETCH aptcur INTO l_apartments; CLOSE aptcur; /* Iterate over the apartment objects in the collection and || delete any elements of the nested table which meet the || criteria */ which := l_apartments.FIRST; LOOP EXIT WHEN which IS NULL; IF l_apartments(which).rent_in_dollars < 500 THEN l_apartments.DELETE(which); END IF; which := l_apartments.NEXT(which); END LOOP; /* Now iterate over the remaining apartments and raise the || rent. Notice that this code will skip any deleted || elements. */ which := l_apartments.FIRST; LOOP EXIT WHEN which IS NULL; l_apartments(which).rent_in_dollars := l_apartments(which).rent_in_dollars * 1.15; which := l_apartments.NEXT(which); END LOOP; /* Finally, ship the entire apartment collection back to the || server -- in a single statement! */ UPDATE apartment_complexes SET apartments = l_apartments WHERE name = 'RIVER OAKS FOUR'; END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
To me, one of the most significant aspects of this example is the single-statement fetch (and store). This PL/SQL fragment emulates the creating of a "client-side cache" of data, which is an essential concept in many object-oriented and client-server architectures. Using this kind of approach with collections can reduce network traffic and improve the quality of your code. Previous: 19.6 Collection Oracle PL/SQL Next: 19.8 Collections Built-Ins Programming, 2nd Edition Housekeeping 19.6 Collection Built-Ins Book Index 19.8 Collections Housekeeping The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.7 Example: Chapter 19 Next: 19.9 Which PL/SQL-to-Server Nested Tables and Collection Type Should I Integration VARRAYs Use? 19.8 Collections Housekeeping Here are some not-so-obvious bits of information that will assist you in using nested tables and VARRAYS. 19.8.1 Privileges When they live in the database, collection datatypes can be shared by more than one Oracle user (schema). As you can imagine, privileges are involved. Fortunately, it's not complicated; only one Oracle privilege -- EXECUTE -- applies to collection types. If you are SCOTT and you want to grant JOE permission to use Color_tab_t in his programs, all you need to do is grant the EXECUTE privilege to him: GRANT EXECUTE on Color_tab_t TO JOE; Joe can then refer to the type using schema.type notation. For example: CREATE TABLE my_stuff_to_paint ( which_stuff VARCHAR2(512), paint_mixture SCOTT.Color_tab_t ); EXECUTE privileges are also required by users who need to run PL/SQL anonymous blocks that uses the object type. That's one of several reasons that named PL/SQL modules -- packages, procedures, functions -- are generally preferred. Granting EXECUTE on the module confers the grantor's privileges to the grantee while executing the module. For tables that include collection columns, the traditional SELECT, INSERT, UDPATE, and DELETE privileges still have meaning, as long as there is no requirement to build a collection for any columns. However, if a user is going to INSERT or UPDATE the contents of a collection column, the user must have the EXECUTE privilege on the type, because that is the only way to use the default constructor. 19.8.2 Data Dictionary Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
There are a few new entries in the data dictionary (shown in Table 19.3) that will be very helpful in managing your collection types. The shorthand dictionary term for user-defined types is simply TYPE. Collection type definitions are found in the USER_SOURCE view (or DBA_SOURCE, or ALL_SOURCE). Table 19.3: Data Dictionary Entries for Collection Types To Answer Use This View As In the Question... What USER_TYPES SELECT type_name collection FROM user_types types have WHERE type_code = 'COLLECTION'; I created? What was USER_SOURCE SELECT text the FROM user_source original WHERE name = 'FOO_T' type AND type = 'TYPE' definition ORDER BY line; of collection Foo_t? What USER_TAB_COLUMNS SELECT table_name, column_name columns FROM user_tab_columns implement WHERE data_type = 'FOO_T'; Foo_t? What USER_DEPENDENCIES SELECT name, type database FROM user_dependencies objects are WHERE referenced_name = 'FOO_T'; dependent on Foo_t? 19.8.3 Call by Reference or Call by Value Under certain circumstances that are beyond the control of the programmer, PL/SQL will pass collection arguments by reference rather than by value. The rationale is that since collections can be large, it is more efficient to pass only a pointer (call by reference) than to make a copy of the collection (call by value). Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Usually, the compiler's choice of parameter passing approach is invisible to the application programmer. Not knowing whether the compiler will pass arguments by reference or by value can lead to unexpected results if all of the following conditions are met: 1. You have created a procedure or function "in line" in another module's declaration section (these are known as nested or local program units and are explained in Chapter 15, Procedures and Functions). 2. The inline module refers to a "global" collection variable declared outside its definition. 3. In the body of the outer module, this collection variable is passed as an actual parameter to the inline module. This is a rather uncommon combination of affairs in most PL/SQL programs. If you are in the habit of using "in line" module definitions, it's probably not a good idea to rely on the value of global variables anyway! Previous: 19.7 Example: Oracle PL/SQL Next: 19.9 Which PL/SQL-to-Server Programming, 2nd Edition Collection Type Should I Integration Use? 19.7 Example: PL/SQL-to- Book Index 19.9 Which Collection Type Server Integration Should I Use? The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.8 Collections Chapter 19 Next: 20. Object Views Housekeeping Nested Tables and VARRAYs 19.9 Which Collection Type Should I Use? It's not altogether obvious how to choose the best type of collection for a given application. Here are some guidelines: q If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth. q If you want to preserve the order of elements that get stored in the collection column and your dataset will be "small," use a VARRAY. What is "small?" I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K. q Here are some other indications that a VARRAY would be appropriate: you don't want to worry about deletions occurring in the middle of the dataset; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously. q If you need sparse PL/SQL tables, say, for "data-smart" storage, your only practical option is an index-by table. True, you could allocate and then delete elements of a nested table variable as illustrated in the section on NEXT and PRIOR methods, but it is inefficient to do so for anything but the smallest collections. q If your PL/SQL program needs to run under both Oracle7 and Oracle8, you also have only one option: index-by tables. Or, if your PL/SQL application requires negative subscripts, you also have to use index-by tables. Previous: 19.8 Collections Oracle PL/SQL Next: 20. Object Views Housekeeping Programming, 2nd Edition 19.8 Collections Book Index 20. Object Views Housekeeping The Oracle Library Navigation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.9 Which Chapter 20 Next: 20.2 INSTEAD OF Collection Type Should I Triggers Use? 20. Object Views Contents: Example: Using Object Views INSTEAD OF Triggers Syntax for Object Views Differences Between Object Views and Object Tables Not All Views with Objects Are Object Views Schema Evolution Object Views Housekeeping Postscript: Using the BFILE Datatype Although Oracle's object extensions offer rich possibilities for the design of new systems, few Oracle shops with large relational databases in place will want to, or be able to, completely reengineer those systems to use objects. To allow established applications to take advantage of the new object features over time, Oracle8 provides object views. With object views, you can achieve the following benefits: q Efficiency of object access. In PL/SQL, and particularly in Oracle Call Interface (OCI) applications, object programming constructs provide for the convenient retrieval, caching, and updating of object data. These programming facilities provide performance improvements, with the added benefit that application code can now be more succinct. q Ability to navigate using REFs (reference pointers). By designating unique identifiers as the basis of an object identifier (OID), you can reap the benefits of object navigation. For example, you can retrieve attributes from related "virtual objects" using dot notation rather than via explicit joins. q Easier schema evolution. In early versions of Oracle8, a pure object approach renders almost any kind of schema change at best ugly (see Chapter 18, Object Types). In contrast, object views offer more ways that you can change both the table structure and the object type definitions of an existing system. q Consistency with new object-based applications. If you need to extend the design of a legacy database, the new components can be implemented in object tables; new object-oriented applications requiring access to existing data can employ a consistent programming model. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Legacy applications can continue to work without modification. Other new features of Oracle can improve the expressiveness of any type of view, not just object views. Two features which are not strictly limited to object views are collections and "INSTEAD OF" triggers. Consider two relational tables with a simple master-detail relationship. Using the Oracle objects option, you can portray the detail records as a single nonscalar attribute (collection) of the master, which could be a very useful abstraction. In addition, by using INSTEAD OF triggers, you can tell Oracle exactly how to perform inserts, updates, and deletes on any view. These two features are available to both object views and nonobject views. (I've described collections in Chapter 19, Nested Tables and VARRAYs, and I describe INSTEAD OF triggers later in this chapter.) From an object-oriented perspective, there is one unavoidable "disadvantage" of object views, when compared to reengineering using an all-object approach: object views cannot retrofit any benefits of encapsulation. Insofar as old applications apply INSERT, UPDATE, and DELETE statements directly to table data, they will subvert the benefits of encapsulation normally provided by an object approach. As I discussed in Chapter 18, object-oriented designs typically prevent free-form access directly to object data. Despite this intrusion of reality, if you do choose to layer object views on top of a legacy system, your future applications can employ object abstractions and enjoy many benefits of encapsulation and information hiding. And your legacy systems are no worse off than they were before! Figure 20.1 illustrates this use of object views. Figure 20.1: Object views allow you to "bind" an object type definition to (existing) relational tables This chapter discusses the nuances of creating and, to a lesser extent, using object views. The discussion of PL/SQL-specific aspects of object views is rather terse, for two reasons: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1. Object views are substantially similar to regular object types, which are covered in a Chapter 18. 2. As a topic, object views are closer to SQL than to PL/SQL. However, PL/SQL developers who are interested in fully exploiting Oracle's object features must understand object views. This chapter pays close attention to the areas of difference between object tables and object views. 20.1 Example: Using Object Views In our first example, let's look at how object views might be used at Planetary Pages, a fictitious firm that designs Web sites. Their existing relational application tracks JPEG, GIF, and other images that they use when designing client Web sites. These images are stored in files, but data about them are stored in relational tables. To help the graphic artists locate the right image, each image has one or more associated keywords, stored in a straightforward master-detail relationship. Our legacy system has one table for image metadata: CREATE TABLE images ( image_id INTEGER NOT NULL, file_name VARCHAR2(512), file_type VARCHAR2(12), bytes INTEGER, CONSTRAINT image_pk PRIMARY KEY (image_id)); and one table for the keywords associated with the images: CREATE TABLE keywords ( image_id INTEGER NOT NULL, keyword VARCHAR2(45) NOT NULL, CONSTRAINT keywords_pk PRIMARY KEY (image_id, keyword), CONSTRAINT keywords_for_image FOREIGN KEY (image_id) REFERENCES images (image_id)); To create a more useful abstraction, Planetary Pages has decided to logically merge these two tables into a single object view. To do so, we must first create an object type with appropriate attributes. Since there are usually only a few keywords for a given image, this relationship lends itself to using an Oracle collection to hold the keywords. Before we can create the top-level type, we must first define a collection to hold the keywords. We choose a nested table because keyword ordering is unimportant and because there is no logical maximum number of keywords.[1] [1] If ordering were important, or if there were a (small) logical maximum number of Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
keywords per image, a VARRAY collection would be a better choice. See Chapter 19 for details. CREATE TYPE Keyword_tab_t AS TABLE OF VARCHAR2(45); From here, it's a simple matter to define the object type. To keep the example short, we'll define only a couple of methods. In the following object type specification, notice that the keywords attribute is defined on the Keyword_tab_t collection type: CREATE TYPE Image_t AS OBJECT ( image_id INTEGER, file_name VARCHAR2(512), file_type VARCHAR2(12), bytes INTEGER, keywords Keyword_tab_t, MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2, new_file_type IN VARCHAR2, new_bytes IN INTEGER) RETURN Image_t, MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t) RETURN Image_t, PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS, WNDS, RNPS, WNPS) ); Here is the body: CREATE TYPE BODY Image_t AS MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2, new_file_type IN VARCHAR2, new_bytes IN INTEGER) RETURN Image_t IS image_holder Image_t := SELF; BEGIN image_holder.file_name := new_file_name; image_holder.file_type := new_file_type; image_holder.bytes := new_bytes; RETURN image_holder; END; MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t) RETURN Image_t IS image_holder Image_t := SELF; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN image_holder.keywords := new_keywords; RETURN image_holder; END; END; I've presented the body only for completeness; from this point forward, I'll discuss object views without regard for the details of their underlying type bodies. At this point, there is no connection between the relational tables and the object type. They are independent organisms. It is when we build the object view that we "overlay" the object definition onto the tables. Finally, to create the object view, we use the following statement: CREATE VIEW images_v OF Image_t WITH OBJECT OID (image_id) AS SELECT i.image_id, i.file_name, i.file_type, i.bytes, CAST (MULTISET (SELECT keyword FROM keywords k WHERE k.image_id = i.image_id) AS Keyword_tab_t) FROM images i; Interestingly, there are only a couple of components of this statement that are unique to object views: OF Image_t This means that the view will return objects of type Image_t. WITH OBJECT OID (image_id) To behave like a "real" object instance, data returned by the view will need some kind of object identifier. By designating the primary key as the basis of a virtual OID, we can enjoy the benefits of referencing objects of this type from other object views. In addition, the select-list has an important requirement unique to object views. The select-list must define the same number of columns as there are attributes in the object type Image_t. The datatype of each retrieved column matches the datatype of its corresponding object attributes. You can use the CAST clause shown in the example in any view, not just in object views (but it does require the presence of the Oracle objects option). This subquery performs an "on-the-fly" conversion of the detail records into a collection type. For more details about the CAST and MULTISET operators, refer to Section 19.5, "Collection Pseudo-Functions" in Chapter 19. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
OK, now that we've created it, what can we do with it? Well, we can retrieve data from it just as if it were an object table. First, let's put some data into the underlying tables: INSERT INTO images VALUES (100001, 'smiley_face.gif', 'GIF', 813); INSERT INTO images VALUES (100002, 'peace_symbol.gif', 'GIF', 972); INSERT INTO KEYWORDS VALUES (100001, 'SIXTIES'); INSERT INTO KEYWORDS VALUES (100001, 'HAPPY FACE'); INSERT INTO KEYWORDS VALUES (100002, 'SIXTIES'); INSERT INTO KEYWORDS VALUES (100002, 'PEACE SYMBOL'); INSERT INTO KEYWORDS VALUES (100002, 'JERRY RUBIN'); Now, from SQL*Plus, you can make a query like the following: SELECT image_id, file_name, keywords FROM images_v; Which yields: IMAGE_ID FILE_NAME KEYWORDS -------- ---------------- ------------------------------------------------------- 100001 smiley_face.gif KEYWORD_TAB_T('HAPPY FACE', 'SIXTIES') 100002 peace_symbol.gif KEYWORD_TAB_T('JERRY RUBIN', 'PEACE SYMBOL', 'SIXTIES') Or, in a PL/SQL block, we can fetch and display a row of data very easily: SET SERVEROUTPUT ON SIZE 100000 DECLARE CURSOR icur IS SELECT VALUE(v) FROM images_v v WHERE image_id = 100001; image Image_t; BEGIN OPEN icur; FETCH icur INTO image; CLOSE icur; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/* Print it out, just to prove that we got it */ DBMS_OUTPUT.PUT_LINE('Image: ' || image.image_id); DBMS_OUTPUT.PUT_LINE('File: ' || image.file_name); DBMS_OUTPUT.PUT('Keywords: '); /* image.keywords is a nested table, and we can just loop || through it to print each keyword. */ FOR key_elt IN 1..image.keywords.COUNT LOOP DBMS_OUTPUT.PUT(image.keywords(key_elt) || ' '); END LOOP; DBMS_OUTPUT.NEW_LINE; END; This results in: Image: 100001 File: smiley_face.gif Keywords: HAPPY FACE SIXTIES See Chapter 19 for more examples of retrieving data from an object table. Other things you can do with object views include the following: q Define REFs that point to "virtual" objects (discussed in detail in Section 20.4, "Differences Between Object Views and Object Tables" later in this chapter). q Encapsulate an object view (more or less) using object methods and/or PL/SQL packages (discussed in-depth in Chapter 18). q Write INSTEAD OF triggers that will allow direct manipulation of the view's contents (discussed in the next section). Previous: 19.9 Which Oracle PL/SQL Next: 20.2 INSTEAD OF Collection Type Should I Programming, 2nd Edition Triggers Use? 19.9 Which Collection Type Book Index 20.2 INSTEAD OF Triggers Should I Use? The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.