Solved

Querying VARRAY and Nested Tables

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

821 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