Solved

nested tables & varying arrays

Posted on 2000-02-14
20
1,181 Views
Last Modified: 2012-05-05
dear experts
          Pl. let me know more about(with syntax and examples) nested tables and varying arrays in oracle8 for storing multiple values in a single row in a table ?

thanks in advance.

sreekanth
0
Comment
Question by:sreek3
  • 9
  • 7
  • 2
  • +2
20 Comments
 
LVL 6

Expert Comment

by:crsankar
ID: 2521926
Oracle documentation set has very good examples for nested tables and varrays. Reffer to Sql reference guide and application developers guide
0
 
LVL 4

Expert Comment

by:sganta
ID: 2522197
Hi,

We can create nested tables in Oracle 8i.

Eg:

CREATE TYPE employee AS OBJECT ( empno NUMBER(4)
                                                                       ,ename VARCHAR2(20));

CREATE TABLE emptbl OF employee;

INSERT INTO emptbl(1245,'JOHN');

CREATE TYPE project AS OBJECT
( pname VARCHAR2(20)
 ,empref REF employee));

CREATE TABLE depttbl ( deptno NUMBER, proj project);


INSERT INTO depttbl VALUES
(10, project ('PROJECT_01',( SELECT REF(p)
                                                    FROM emptbl p
                                                    WHERE ename = 'JOHN')));

Hope these things will helps you !
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2524416
sganta,

Objects Option was available from oracle8.

The example that you gave is not for Nested Table. Actually you are creating Object Tables ( which are based on a single object type). Where are nested tables, should be seperate from the main table.

sreek3, I'll post more tonight.

Regards,
Sudhi.
0
 

Author Comment

by:sreek3
ID: 2525634
NESTED IN THE SENCE HAVING MULTIPLE VALUES FOR A SINGLE COLUMN IN A SINGLE ROW - I NEED DETAILS ABOUT THIS.
 REGARDS
SREEKANTH
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2525661
sreek3, if i'm right you are looking for Collections ( which will give you that effect ). Using this for a single row you "collect" some values and access it by using the table's column.

From Oracle8, Oracle offers 2 types of collections for tables.
1. Netsted Tables
2. Varrying Arrays ( VARRAYS )

Now the difference b/w these 2 is :
Netsted tables allows you to collect as many values you want for a row, but VRRAYS has a upper limit to the number of collected values ( defined by you at the time of creating it ).

If this is what you want, post a comment.

Regards,
Sudhi.
0
 

Author Comment

by:sreek3
ID: 2525695
HI SUDHI
   I AM LOOKING FOR IT . PL. PROVIDE ME MORE DETAILS WITH SYNTAX AND EXAMPLES..

SREEKANTH.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2525704
boy, i'm dying out here with my deadlines man. If its ok with you, can you wait for some more days?

If its ok, meanwhile somebody else might be able to post an example for you. If its still not given, then i'll give it to you. ok.

Regards,
Sudhi.
0
 
LVL 4

Expert Comment

by:sganta
ID: 2525785
Hi Sudhi,

Will it not help my last table "depttbl" table for nested tables.

In that table you can have multiple employees values  for each deptno.

0
 

Author Comment

by:sreek3
ID: 2525819
hi sqanta
   I am sorry to say that u have not understood my question properly.. My aim is to store multiple values for a single column in a single row.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2526041
Hi ganta,

In the examples that you have provided, one is for Object Tables and the other one is for column objects.

Now the entire table could be based on a UDT(user defined type) or one of the column could be based on a UDT.

When you base the entire table on the UDT (your first eg) the rows will be "row objects". Apart from having a rowid, the "row objects" also will get a object id. Note that object id is not same as REF value generated by the function. REF values are generated by taking the oids. you can query the object table ( first example) like:

SQL> Select sys_nc_oid$ from emptbl;

Also an object table could be treated as a relation table too. Thats why your insert statement works. But in a column object you have to explicitly make a call to constructor's method of the UDT.


