Working with VARRAYs in Oracle - Part II - Page 2
March 12, 2004
Making Selecting the data Easier
If you look at Part I of this series, you surly would have noticed the difficulty of selecting data from the VARRAY. If you were to select straight from the table, the VARRAY information displays as a wrapped set of information that is very unreadable. If you issued the SELECT statement that took advantage of the TABLE function, you surly were able to get the data out in a more readable form but the ability to code this function in normal SQL statements could cause problems for more junior developers. What we would really like to do is just issue a normal SQL SELECT statement with which everyone is familiar. In order to do this we must use a table function to produce a result set for a simple select.
Another Abstract Object
Here we create our own object type called GAS_LOG2_TY. Then we create a table of GAS_LOG2_TY called GAS_LOG_TBL_TY. The table GAS_LOG_TBL_TY is what we will use to return the rows from the table function within a simple select statement.
CREATE TYPE GAS_LOG2_TY AS OBJECT (VIN NUMBER, GALLONS NUMBER, FILLUP_DATE DATE, GAS_STATION VARCHAR2(255)); / CREATE TYPE GAS_LOG_TBL_TY AS TABLE OF GAS_LOG2_TY; /
I have created a set of PL/SQL statements in Figure 2. There is nothing particularly interesting here but please just notice that the SQL statement we used in Part I of this series is now within the function.
To simplify the SQL to select through this function we need to put a view on top of it. Figure 3 shows the view and notice the TABLE function call to the GAS_LOG_FN function.
Now we can simply select from the GAS_LOG_VW as shown here.
SQL> select * from gas_log_vw where vin = 101010101010101; VIN GALLONS FILLUP_DA GAS_STATION ----------------- ---------- --------- ------------ 101010101010101 32 19-FEB-04 Shell 101010101010101 22 20-FEB-04 Sinclare 101010101010101 55 20-FEB-04 Texaco
Simplified INSERT as Promised
We now have most of the pieces to perform a simple SQL INSERT statement that looks like what most of us are used to. The last issue is that a table function cannot be the target of a DML operation such as INSERT, UPDATE or DELETE. To get around this we will create an INSTEAD OF INSERT trigger for the previous GAS_LOG_VW view we created. Figure 4 shows the DDL for this trigger.
Now we can issue the following INSERT statement that most of us are familiar with to add values to the VARRAY within our table.
SQL> INSERT INTO gas_log_vw VALUES (101010101010101,55,sysdate,'Texaco');
This article gives you all the tools and methods of abstracting the complexity of working with VARRAYs within a table. You can now perform all types of DML operations in your normal fashion. The smiles you will have on developers' and end users' faces will far outweigh the extra lines of code you have just produced. Remember that it is your task in life to take advantage of the new features that Oracle has to offer but at the same time make them simple enough that anyone can use them or in this case don't even know they are. Next time we will look at the performance implications of using VARRAYs. Stay tuned.