Working with VARRAYs in Oracle - Part II
March 12, 2004
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 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.
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
SQL> exec gas_log_insert(101010101010101,22,sysdate,'Sinclare');