Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 4, 2004

Working with VARRAYs in Oracle Part I - Page 2

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM