Inserting Data
Inserting one entry into the VARRAY
In order to insert into the GAS_LOG table and provide values
for the array type you must supply the gas log object type (GAS_LOG_TY) to
reference and provide values for the GAS_LOG column. Following are two examples
that insert 1 row and 1 set of values for the GAS_LOG column. Note also, that
these are two distinct VINs and are two distinct rows in our table.
SQL> insert into gas_log values (101010101010101,gas_log_va(gas_log_ty(32,sysdate-1,'Shell')));
1 row created.
SQL> insert into gas_log values (222222222222222,gas_log_va(gas_log_ty(27,sysdate-1,'Texaco')));
1 row created.
Inserting multiple entries into the VARRAY
Now that we have mastered the single set of values for the VARRAY,
we can move on to the more difficult coding of inserting multiple entries into
the VARRAY. Following is a single SQL statement that will add one row to the
GAS_LOG table. This row will have associated with it six different gas log
entries that populate the VARRAY object. In addition, to note that in order to
add a value within our GAS_LOG VARRAY object we would have to repeat this full
SQL statement while adding our seventh entry to the end of it.
SQL> insert into gas_log values (321321321321321,gas_log_va(
gas_log_ty(45,sysdate-10,'Diamond Shamrock'),
gas_log_ty(31,sysdate-9,'Shell'),
gas_log_ty(32,sysdate-8,'Shell'),
gas_log_ty(33,sysdate-7,'Texaco'),
gas_log_ty(34,sysdate-6,'Texaco'),
gas_log_ty(35,sysdate-5,'Diamond Shamrock')));
1 row created.
Selecting the data
If you wanted to issue a select against the GAS_LOG table,
you would get the results that follow. This is a very raw and crude format to
try to read but gives you an idea of how the data is stored.
SQL> col gas_log for a50
SQL> select * from gas_log;
VIN GAS_LOG(GALLONS, FILLUP_DATE, GAS_STATION)
----------------- --------------------------------------------------
101010101010101 GAS_LOG_VA(GAS_LOG_TY(32, '19-FEB-04', 'Shell'))
222222222222222 GAS_LOG_VA(GAS_LOG_TY(27, '19-FEB-04', 'Texaco'))
321321321321321 GAS_LOG_VA(GAS_LOG_TY(45, '10-FEB-04', 'Diamond Sh
amrock'), GAS_LOG_TY(31, '11-FEB-04', 'Shell'), GA
S_LOG_TY(32, '12-FEB-04', 'Shell'), GAS_LOG_TY(33,
'13-FEB-04', 'Texaco'), GAS_LOG_TY(34, '14-FEB-04
', 'Texaco'), GAS_LOG_TY(35, '15-FEB-04', 'Diamond
Shamrock'))
For a much
prettier formatted SQL code and output so that we can view the data much in the
way we are used to, you must issue SQL in the following form. The TABLE
function is used as the target of the GAS_LOG column VARRAY and is given the
alias of var. Please note that although we have eight rows returned for the SQL
query, we in fact only retrieved three rows from the GAS_LOG table.
SQL> col vin for 9999999999999999
SQL> col gas_station for a40
SQL> set linesize 132
SQL> select a.vin,var.* from gas_log a, table(gas_log) var;
VIN GALLONS FILLUP_DA GAS_STATION
----------------- ---------- --------- ----------------------------
101010101010101 32 19-FEB-04 Shell
222222222222222 27 19-FEB-04 Texaco
321321321321321 45 10-FEB-04 Diamond Shamrock
321321321321321 31 11-FEB-04 Shell
321321321321321 32 12-FEB-04 Shell
321321321321321 33 13-FEB-04 Texaco
321321321321321 34 14-FEB-04 Texaco
321321321321321 35 15-FEB-04 Diamond Shamrock
8 rows selected.
As an
alternative SQL to the previous for selecting the rows from our GAS_LOG table,
I would just like to show the following to emphasize what is really happening
and how we can actually reference all the columns in the GAS_LOG column VARRAY.
SQL> select a.vin,var.gallons,var.fillup_date,var.gas_station
2 from gas_log a, table(gas_log) var;
VIN GALLONS FILLUP_DA GAS_STATION
----------------- ---------- --------- -------------------
101010101010101 32 19-FEB-04 Shell
222222222222222 27 19-FEB-04 Texaco
321321321321321 45 10-FEB-04 Diamond Shamrock
321321321321321 31 11-FEB-04 Shell
321321321321321 32 12-FEB-04 Shell
321321321321321 33 13-FEB-04 Texaco
321321321321321 34 14-FEB-04 Texaco
321321321321321 35 15-FEB-04 Diamond Shamrock
8 rows selected.
While not everyone will agree that the creation and use of
VARRAYs within a table is the best thing to do, I do believe they have a place
in the architecture under certain circumstances. Please follow along for the
next two entries in this series and hopefully I can change your mind as we
discover how to use them and where the benefits can be found. In the meantime,
please create these objects and play with them a bit to get familiar with them.
Next time I will take you through the creation of a few more objects and code
to make your life a bit easier while having to manipulate these abstracted
objects.
»
See All Articles by Columnist James Koopmann
» 
Comment and Contribute
