Solved

VARRAY & NESTED TABLE USE

Posted on 2004-04-22
2
1,718 Views
Last Modified: 2008-02-01
I hv some clue why we use varary and nested tables and even their functionality.

In my case i hv object like:

CUSTOMER_TYPE (type)
cust_no
cust_name
cust_address address_type
cust_phone phone_var

ADDRESS_TYPE (type)
street,
state,
zip
....

Creating varray for phone as phone_var

Customer table contains address_type object and varray type phone_var
now problem is that how can i get address and phone for selected customer from varray and address object.
can i use simple query or its diff in object relational?

And i like to know more about Nested table on which case it is useable and i hv to use, how can i implement it.

Regards!

0
Comment
Question by:jay4
2 Comments
 

Expert Comment

by:expertsCTS
ID: 10899000
Nested Tables are collections.
for example, say few students in a computer center are enrolling for multiple courses at a time. but they will be finishing off one course first and then only they will be starting the other course.
In this case, nested tables are useful. once the user queries the database , the output will be like this .
Name               DOJ                COURSES enrolled.
---------------------------------------------------------
Gomathi           12-09-2004       Oracle/VB
                                              ASp/Sqlserver
                                              Asp.net/C#    


This is not how you will get the display in oracle. This is just to explain you that 1 row with multiple information is the use of nested tables.
 
Does this answer you?
0
 
LVL 2

Accepted Solution

by:
n4nazim earned 60 total points
ID: 10906419

Hi,

Here is a example of working of VARRAY's. This shows how to create, insert, update, delete records with good examples.


WORKING WITH VARRAYS IN ORACLE

This article is the first in a three part series that will take you through the process of creating VARRAYs and dealing with them in Oracle tables.

If you were a strict normalization geek, you would definitely venture down the track we are going to take. However, if you adhere to object technology you will more than likely enjoy this article. This article is going to introduce you to the creation of an abstract data type, or object that can be used within the typical table structure within Oracle. The caveat of this is that we are also going to introduce an array in our table that will make you cringe because we are introducing a repeating field, or object, within the table that goes against all normalization practices. Before you throw this article out or stop reading it, there are a few good reasons why you may want to consider putting this technique in your bag of tricks.

By in-lining the repeated fields (object) in the table, you remove the reliance on creating another table with its own structure and indexes to worry about.
You do not have to join to another table just to get a set of related fields since they are stored in the table already.
The abstracted data type (object) can be reused by other tables or objects that forces designers to adhere to a standard for the columns in the abstracted object.
An Abstract Data Type
As stated briefly, an abstracted data type is an object type that groups common columns together. In our example, we have an object that is designed to hold a gas log for a fleet system. The gas log is made up of the number of gallons of gas we have pumped, the date we filled up, and the particular gas station used. Obviously, we could add things but this is a simple case and I hope that you get the picture presented here. We will use this object when defining and relating a gas log to a particular vehicle later on. The interesting point to note here is that this object could be used by another table. For example, what if our fleet of vehicles were also used to fill the tanks at our local gas stations. We could then also use this object and add it to a table called STATION_FILL_SCHEDULE to distinguish the number of gallons deposited, when the tanks were filled, and the gas station. This is of great use since we gain reusability and commonality of field definitions. To create this new object you would issue the following DDL:

CREATE TYPE GAS_LOG_TY AS OBJECT (
            GALLONS       NUMBER,
            FILLUP_DATE   DATE,
            GAS_STATION   VARCHAR2(255));
The Array
The GAS_LOG_TY object previously created will easily stand on its own and can be added to a table. However, we want to track the last 100 times a vehicle was filled with gas in our fleet system. We do this be providing an array structure that will hold 100 of the gas log object. Here is the DDL to accomplish that.

CREATE TYPE GAS_LOG_VA AS VARRAY(100) OF GAS_LOG_TY;
The Table
Now that we have the gas log object created and the array of gas log, all we need to do is issue the DDL that you and I are already familiar with. To note, we are tracking by the vehicle ID number. Also note that the column GAS_LOG has a column type of the array we just defined GAS_LOG_VA.

CREATE TABLE GAS_LOG
       (VIN        NUMBER NOT NULL,
        GAS_LOG    GAS_LOG_VA);
Describing the structure
If you are familiar with the SQL*Plus DESCRIBE command, you can get the full description of this newly created table with little more effort than before. Following is the sequence as depicted in Listing 1.

First describe the table GAS_LOG and you will note the column type GAS_LOG_VA
You can then describe the column type GAS_LOG_VA to show that it is in fact a VARRAY of 100 entries of the GAS_LOG_TY object.
Describing the GAS_LOG_TY object does not provide any further information in this example but is practice just to get the full picture.
Listing 1.
Describing the abstracted objects

SQL> DESCRIBE gas_log
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 VIN                                       NOT NULL NUMBER
 GAS_LOG                                            GAS_LOG_VA
 
SQL> DESCRIBE GAS_LOG_VA
 GAS_LOG_VA VARRAY(100) OF GAS_LOG_TY
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 GALLONS                                            NUMBER
 FILLUP_DATE                                        DATE
 GAS_STATION                                        VARCHAR2(255)
 
SQL> DESCRIBE GAS_LOG_TY
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 GALLONS                                            NUMBER
 FILLUP_DATE                                        DATE
 GAS_STATION                                        VARCHAR2(255)
How to Drop the Structure
In order to drop the newly created structure, you should begin dropping in the reverse order from which you created them. If you try and drop the underlying object types you will get an ORA-02303 which tells you that you cannot drop or replace a type with type or table dependents. Here is the proper sequence to drop the above structures.

DROP TABLE GAS_LOG;
DROP TYPE GAS_LOG_VA;
DROP TYPE GAS_LOG_TY;
 

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.

Rgds,
Nazim M

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now