Solved

Querying VARRAY and Nested Tables

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle report printing 2 pages in one page 2 68
How to connect SQL Server from my Oracle database? 11 96
grouping on time windows 6 51
MULTIPLE DATE QUERY 15 77
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

776 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