column object example:
1. create a UDT
sql> Create or replace type address_ty as object(
city   varchar2(20),
state  varchar2(20))
/

2. Create a table using this udt for one of the columns.

sql> Create table customer(
custid   number(4),
custname varchar2(20),
custaddress address_ty);

3. insert records into this table.

sql> insert into customer values (1,'A', address_ty('San Diego','CA'));

Row object example:

1. Create the UDT
sql> Create or replace type cust_ty as object(
custid number(4),
custname varchar2(20));
/

2. Create a table using this UDT

sql> Create table customer of cust_ty;

3. Insert into records into this object table either by treating like a relational one or by treating it like a object table.

sql> insert into customer values (1,'A'); -- relational way

sql> insert into customer values ( cust_ty('1','a')); -- object way.

4. To see the oid of these object rows

sql> select sys_nc_oid$ from customer;


Collections:
-----------
Customers table will store customers info and we'll have a column called orders in it which will store the orderids and ord_date.

1. Create the UDT

sql> Create or replace type order_ty as object(
ord_id number(2),
ord_date date);
/

2. Next, define the "table" type of order_ty which stores orders objects.

sql> create type ord_list as table of ord_ty;

3. Create the table customers which has a column of "ord_list"

sql> create table customers(
cust_id number(4),
cust_name varchar2(20),
orders  ord_list)
nested table orders store as orders_tab;

note: the nested table has to be named, in this case it is named as "orders_tab"

4. insert a customer who has got 2 orders.

sql> insert into customers values (1,'A',
ord_list(ord_ty(10,'13-jan-76'),
         ord_ty(11,'13-jan-76')));

0
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.

 
LVL 4

Expert Comment

by:sudhi022299
ID: 2526055
in the comment above, there is no limit on the number of orders we have for a particular customer. But if the column "orders" of the customers table, has beend defined using VARRAY then there would be a limit on it.
That is one of the limitations of using VARRAYS for collecting values. Lets say you have students table and books_issued is collection of VARRAY. You can limit the number of books issued to the student say only 3.

if you have followed my crude example then i'll post more on varrays.

( got to get some sleep now man...  :-)

Regards,
Sudhi.
0
 

Author Comment

by:sreek3
ID: 2526206
dear sudhi
        LOT & LOTS OF THANKS FOR PROVIDING APT ANSWER. IF YOU FIND TIME POST ME ABOUT VARRYS..

 SO KIND OF U

 SREEKANTH.
0
 

Expert Comment

by:surendranath
ID: 2532353
NESTED TABLE EXAMPLE
==============
sql> create or replace type phone as object
     (off number,
     res number,
     pers number,
     cell number,
     mail varchar2(15),
     fax varchar2(10));
     /


------------------------------------------------------
/* creating a user defined data type of name */

sql> create or replace type name as object
     (fname varchar2(10),
     mname varchar2(10),
     lname varchar2(10));
     /


-------------------------------------------------------
/* creating a table using user defined data types */

sql>  create table emp (empno number(4) primary key,
      ename name,
      addr varchar2(10),
      tele phone,
      job varchar2(10),
      doj date default sysdate,
      sal number(9,2),
      remarks varchar2(10));


--------------------------------------
/* to insert rows in a table which holds user defined data types */

