Page 603
TEST_SCORE OBJECT 2 0 TEST_SCORE_TABLE COLLECTION 0 0 5 rows selected.
As you can see in the previous query, the TYPECODE column will return OBJECT if a type is defined as an object or COLLECTION if a type is defined as a VARRAY or a nested table. For example, AVAILABLE_ROOM was defined as an object so its TYPECODE is OBJECT, whereas AVAILABLE_ROOMS was defined as a VARRAY of AVAILABLE_ROOM so its TYPECODE is COLLECTION.
If you want to look at the attributes that have been defined for a type, you can query the USER_TYPE_ATTRS data dictionary view. Listing 23.17 provides a description of the USER_TYPE_ATTRS view and a sample query which returns the attributes that have been defined for the TEST_SCORE type.
Listing 23.17. Looking at the contents of USER_TYPE_ATTRS.
SQLWKS> desc user_type_attrs
Column Name Null? Type
------------------------------ -------- ----
TYPE_NAME NOT NULL VARCHAR2(30)
ATTR_NAME NOT NULL VARCHAR2(30)
ATTR_TYPE_MOD VARCHAR2(7)
ATTR_TYPE_OWNER VARCHAR2(30)
ATTR_TYPE_NAME VARCHAR2(30)
LENGTH NUMBER
PRECISION NUMBER
SCALE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
SQLWKS> select attr_name, attr_type_name, length
2> from user_type_attrs
3> where
4> type_name = `TEST_SCORE';
ATTR_NAME ATTR_TYPE_NAME LENGTH
------------------------------ ------------------------------ --------
STUDENT_ID VARCHAR2 6
SCORE NUMBER
2 rows selected.
A new feature provided with the Oracle8 server is the capability to supply a subquery in the FROM clause of a SELECT statement in place of a table or view. Listing 23.18 presents an example.
Page 604
Listing 23.18. Using a subquery in the FROM Clause.
SQLWKS> select Operating_Room, Surgeon_ID, Last_Name
2> from (select Patient_ID, Last_Name from Patient
3> where Status != `MORGUE') P, Surgery_Schedule S
4> where
5> P.Patient_ID = S.Patient_ID;
OPERAT SURGEO LAST_NAME
------ ------ ------------------------------
A10 G101 JOHNSON
1 row selected.
ANALYSIS
In line 2 of Listing 23.18, a subquery of the Patient table is enclosed in parentheses; it returns Patient_ID and Last_Name only for those patients whose value for Status is not equal to `MORGUE'. In line 3, the subquery is joined with the Surgery_Schedule table based on the join criterion found on line 5.
Oracle8 gives a developer the capability to call routines written in other languages from a PL/SQL subprogram. These routines, written in a 3GL and residing in a shared library, are referred to as external procedures. To register an external procedure, Oracle8 includes a new statement that will let a developer reference an external library which contains the external procedures: CREATE LIBRARY (see Listing 23.19).
| NOTE |
At the present time, only external procedures must be written in C. |
Here is the syntax to create a library that contains external procedures:
CREATE LIBRARY library_name as `file_name';
The variables are defined as follows:
library_name is the name of the library that can be referenced from PL/SQL subprograms.
file_name is the directory and name of the shared library.
Listing 23.19. Creating a library alias for referencing external procedures.
SQLWKS> create library flugle_lib as `/opt/flugle/lib/flugle_lib.so'; Statement processed.
Page 605
Oracle8 also introduces a new feature called object views. An object view is an extension of the relational view that you've learned about. One of Oracle's goals with Oracle8 is to begin supporting object-relational applications; the object view is part of this strategy. Object views can coexist with relational views of the same tables; therefore, users can continue to use traditional relational applications that work with a set of tables in an Oracle database while developers experiment with object-oriented applications that rely on object views against the same tables.
Listing 23.20 presents an example of an object view. First, the Patient table is created. Next, the Patient_Type datatype is created which mirrors the Patient table. Finally, the object view Patient_View is created which uses Patient_ID as the object ID.
Listing 23.20. Looking at the contents of USER_TYPES.
SQLWKS> create table Patient (
2> Patient_ID varchar2(6),
3> Last_Name varchar2(30),
4> First_Name varchar2(20),
5> MI varchar2(1),
6> SS_Number varchar2(9),
7> Location varchar2(6),
8> Status varchar2(20),
9> Admittance_Date date,
10> Discharge_Date date,
11> Carrier varchar2(30));
Statement processed.
SQLWKS> create type Patient_Type (
2> Patient_ID varchar2(6),
3> Last_Name varchar2(30),
4> First_Name varchar2(20),
5> MI varchar2(1),
6> SS_Number varchar2(9),
7> Location varchar2(6),
8> Status varchar2(20),
9> Admittance_Date date,
10> Discharge_Date date,
11> Carrier varchar2(30));
Statement processed.
SQLWKS> create view Patient_View of Patient_Type
2> with object oid (Patient_ID) as
3> select Patient_ID, Last_Name, First_Name, MI, SS_Number,
4> Location, Status, Admittance_Date, Discharge_Date, Carrier
5> from Patient;
Statement processed.
Page 606
Although Oracle has provided an easy migration path to Oracle8, it is difficult to predict how quickly existing Oracle customers will migrate their databases from Oracle7 to Oracle8. In any event, if you are planning to develop an application that will be used with Oracle7 databases, you won't be able to use the new features discussed in this chapter in that version of the application. Of course, you could develop a separate version for an Oracle8 database that incorporates some of the new features. However, at the present time, support for the new features described in this chapter is limited to use in:
However, Oracle Corporation and other third-party software tool vendors will soon provide tools that are able to take advantage of the new datatypes and the features provided by the Object Option. Until then, you have an opportunity to learn how these features can be utilized in an application.
This lesson was a brief introduction to some of the new features provided by the Oracle8 server, such as the following: