Working with VARRAYs in Oracle - Part II - Page 2

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;
/

The Function

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.

Figure 2.
Function to pipe results through abstract type

CREATE OR REPLACE FUNCTION GAS_LOG_FN
         RETURN GAS_LOG_TBL_TY PIPELINED IS
         PRAGMA AUTONOMOUS_TRANSACTION; 
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
out_rec      gas_log2_ty 
          := gas_log2_ty(NULL,NULL,NULL,NULL);
BEGIN
OPEN cur0 FOR 
'select a.vin,var.gallons,var.fillup_date,var.gas_station '||
'  from gas_log a, table(gas_log) var ';
LOOP
  FETCH cur0 INTO out_rec.vin, out_rec.gallons, out_rec.fillup_date, out_rec.gas_station;
  EXIT WHEN cur0%NOTFOUND;
  PIPE ROW(out_rec);
END LOOP;
CLOSE cur0;
RETURN;
END GAS_LOG_FN;
/

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.

Figure 3
View for Table Function

CREATE OR REPLACE VIEW GAS_LOG_VW AS 
  SELECT a.vin, a.gallons, a.fillup_date, a.gas_station
  FROM TABLE(GAS_LOG_FN) a
/

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.

Figure 4.
INSTEAD OF TRIGGER for Inserts

CREATE OR REPLACE TRIGGER GAS_LOG_TRIGGER
INSTEAD OF INSERT ON GAS_LOG_VW
FOR EACH ROW
BEGIN
gas_log_insert(:new.vin,:new.gallons,:new.fillup_date,:new.gas_station);
END;
/

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.

» See All Articles by Columnist James Koopmann