Object Types
| Object types in Oracle are the equivalent of classes in Java. Like Java classes, they have attributes and methods. An object type can be used as the datatype in a table column; as an attribute in another object; and as an argument, variable, or return value for a method. In Oracle, attributes correspond to the columns in a table. In the same way that attributes in Java can be of either primitive types, such as int and float, or other classes, such as String and FileWriter, attributes in Oracle can be either standard SQL datatypes, such as NUMBER and VARCHAR2, or other object types, including nested tables and arrays. In Oracle, methods are functions or procedures written in PL/SQL, Java, or C/C++. We'll take a look at methods later; for now, we'll consider simple object types without methods. Creating Object TypesCreating an object type resembles creating a table but does not actually allocate any space for storing data. This is the basic format used to define a simple object type without any methods: CREATE TYPE type_name AS OBJECT ( column_name1 column_type1 [, column_name2 column_type2[,...]] ); This does not create an object it only defines the type. It assigns a name to the type and assigns names and types to each of its attributes. We can create a type, POINT_TYPE, which has two attributes, X and Y, like this: SQL> CREATE TYPE POINT_TYPE AS OBJECT ( 2 X, 3 Y 6 ) 7 / Type created. Notice that, as we did for PL/SQL, we need to type a slash at the end, rather than a semicolon, in order to execute this command. If there had been errors in compilation, we would've typed SHOW ERROR to get SQL*Plus to list them. Now when we create a table, we can use this object type as the type for a column, just as we would any other datatype. Here is an example of how we might use the POINT_TYPE object in a relational table. SQL> CREATE TABLE CIRCLES ( 2 RADIUS NUMBER, 3 CENTER POINT_TYPE 4 ); Table created. Assuming that the table has some data we'll see how to perform inserts into an object type in a moment we can query a table that includes object types in the same way that we do other tables. SQL> select radius, center from circles; RADIUS CENTER(X, Y) ---------- ------------------------------------------- 3 POINT_TYPE(2, 2) Notice that the column CENTER is returned as a POINT_TYPE. If we want the values X and Y returned individually, we need to include and fully qualify the object's X and Y attributes in the SELECT statement with an alias for the table and the object's column name as follows: SELECT RADIUS, C.CENTER.X, C.CENTER.X FROM CIRCLES C; This provides a result in a more familiar format: SQL> SELECT RADIUS, C.CENTER.X, C.CENTER.X FROM CIRCLES C; RADIUS CENTER.X CENTER.X ---------- ---------- ---------- 3 2 2 Which format we use depends in large part on our application and whether it is prepared to accept objects in addition to standard SQL types. Object TablesWe can also create a table where every row is an object. This type of table is called an object table, and it has the interesting characteristic that you can use it in two ways: either as a table with a single column of that object type or as a standard relational table that has the same column definitions as the object type. The format for creating an object table is: CREATE TABLE table_name OF object_type; Suppose we wanted to keep track of a number of points, perhaps to create a scattergram to test how well two variables are correlated for example, inches of rain in April and number of flowers in May. We might create a table: SQL> CREATE TABLE BLOOM_DATA OF POINT_TYPE; Table created. There are two advantages to using object tables. The first advantage is that object tables simplify the use of objects somewhat. Because an object table's column types coincide with the object type, we don't need to qualify the object attributes with the object name. We can insert data into an object table just as we would into a relational table. SQL> INSERT INTO BLOOM_DATA VALUES(2,3); 1 row created. Querying the attributes of an object in an object table is easier than querying a multicolumn table with objects. SQL> SELECT X,Y FROM BLOOM_DATA; X Y ---------- ---------- 2 3 The second advantage is that object tables can be a convenient way of using object types as templates for creating tables, ensuring that multiple tables have the same structure. For example, we can create an address book type and use it as a template for separate but identical tables for business, school, and personal contacts although it may be preferable to have a single table and add a column identifying the type of contact. MethodsAlthough we haven't explicitly included any methods in our object definition above, our objects already include one automatically: the default constructor. We need a constructor in order to create objects, particularly when we use INSERT or UPDATE to add data to our tables. The constructor has the same name as the object type and it takes one argument for every attribute in the object type. The POINT_TYPE object that we defined above, for example, has the two attributes, X and Y. To insert a row into our CIRCLES table, we use the following INSERT statement with the POINT_TYPE(x, y) constructor: SQL> INSERT INTO CIRCLES VALUES ( 2 3, 3 POINT_TYPE(5,5) 4 ); 1 row created. Using a constructor to create and insert an object into an object table is optional. For example, we can insert a row into our BLOOM_DATA table, as follows: INSERT INTO BLOOM_DATA(POINT_TYPE(2, 12)); But because of the object table's dual nature, we can also treat it as an ordinary relational table with columns corresponding to those of the object type. INSERT INTO BLOOM_DATA VALUES(3, 15); User-Defined MethodsConstructors are necessary and, fortunately, they are provided for free by the system. In addition, we may also wish to have other methods associated with our object types to provide behavior that we want available anywhere we might want to use that object. Encapsulating the behavior of objects together with their attributes makes objects easier to understand, maintain, and reuse. If we change the underlying representation of our data, for example, any code that needs to change to correspond to those changes is in the same place. To define our own methods, we first need to declare them when we define the object type. This part is the same, regardless of what language, (PL/SQL, Java, or C/C++), we will use to implement them. Methods can be either procedures or functions. As you may remember, the difference between the two is that functions return a value. Functions are more useful than procedures because we can use them in such places as select list items, WHERE clauses, and ORDER BY clauses. Because of this, our examples will be limited to functions. CREATE TYPE type_name AS OBJECT ( column_name1 column_type1 [, column_name2 column_type2[,...]] , MEMBER FUNCTION method_name [(argument_list)] RETURN datatype [, additional methods ...] ); For each defined method, this specifies the method's name, its argument list (optionally), and its return type. If the method is implemented in PL/SQL, we need to perform one more step, creating the type body. CREATE TYPE BODY type_name AS MEMBER FUNCTION method_name RETURN datatype {AS|IS} variable declarations ... BEGIN method code ... RETURN return value; END; END; [additional functions or procedures] END; Notice that each function or procedure block is preceded by either of the keywords AS or IS it doesn't make any difference which. We'll use AS. Let's create a new object type, LINE_TYPE, that contains two points, a starting point and an end point. We'll include a method to calculate length, GETLENGTH(), which takes no parameters and returns a NUMBER. SQL> CREATE TYPE LINE_TYPE AS OBJECT ( 2 START_POINT POINT_TYPE, 3 END_POINT POINT_TYPE, 4 MEMBER FUNCTION GETLENGTH RETURN NUMBER 5 ); 6 / Type created. As mentioned above, we can create methods using PL/SQL, Java, or C/C++. Java and PL/SQL are preferable for short procedures or procedures that interact a lot with the database. C/C++ procedures, because they are compiled into native machine code, can execute more quickly but they incur a lot of overhead because they are executed as external processes outside the database. C/C++ is preferable for long, complicated routines that do not interact much with the database. The following is the implementation of the GETLENGTH() method in PL/SQL: SQL> CREATE OR REPLACE TYPE BODY LINE_TYPE AS 2 3 MEMBER FUNCTION GETLENGTH RETURN NUMBER AS 4 BEGIN 5 RETURN SQRT( 6 POWER(START_POINT.X-END_POINT.X,2) 7 +POWER(START_POINT.Y-END_POINT.Y,2) 8 ); 9 END; 10 11 END; 12 / Type body created. The code in this function consists of this one statement: RETURN SQRT( POWER(START_POINT.X-END_POINT.X,2) +POWER(START_POINT.Y-END_POINT.Y,2) The important thing to notice is that we automatically have access to the object's START_POINT and END_POINT and their attributes. This is because, even though we did not define any parameters for our function, we get one for free: SELF. This parameter refers to the current instance of the object that the method belongs to, in this case, LINE_TYPE. It is Oracle's equivalent of Java's this. Because no other object is specified, START_POINT and END_POINT are assumed to refer to the SELF object. The following code, where we explicitly use SELF, means exactly the same thing as the code above. RETURN SQRT( POWER(SELF.START_POINT.X-SELF.END_POINT.X,2) +POWER(SELF.START_POINT.Y-SELF.END_POINT.Y,2) If we create a table that includes a LINE_TYPE and call this GETLENGTH() function in a SELECT statement that queries that table, Oracle will iterate through every row selected, passing each row's LINE_TYPE object to this function. Let's create an object table of LINE_TYPE to test our method. SQL> CREATE TABLE LINES OF LINE_TYPE; Table created. We'll insert a line of data. SQL> INSERT INTO LINES VALUES(POINT_TYPE(1,1), POINT_TYPE(4,5)); 1 row created. Notice that, because LINES is an object table, we don't have to use the LINE_TYPE constructor to insert LINE_TYPE objects into it, but because each LINE_TYPE contains POINT_TYPE objects, we do need to use the POINT_TYPE constructor to insert the starting point and end point we get only one free layer with our object table. We can call the GETLENGTH() method as follows: SQL> SELECT L.GETLENGTH() FROM LINES L; L.GETLENGTH() ------------- 5 Notice that in the call to GETLENGTH(), we need to qualify the method name with an alias for the table name. It's pretty common for functions, such as GETLENGTH(), not to take any arguments. When a function or procedures takes no arguments, we omit the parentheses and argument list in the CREATE TYPE and CREATE TYPE BODY statements, but we still need to include an empty pair when we call it in a SELECT statement. Note that this is inconsistent with regular SQL functions, which do not take parenthesis if they do not have arguments. Let's take a look at a couple more examples. First we'll take a look at a method that takes an argument. Arguments can be of any SQL type, e.g., VARCHAR2, NUMBER, or user-defined types, including object types. We can also optionally specify whether it is an input variable, an output variable, or both, using the keywords IN, OUT, and INOUT but only IN makes sense for functions, and because this is the default mode, we usually just omit it. For this example, we'll define yet another type, RECTANGLE, with two methods, a CONTAINS method, which takes an argument of POINT_TYPE; and GETAREA, which is a special type of method a map method that we'll explain later. First we create the type. SQL> CREATE TYPE RECTANGLE_TYPE AS OBJECT ( 2 TOP_LEFT POINT_TYPE, 3 WIDTH NUMBER, 4 HEIGHT NUMBER, 5 MAP MEMBER FUNCTION GETAREA RETURN NUMBER, 6 MEMBER FUNCTION CONTAINS (PT IN POINT_TYPE) RETURN NUMBER 7 ) 8 / Type created. Next we create the type body for the PL/SQL code. SQL> CREATE OR REPLACE TYPE BODY RECTANGLE_TYPE AS 2 3 MAP MEMBER FUNCTION GETAREA RETURN NUMBER AS 4 BEGIN 5 RETURN WIDTH*HEIGHT; 6 END; 7 8 MEMBER FUNCTION CONTAINS 9 (PT IN POINT_TYPE) RETURN NUMBER AS 10 IS_INSIDE NUMBER := 0; 11 BEGIN 12 IF( PT.X > TOP_LEFT.X 13 AND PT.X < TOP_LEFT.X + WIDTH 14 AND PT.Y > TOP_LEFT.Y 15 AND PT.Y < TOP_LEFT.Y + HEIGHT 16 ) 17 THEN 18 IS_INSIDE := 1; 19 END IF; 20 RETURN IS_INSIDE; 21 END; 22 END; 23 / Type body created. We'll create a table that uses this type: SQL> CREATE TABLE RECTANGLES ( 2 LABEL VARCHAR2(25), 3 RECTANGLE RECTANGLE_TYPE 4 ); Table created. And insert some data. SQL> INSERT INTO RECTANGLES VALUES('One', 2 RECTANGLE_TYPE(POINT_TYPE(10,50), 40, 20)); 1 row created. SQL> INSERT INTO RECTANGLES VALUES('Two', 2 RECTANGLE_TYPE(POINT_TYPE( 0, 0), 10, 10)); 1 row created. Notice that we need to use two constructors, one nested within the other, because we've nested a POINT_TYPE inside a RECTANGLE_TYPE inside a regular multicolumn table. (In the previous example, we didn't need to use a constructor for highest level object, LINE_TYPE, because it was an object table of LINE_TYPE.) The first method we'll test is the CONTAINS() method. It determines whether a point that is passed in as an argument is inside the rectangle defined in the RECTANGLE_TYPE object. Oracle does not support a Boolean type, so we use 0 to represent false and 1 to represent true. SQL> SELECT LABEL, R.RECTANGLE.CONTAINS(POINT_TYPE(20,60)) 2 AS CONTAINS 3 FROM RECTANGLES R; LABEL CONTAINS ------------------------- ---------- One 1 Two 0 SQL> SELECT LABEL, R.RECTANGLE.CONTAINS(POINT_TYPE( 5, 5)) 2 AS CONTAINS 3 FROM RECTANGLES R; LABEL CONTAINS ------------------------- ---------- One 0 Two 1 Comparison MethodsThe second method we included in the object type and object type body above is one of two types of comparison methods a map method. A map method is a type of comparison method that takes no arguments and returns a standard scalar Oracle SQL type, such as NUMBER or VARCHAR2, that Oracle will use implicitly to perform comparisons. Suppose that we have a number of rectangles and want to put them in order on what basis do we do that? Perhaps arbitrarily, our map method for RECTANGLE_TYPE (as suggested by its name, GETAREA()), returns the area of the rectangle. We can call our map method, GETAREA(), directly, like the other methods we've seen so far. SQL> SELECT LABEL, R.RECTANGLE.GETAREA() 2 FROM RECTANGLES R; LABEL R.RECTANGLE.GETAREA() ------------------------- --------------------- One 800 Two 100 However, the important characteristic of this map method is that Oracle will use it implicitly when we use a RECTANGLE_TYPE object in a comparison, such as in a WHERE clause or an ORDER BY clause. If we order the RECTANGLES table by RECTANGLE, for example, we should see our rectangles in ascending order (the default order) by area. SQL> SELECT LABEL, R.RECTANGLE.GETAREA() 2 FROM RECTANGLES R 3 ORDER BY RECTANGLE; LABEL R.RECTANGLE.GETAREA() ------------------------- --------------------- Two 100 One 800 We can also see the indirect results of the GETAREA() method when we compare RECTANGLE_TYPE objects. In the following example, we'll create a RECTANGLE_TYPE object directly in our SELECT statement, with an area of 200 to use for comparison in a WHERE clause. SQL> SELECT LABEL, R.RECTANGLE.GETAREA() 2 FROM RECTANGLES R 3 WHERE RECTANGLE > RECTANGLE_TYPE(NULL, 10, 20); LABEL R.RECTANGLE.GETAREA() ------------------------- --------------------- One 800 Notice that this comparison works only because both objects being compared are of the RECTANGLE_TYPE type and we have a map method for that type. Even though what ultimately gets compared is a NUMBER representing the area, we can't provide a NUMBER directly; a WHERE clause like this does not work. SQL> SELECT LABEL, R.RECTANGLE.GETAREA() 2 FROM RECTANGLES R 3 WHERE RECTANGLE > 200; WHERE RECTANGLE > 200 * ERROR at line 3: ORA-00932: inconsistent datatypes There is one other type of comparison method, an order method. An object type's order method takes one argument, of the same object type, to which it compares the SELF object. If the object whose method is called, SELF, is smaller than the object that is passed in as an argument, the value returned must be negative. If the objects are equal, the value returned must be zero, and if SELF is larger than the object passed in, a positive value must be returned. How this result is determined is entirely up to the order method. This is how we could have used an order method in our example. First, the type definition: CREATE TYPE RECTANGLE_TYPE AS OBJECT ( TOP_LEFT POINT_TYPE, WIDTH NUMBER, HEIGHT NUMBER, ORDER MEMBER FUNCTION GETAREA (RECT IN RECTANGLE_TYPE) RETURN NUMBER, /* ... */ ) / And the type body: CREATE OR REPLACE TYPE BODY RECTANGLE_TYPE AS ORDER MEMBER FUNCTION GETAREA (RECT IN RECTANGLE_TYPE) RETURN NUMBER AS BEGIN RETURN (WIDTH*HEIGHT)-(RECT.WIDTH*RECT.HEIGHT); END; /* ... */ END; / A map method has the advantage of efficiency because it associates each object with a single scalar value. An order method has the advantage of flexibility because it can perform an arbitrarily complex comparison between two objects, which might not necessarily be possible by reducing each object to a single value. However, an order method is a user-defined function that is called for each pair of objects to be compared making it slower than using a map method, especially for sorts, where it may need to be called many times. Implementing Methods in JavaFor Java programmers who are unfamiliar with PL/SQL, implementing methods in Java may seem a promising option. Unfortunately, there are quite a few steps required to do this. If the methods are fairly simple, you may find it more worth your while to master just enough PL/SQL to get the job done. If the methods are not simple or you really don't want to code in PL/SQL, you may wish to consider implementing the object methods outside the database use the database to hold only object attributes and implement methods in client-side Java classes that map to the server-side database objects. Nonetheless, there are some advantages to implementing methods in the database in Java if the methods are substantial enough to justify the overhead. Methods that run inside the database are much faster, especially if they are database-intensive. Compared with PL/SQL, the flexibility, power, and expressiveness of the Java language (not to mention its familiarity) are pretty compelling when faced with a daunting programming task that is performance-critical. Portability may clinch the case for implementing important code in Java: Should you later decide to move functionality out of the database and into a client application written in Java (or into a middle-tier), you won't need to rewrite the code. In fact, you may wish to develop the method outside the database first, then move it into the database only once it is has been fully debugged and tested, using your familiar development tools. There is an important restriction for methods implemented in Java: They can be only static methods because they are not passed a SELF value. In other words, they do not have an object instance available. If you wish to implement a function that operates on object instances for example, every row in the SELECT statement's result set you will need to pass the object into the function or a value that will allow the method to get the object for itself as one of the parameters. The steps necessary to implement an object method in Java are:
For this example, we'll create an object type for triangles. The object composition is shown in Figure 6-1. Figure 6-1. Object composition.
We could use a type specification and separate type body, like we did before. CREATE TYPE TRIANGLE_TYPE AS OBJECT ( PT_A POINT_TYPE, PT_B POINT_TYPE, PT_C POINT_TYPE, STATIC FUNCTION GETAREA(LAB IN VARCHAR2) RETURN NUMBER ) / CREATE TYPE BODY TRIANGLE_TYPE AS STATIC FUNCTION GETAREA(LAB in VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA NAME 'Triangle.getArea(java.lang.String)return double'; END; / In this case, however, we don't have any code in our body type, so we can include the call specification in the type specification, instead. We'll execute this all-in-one statement in place of the two separate statements above: SQL> CREATE TYPE TRIANGLE_TYPE AS OBJECT ( 2 PT_A POINT_TYPE, 3 PT_B POINT_TYPE, 4 PT_C POINT_TYPE, 5 6 STATIC FUNCTION GETAREA(LAB in VARCHAR2) 7 RETURN NUMBER 8 AS LANGUAGE JAVA 9 NAME 'Triangle.getArea(java.lang.String)return double' 10 ) 11 / Type created. Now we can create a table using the TRIANGLE_TYPE. SQL> CREATE TABLE TRIANGLES ( 2 LABEL VARCHAR2(25), 3 TRIANGLE TRIANGLE_TYPE, 4 CONSTRAINT TRIANGLE_PK 5 PRIMARY KEY(LABEL) 6 ); Table created. In the interest of simplicity, we've been lax in identifying primary keys in previous examples, but in this case, because we are going to use the LABEL column as the identifier in our method, we need to ensure that it is unique. Next, we'll populate the table with some data. SQL> INSERT INTO TRIANGLES 2 VALUES('One', 3 TRIANGLE_TYPE(POINT_TYPE(1,2),POINT_TYPE(5,6), POINT_TYPE(3,2))); 1 row created. SQL> INSERT INTO TRIANGLES 2 VALUES('Two', 3 TRIANGLE_TYPE(POINT_TYPE(0,0),POINT_TYPE(0,4), POINT_TYPE(4,0))); 1 row created. SQL> COMMIT; Commit complete. To create the Java class and load it into the database, we'll follow most of the same steps we did when we created Java stored procedures in the previous chapters. As before, we won't examine the JDBC code in depth here that's coming up in the next chapter. Because it's easiest to code and debug outside the database, we'll write our code to work both outside and inside the database. The biggest difference between running inside and outside the database is that, inside, we have a default connection, and outside, we need to obtain our own connection. Cleanup is also a little different because we shouldn't close the default connection. To facilitate this, our class Triangle will include a main() to get a connection before calling our method getArea() and will set a flag so that we can clean up appropriately. import java.sql.*; public class Triangle { static Connection conn = null; static String testval="One"; boolean isLocal = false; public static void main(String [] args) { try { DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); conn = DriverManager.getConnection( "jdbc:oracle:thin:@noizmaker:1521:osiris", "david", "bigcat"); isLocal = true; getArea(testval); } catch(SQLException e) { System.out.println("Caught " + e); } } Because the getArea() method doesn't automatically have an object instance to work with, we need to either provide the object or pass it a value so that it can obtain the information from it on its own. In the sample implementation here, we'll pass it the value for LABEL so that our method can obtain the corresponding TRIANGLE object from the TRIANGLES table. (A better implementation would not need to be tied to a specific table. The right way to do it would be for getArea() to take an argument of type TRIANGLE_TYPE, but interpreting an object which we could pass to Java as java.sql.Struct is beyond the scope of what we're doing here.) We use a SELECT statement to obtain the X and Y coordinates of each of the three points in TRIANGLE as double, using LABEL in the WHERE clause. public static double getArea(String label) { double area = 0; try { if(conn==null) { conn = DriverManager.getConnection("jdbc:default:connection:"); } Statement stmt = conn.createStatement(); String sql = "SELECT " + "T.TRIANGLE.PT_A.X, T.TRIANGLE.PT_A.Y," + "T.TRIANGLE.PT_B.X, T.TRIANGLE.PT_B.Y," + "T.TRIANGLE.PT_C.X, T.TRIANGLE.PT_C.Y " + "FROM TRIANGLES T WHERE LABEL ='" + label +"'"; ResultSet rs = stmt.executeQuery(sql); To calculate the triangle's area, we'll use Heron's formula. According to this formula, given the lengths of the three sides of a triangle, a, b, and c, the area A is
Because our SELECT statement obtains only the x and y coordinates, we use a helper method, getLength(), to calculate the lengths a, b, and c, and from that, the area. if(rs.next()) { double ax, ay, bx, by, cx, cy; double a, b, c; ax = rs.getDouble(1); ay = rs.getDouble(2); bx = rs.getDouble(3); by = rs.getDouble(4); cx = rs.getDouble(5); cy = rs.getDouble(6); a = getLength(ax, ay, bx, by); b = getLength(bx, by, cx, cy); c = getLength(cx, cy, ax, ay); /* Calculate area using Heron's formula */ double s = (a + b +c)/2; // perimeter/2 area = Math.sqrt(s * (s - a) * (s - b) * (s - c)); } Finally, we do some cleanup; close the resources we used (except the default connection!) and return the area that we calculated. catch(SQLException e) { System.out.println("Caught " + e); } finally { rs.close(); stmt.close(); if(isLocal) { conn.close(0); // don't close default connection! } } return area; } This is the helper method for calculating the distance between two points, ax, ay, and bx, by: static double getLength(double ax, double ay, double bx, double by) { return Math.sqrt(Math.pow((ax-bx),2) + Math.pow((ay-by),2)); } } One of the benefits of object-oriented programming is that it makes it easier to reuse code. One of the new object-relational features in Oracle 9i, the ability to create transient objects to use in SELECT statements, can help us do this. In a previous example, we created a LINE_TYPE object that had a method for calculating the length of a line. If our code doesn't need to support Oracle 8i we can reuse the GETLENGTH() method in the LINE_TYPE object type and replace the Java code for calculating length. We could use the following query to obtain the lengths a, b, and c from Oracle: // Oracle 9i version: String sql = "SELECT " + "LINE_TYPE(T.TRIANGLE.PT_A,T.TRIANGLE.PT_B).GETLENGTH(), " + "LINE_TYPE(T.TRIANGLE.PT_B,T.TRIANGLE.PT_C).GETLENGTH(), " + "LINE_TYPE(T.TRIANGLE.PT_C,T.TRIANGLE.PT_A).GETLENGTH() " + "FROM TRIANGLES T WHERE LABEL ='" + label +"'"; ResultSet rs = stmt.executeQuery(sql); double a, b, c; if(rs.next()) { a = rs.getDouble(1); b = rs.getDouble(2); c = rs.getDouble(3); /* Calculate area using Heron's formula */ double s = (a + b +c)/2; // perimeter/2 area = Math.sqrt(s * (s - a) * (s - b) * (s - c)); } After we've compiled and tested this code, we can load the class into the database by using the Oracle loadjava utility. At the command prompt, we enter: C:\myjava>loadjava -v -u david/bigcat Triangle.class This command produces the following output: arguments: '-v' '-u' 'david/bigcat' 'Triangle.class' creating : class Triangle loading : class Triangle Now we can query the table, using our method. Because GETAREA() is a static method, it is not associated with any instance, and we need to qualify it with the type name, TRIANGLE_CLASS, not the instance name or the column name in the TRIANGLES table, TRIANGLE. We need to pass LABEL as an argument to the method so that it can retrieve the proper row and calculate the area (essentially using a subquery) for each row in the result set. SQL> SELECT LABEL, TRIANGLE_TYPE.GETAREA(LABEL) FROM TRIANGLES; LABEL TRIANGLE_TYPE.GETAREA(LABEL) ------------------------- ---------------------------- One 4 Two 8 Troubleshooting Java MethodsImplementing methods in Java may seem to be a lot of trouble. If you are experimenting with your own code and are experiencing problems, you should be aware that this is common; troubleshooting can be a tedious and sometimes frustrating experience. A good approach is to take it one step at a time and simplify each step as much as possible. For example, to make sure that the method is being called correctly that it is being passed the right parameters and is returning the expected value you might try replacing the Java code with something simple at first. It may, for example, be easier to get this version of the getArea() method working before the real Java method. public double getArea(String label) { return label.length(); } This bypasses any problems that the JDBC and SQL code might cause and lets us concentrate on making sure the call specification and loadjava are working as expected. Another troubleshooting technique is to eliminate possible problems with the object type and object type body by publishing the method as a standalone stored procedure or function this won't interfere with the same method being called as an object method. After using loadjava to upload the Triangle class into the database, you can publish the getArea() method as follows: SQL> CREATE OR REPLACE FUNCTION GETTRIANGLEAREA(LABEL VARCHAR2) 2 RETURN NUMBER 3 AS LANGUAGE JAVA 4 NAME 'Triangle.getArea(java.lang.String)return double'; 5 / Function created. Now you can test this function independently of the object type mechanism. SQL> SELECT GETTRIANGLEAREA('One') FROM DUAL; GETTRIANGLEAREA('ONE') ---------------------- 4 Note that, although we don't need to use the TRIANGLES table explicitly in our query if we already know a valid value for LABEL, it still must be present because our method uses it implicitly. Finally, in the course of testing, debugging, and troubleshooting, you will need to drop object types, type bodies, and tables, perhaps repeatedly. The commands you need to do this and the order you need execute them are: DROP TABLE tablename; DROP TYPE BODY object_type_name; DROP OBJECT TYPE object_type_name; Given all that, you may wonder whether implementing object methods in Java is worth all the trouble. If it makes sense from a design point of view and seems warranted by performance considerations, you may wish first to try to build a prototype or a pilot project before committing to Java object methods. Nested CollectionsIn some of the examples above, we've seen that we can nest single objects within other objects. For example, our CIRCLE_TYPE included a POINT _TYPE for the center. We are not limited to including single objects within other objects; we can also include objects with multiple values called collections. There are two collection types in Oracle, nested tables and VARRAYs. They are similar in the type of data they can store because they both can include objects. The differences are:
In Oracle 8i, only a single level of nesting is supported. Oracle 9i allows multiple levels of nesting. Except as noted, most of the examples in this section will work with either version of Oracle. Nested TablesAs you might expect, nested tables are tables that are stored as part of another table. We use them much like we use other object types: We define a table type just as we did an object type and we can use a table type, like an object type, as the datatype in a table column, as an attribute in another object, and as an argument, variable, or return value for a method. This is the general format for creating a table type: CREATE TYPE table_type_name AS TABLE OF {object_type|datatype} / Suppose we want to keep track of the daily high temperature for a group of cities for an indefinite period of time. We can create a nested table of NUMBER to hold daily highs for each city. First, we create the table type: SQL> CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER 2 / Type created. Now we can use NUMBER_TYPE almost anywhere that we would use a standard SQL datatype, including as the column type of a relation table. The format for creating a table that contains a nested table is almost the same as creating any other table, except that we need to provide one more piece of information: where to store the information for the nested tables. This storage clause specifies the name of a table it is to create and use to hold the information for all of the nested tables in our main table. (Apart from creating it, we won't ever need to refer to this storage table again set it and forget it.) The format is: CREATE TABLE ( column_name1 {data_type|object_type|table_type} [,column_name2 {data_type|object_type|table_type}[,...]] [additional create table options] ) NESTED TABLE column_name STORE AS storage_table_name; Let's create our table of cities, including state and country, and a table of temperatures for each. SQL> CREATE TABLE DAILY_HIGHS( 2 CITY VARCHAR2(32), 3 STATE VARCHAR2(32), 4 COUNTRY VARCHAR(32), 5 TEMPS NUMBER_TABLE 6 ) 7 NESTED TABLE TEMPS STORE AS DAILY_HIGHS_TEMPS_STORAGE; Table created. We insert data into a table that contains a nested table in much the same way that we insert into a table that contains an object: by using a constructor. A table constructor has the same name as the table type and it accepts multiple rows separated by commas. type_name([value[, value[,...]]]); We can include as many values as we like to insert multiple rows. (If we want to insert an empty table, we can use the constructor with no values.) To insert a city, Houston, for examle, with the list of temperatures 98, 97, and 95, we can use the following statement: SQL> INSERT INTO DAILY_HIGHS 2 VALUES('Houston', 'TX', 'USA', 3 NUMBER_TABLE(98,97, 95)); 1 row created. In general, we can address a nested table like we would any other table by using a TABLE subquery. The subquery must have a WHERE clause that restricts the selection to the single row containing the table we want to update. This is the general format of a TABLE subquery: TABLE(SELECT nested_table_name FROM table_name WHERE single_row_condition) To continue inserting rows into a table that's already been created using a table constructor, we can use the familiar INSERT statement, replacing the table name with a table subquery: INSERT INTO TABLE(SELECT nested_table_name FROM table_name WHERE single_row_condition) VALUES (value); We can insert a new row into our DAILY_HIGHS table as follows: SQL> INSERT INTO 2 TABLE(SELECT TEMPS 3 FROM DAILY_HIGHS 4 WHERE CITY='Houston') 5 VALUES(90); 1 row created. To drop a nested table, we don't use a DROP statement. We instead simply set the nested table entry to NULL. SQL> UPDATE DAILY_HIGHS SET TEMPS=NULL 2 WHERE CITY='Houston'; 1 row updated. If we try to insert into this row's table now, we get an error. SQL> INSERT INTO 2 TABLE(SELECT TEMPS 3 FROM DAILY_HIGHS 4 WHERE CITY='Houston') 5 VALUES(90); INSERT INTO * ERROR at line 1: ORA-22908: reference to NULL table value We can add a table to an existing row using an UPDATE statement with the table constructor. Once again, we can add as many rows as we like at the same time. The following statement adds an empty table: SQL> UPDATE DAILY_HIGHS SET TEMPS=NUMBER_TABLE() 2 WHERE CITY='Houston'; 1 row updated. The nested table in the preceding examples has been a single column of NUMBER. The other option we can use for a nested table is an object type. Let's continue with a similar example but extend it to include highs, lows, and dates for each city. First we need to create the object type. SQL> CREATE TYPE TEMP_RECORD_TYPE AS OBJECT ( 2 DAY DATE, 3 HIGH NUMBER, 4 LOW NUMBER 5 ) 6 / Type created. Next we create a table type of this object type. SQL> CREATE TYPE TEMP_RECORD_TABLE AS 2 TABLE OF TEMP_RECORD_TYPE 3 / Type created. Now we can create a table that includes this nested table of objects. SQL> CREATE TABLE DAILY_TEMPS( 2 CITY VARCHAR2(32), 3 STATE VARCHAR2(32), 4 COUNTRY VARCHAR(32), 5 TEMPS TEMP_RECORD_TABLE 6 ) 7 NESTED TABLE TEMPS STORE AS DAILY_TEMPS_TEMPS_STORAGE; Table created. To insert dates, highs, and lows into this table, we need to use the table constructor, of course. But because the nested table contains objects, we also need to use the object constructor. Once again, we can insert multiple rows at once with the table constructor; this time using multiple object constructors separated by commas, as follows: SQL> INSERT INTO DAILY_TEMPS 2 VALUES('Reno', 'NV', 'USA', 3 TEMP_RECORD_TABLE( 4 TEMP_RECORD_TYPE('18-APR-2002',61,31), 5 TEMP_RECORD_TYPE('29-APR-2002',53,36), 6 TEMP_RECORD_TYPE('30-APR-2002',51,30) 7 ) 8 ); 1 row created. Let's next take a brief look at querying tables. We'll insert some more data to make things more interesting. SQL> INSERT INTO DAILY_TEMPS 2 VALUES('Anchorage', 'AK', 'USA', 3 TEMP_RECORD_TABLE( 4 TEMP_RECORD_TYPE('18-APR-2002',46,35), 5 TEMP_RECORD_TYPE('29-APR-2002',50,39), 6 TEMP_RECORD_TYPE('30-APR-2002',51,39) 7 ) 8 ); 1 row created. If we use a simple SELECT on a nested table, we get the nested information identified by type within parentheses, like this:
Using SQL*Plus, this is hard to read. In a programming environment, such as Java with JDBC, this is tedious to process. We could use nested cursors to deal with it, but another alternative is to un-nest, or flatten, the table, using a TABLE expression in the query. What we will do, in effect, is perform a join of the outer table with the inner table. SQL> SELECT CITY, T.* 2 FROM DAILY_TEMPS D, TABLE(D.TEMPS) T; CITY DAY HIGH LOW -------------------------------- --------- ---------- ----- Reno 18-APR-02 61 31 Reno 29-APR-02 53 36 Reno 30-APR-02 51 30 Anchorage 18-APR-02 46 35 Anchorage 29-APR-02 50 39 Anchorage 30-APR-02 51 39 6 rows selected. Notice that we assign an alias D to the outer table and that we use this alias in the TABLE expression to select the nested table. Then we assign an alias T to the nested table to use in our select list. As a final example, we add a WHERE clause to this query. SQL> SELECT CITY, T.* 2 FROM DAILY_TEMPS D, TABLE(D.TEMPS) T 3 WHERE T.LOW <= 32; CITY DAY HIGH LOW -------------------------------- --------------- ------ --- Reno 18-APR-02 61 31 Reno 30-APR-02 51 30 This by no means exhausts the options and possibilities of using nested tables, but we hope it's enough for you to understand them, evaluate them, and possibly start using them. VARRAYSVARRAYS are much like nested tables, but rather than being of unlimited size, we must specify their size when we declare them like an array in Java. The advantage that they have over nested tables is that they store collections much more efficiently. The disadvantage is that VARRAYs can be manipulated only as a whole. They are opaque to Oracle it doesn't know anything about what's inside them. It can't query them or change them. In order to manipulate individual elements, we must retrieve the VARRAY into an array type in a procedural language, such as Java, where we can address and alter individual elements, using indexes. When we are through updating the array, we must insert it back into the database as a single chunk of data. The basic format for declaring a VARRAY is CREATE TYPE type_name AS VARRAY(n) OF {data_type|object_type) / Instead of creating a TRIANGLE_TYPE with three separate points, for example, we could have used a VARRAY(3) of POINT_TYPE. SQL> CREATE TYPE TRIANGLE_VARRAY AS VARRAY(3) OF POINT_TYPE 2 / Type created. To include methods, however, we would then need to create an object type that uses this VARRAY. CREATE TYPE VTRIANGLE_TYPE AS OBJECT ( POINTS TRIANGLE_VARRAY, STATIC FUNCTION GETAREA(LAB in VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA NAME 'Triangle.getArea(java.lang.String)return double' ) / We would next need to update our Java Triangle class to use VARRAY instead of the individual points, create a new table, etc., as we did before. Let's take another example. Suppose we have a Web server application and we want to track the last 100 visitors to each page on our site. One way to do this is to use a circular buffer. Conceptually, a circular buffer is like a list, but the end of the list loops back around to the beginning. We can implement this with an array by using two pointers, one to tell us what the first entry in the buffer is and one to tell us what the last (most recent) entry is. Eventually, when the list fills up, the pointer to the last entry wraps back to the beginning, and from then on, the two pointers advance together. First we'll create the VARRAY to use as the buffer for each Web page. SQL> CREATE TYPE VISITOR_VARRAY AS VARRAY(100) OF VARCHAR2(25) 2 / Type created. Now we create the table with the page name, the starting index, the index of the end of the buffer, and the VARRAY for each page. This step is a little simpler than including nested tables because by default, Oracle takes care of deciding how to store the VARRAY. CREATE TABLE VISITORS ( PAGE_NAME VARCHAR2(25), START_INDEX NUMBER, END_INDEX NUMBER, USERNAME VISITOR_VARRAY ); Table created. We'll initialize a page with two sample entries. INSERT INTO VISITORS VALUES('INDEX.HTML',0,1, VISITOR_VARRAY('TestUser1','TestUser2')); Querying tables with VARRAYs is similar to querying tables with nested tables. SQL> SELECT PAGE_NAME, U.* 2 FROM VISITORS V, TABLE(V.USERNAME) U; PAGE_NAME COLUMN_VALUE ------------------------- ------------------------- INDEX.HTML TestUser1 INDEX.HTML TestUser2 Proceeding further requires a procedural language, but in summary, this is what we will need to do to add a username to this list:
In addition to the brief steps outlined above, our code will, of course, need to deal with two special cases, wrapping the indexes to 0 when they get to the end of the array and advancing the start index when the end index catches up to it. |
36), TEMP_RECORD_TYPE('30-APR-02', 51, 30)) Anchorage TEMP_RECORD_TABLE(TEMP_RECORD_TYPE('18-APR-02', 46, 35), TEMP_RECORD_TYPE('29-APR-02', 50, 
EAN: 2147483647
Pages: 71