Solved

Querying VARRAY and Nested Tables

Posted on 2004-04-29
3
1,685 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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