sql> insert into emp values(1000,
                       name('SURYA,'PRAKASH','haha'),
                   '1-2-247',
                   phone(7612214,7013254,69365,null,null,null),
                   'programmer',sysdate,12000,'good');

sql> insert into emp values(1001,
                       name('SUREN','NATH','REDDY'),
'4-2-247',
                   phone(7612214,7660197,621344,null,'deep@lat.com',null),'programmer',sysdate,5000,'good');

------------------------------------
/* to select a specific field value from a user defined data type */

sql> select e.empno,e.ename.mname,e.tele.off,e.tele.pers
     from emp e
     where empno = 1000;

sql> select * from emp e
     where e.ename.mname like 'S%';
---------------------------------------
/* to update a specific field value of use defined datatype */

sql> update emp e
     set e.ename.lname = 'HELLO'
     where e.ename.mname like  'S%';

sql> update emp e
     set e.ename.fname = 'SQL'
     where empno = 1000;

sql> update emp e
     set e.ename.lname = null
     where e.ename.mname = 'Deepak';
---------------------------------------
VARRAYS
=======

sql> create or replace type course as varray(8) of varchar2(4);
     /

SQL> desc course
 course VARRAY(8) OF VARCHAR2(4)

sql> create table student(sid number(4) primary key,
     sname varchar2(10),
     address varchar2(14),
     phone number,
     doj date default sysdate,
     cdet course,
     rem varchar2(10));

SQL> desc student
 Name           Null?    Type
 -------------------- -------- ----
 SID           NOT NULL NUMBER(4)
 SNAME         VARCHAR2(10)
 ADDRESS       VARCHAR2(14)
 PHONE           NUMBER
 DOJ            DATE
 CDET          COURSE
 REM        VARCHAR2(10)
   
sql> insert into student values
     (1000,'srinivas','1-10/s',235332,sysdate,
     course('C100','C101','C104'),
     'good');

sql> insert into student values
     (1001,'kishan','1-10/s',235332,sysdate,
     course('C100','C102','C105'),
     'good');

sql> update student
     set cdet = course('C100','C101','C104','C105')
     where sid = 1000;



Hope the above examples will do
0
 

Author Comment

by:sreek3
ID: 2533652
Hi surendranath
          You have provided examples for
sql statements which uses USER DEFINED DATA TYPES. Nested tables are those which uses one table inside another one.(refer SUDI's comment on this same question.)

thanks
sreekanth.
0
 

Author Comment

by:sreek3
ID: 2537735
Hi sudhi
     sorry for troubling u again & again.  Ur examples about nested table helped me a lot.  The problem is that I NEED A SELECT St. WHICH WILL SELECT A PARTICULAR COLUMN INSIDE THE NESTED TABLE.
    For examples IN THE NESTED ORDER TABLE i would like to select the ord_id particularly. [eg. the select st. must reterive all customer numbers along with the dates(all dates for each customer) in which they have placed their orders]
     Pl. clear my doubt IF U FIND TIME.


SREEKANTH.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2537788
Sorry for not checking this.
Oracle supports selecting rows from the inner table ( nested table ) for a particular outer record ( Customer ).

you cannot say like this " select ord_id from orders_tab where cust_id = 1; "
you cannot reference it directly. To do this there is a new function called as "THE". Now you pass a query to this "the" function the collection by using the column name which is based on that collection. Using an alias you can just treat it as a table.

to find out the order dates for a particular customer the query would be:

sql> select a.ord_id from the(select orders where cust_id = 1) a;

This would give you all the order dates for that customer.
You can use the "the" function in insert, update, delete basically dml statements to selectively work on a collection for a particular customer.

On the weekend i'll post some more egs on VARRAYS.

Regards,
Sudhi.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2541372
hi sreek3,

Just found a example of all object related stuff at techent.oracle.com
go thru it.
http://technet.oracle.com/tech/pl_sql/index2.htm?Code&listing.htm

In case you don't have an account, create it( its free ).

Regards,
Sudhi.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2547588
did you go thru the documents ?
0
 

Author Comment

by:sreek3
ID: 2549844
Hi SUDHI
    Thanks a lot for u'r mail. I went through it and got the necessary information. sorry for delayed reply..

thanxs a lot gentleman.

sreekanth.
   
0
 
LVL 4

Accepted Solution

by:
sudhi022299 earned 20 total points
ID: 2550274
Your Welcome.

Regards,
Sudhi.
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.

708 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

13 Experts available now in Live!

Get 1:1 Help Now