Solved

Querying VARRAY and Nested Tables

Posted on 2004-04-29
3
1,675 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 70 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

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

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

12 Experts available now in Live!

Get 1:1 Help Now