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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 12, 2004

Working with VARRAYs in Oracle - Part II

By James Koopmann

This article is the second in a three part series that will take you past the process of creating VARRAYs and will show you how to abstract the details and complexity from your end users.

In Part I of this series, we saw how to create an abstract data type and define an array within a table. The mechanisms for inserting data into this structure and selecting data from this structure was not too difficult but did require some thought processes and will no doubt look a bit confusing to the normal person who is familiar with the normal structures of plain tables within Oracle and simple DML. This article will take you through the steps on how to abstract the difficulty from using VARRAYs so that developers or end uses can interact with these structures through familiar table insert and select statements.

Inserting Data

Inserting one entry into the VARRAY

As seen in Part I of this series, 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. Here is the simple version from Part I on how to insert a value in the VARRAY. If you look back at the first article, you will see this gets much more confusing as we want to add multiple values to the VARRAY.

SQL> insert into gas_log values (101010101010101,gas_log_va(gas_log_ty(32,sysdate-1,'Shell')));
1 row created.

Making Inserts Easier

Since you cannot reference an element of a VARRAY individually but need to reference the whole VARRAY this makes adding one value to the VARRAY somewhat difficult. In order to add a value to an element in the array, you must re-insert all values that are already in the VARRAY at the same time with the new value. Since you do not want to put developers at the mercy of remembering this, your next best alternative is to abstract the insert of a single value to make it look like you are only adding a row in a normal table. We can do this by designing a procedure to handle this manipulation of the full VARRAY when we want to insert a new value in the array. Figure 1 gives the code to handle the abstraction.

Figure 1.
Dynamically Method of Inserting into VARARY

( in_vin           IN NUMBER,
  in_gallons       IN NUMBER,
  in_fillup_date   IN DATE,
  in_gas_station   IN VARCHAR2) AS
pr_gas_log_va     gas_log_va := gas_log_va();
'SELECT gas_log FROM gas_log WHERE vin = :1 FOR UPDATE OF gas_log' 
   INTO pr_gas_log_va USING in_vin;
pr_gas_log_va(pr_gas_log_va.LAST) := gas_log_ty(in_gallons,in_fillup_date,in_gas_station);
'UPDATE gas_log SET gas_log = :1 WHERE vin = :2' 
  USING pr_gas_log_va, in_vin;
  'INSERT INTO gas_log 
   VALUES (:1,gas_log_va(gas_log_ty(:2,:3,:4)))' 
    USING in_vin,in_gallons,in_fillup_date,in_gas_station;
END gas_log_insert;

Now if we would like to insert a row into our VARRAY all we need to do is issue the following simplified statement. This is the basic form of executing a procedure within Oracle and most developers are use to this. While this is still not a simple INSERT statement, it is much closer. By the end of this article, we will be doing simple INSERT statements that anyone will be familiar with. However, this procedure is a critical step in getting us to the more simplified mechanism and in fact, it can be used by most any developer in code.

Inserting a row through the gas_log_insert procedure

exec gas_log_insert(101010101010101,22,sysdate,'Sinclare');

Oracle Archives

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