Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Querying VARRAY and Nested Tables

Posted on 2004-04-29
3
Medium Priority
?
1,690 Views
Last Modified: 2012-06-27
As i know that VARRAY is in order , have specific maxno. of elements and it can't be query.
and Nested Table are not in order but can be query like relational database.

For exmaple: Phone number do not need to query so i can use create it as VARRAY, coz i am retrieving collection as a whole. But if i use VARRAY in case like varray contains max 5 elements may be name of five person (Peter, John........., Thomas)

CREATE  TYPE Name_vartype AS VARRAY(5) OF VARCHAR2(15);

CREATE TYPE Customer AS OBJECT (
cust_no
name Name_vartype );

***In this case can i query the name of person like in relational database:
for eg: Select.................... from customer
           where name ='John';
is it good idea to use varray in this case or should i use Nested Table?

If i am using nested table or Varray how can i query the cust_no along with name in both VARRAY and NEsted Tbale Case.

Regards!
0
Comment
Question by:jay4
3 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 10947442
It is a bad idea to use either, IMO.  Just create a separate table in the normal, relational way.  Then you won't have to deal with the complicated SQL syntax required to work with collections, and your database will be more useful.

Collections are useful in PL/SQL code.  In database tables, they are an irrelevant pain in the neck.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 210 total points
ID: 10949306
@jay4----->As i know that VARRAY is in order , have specific maxno. of elements and it can't be query.

I wonder where you got that from. Varray collection type can definitely be queried. it's queried the same way as nested tables,


In your case, it's true that VARRAY is more appropriate.

the whole argument of using Object feature Vs. relational feature is of much detate and personal preference. But I am not too crazy about it either from Developer's point of view, but as a DBA, I like it.

so your pick.
0
 

Author Comment

by:jay4
ID: 10958415
So in above case i can query like i query for Nested Tables rite?

For example i need cust_no and name for select person't name ('john')

it will be stored as (Thomas, John, David, Matt, Jack) as a whole.

so while querying particular person how can i do?

From my Above 2 types.

Regards!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

824 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