Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Anitha.

Asked: October 09, 2001 - 4:02 pm UTC

Answered by: Tom Kyte - Last updated: January 08, 2014 - 6:04 pm UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Recommendations for unit testing PL/SQL programs

You Asked

HI Tom,
I wanted to use VARRAY in my tables . And when I query the table I want to make look all the columns like a flat table.

for ex:I am creating a varray to store emails -----
create or replace type emails_va as varray(2) of varchar2(30);

and then use it in my emp table -----

create table EMP
(
EMPID NUMBER not null,
EMPNAME VARCHAR2(20),
EMAILS EMAILS_VA
)

now I want to create a view on EMP which lists all the rows from EMP like a flat table .
may be some thing like
create view EMP_V (EMPID,EMPNAME,EMAIL1,EMAIL2)
as (SELECT EMPID,EMPNAME,?,? from EMP ).

I am stumbled at this point .
I appreciate any help on this or any other solution for this.

Thanks




and we said...

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp values ( 1, 'tom', emails_va( 'x', 'y' ) );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp values ( 2, 'lori', emails_va( 'a', 'b' ) );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select empid, empname, x.* from emp, table( emp.emails ) x
2 /

EMPID EMPNAME COLUMN_VALUE
---------- -------------------- ------------------------------
1 tom x
1 tom y
2 lori a
2 lori b




and you rated our response

  (31 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Excellent

October 09, 2001 - 6:28 pm UTC

Reviewer: A reader


VARRAY

October 10, 2001 - 8:46 am UTC

Reviewer: William from Ontario Canada

Exellent

But waht EMAILS_VA data type mean

October 10, 2001 - 10:00 am UTC

Reviewer: Mohammed Esmael from Yemen

yes, useful,but could you please explain to me the benfit of using EMAILS_VA data Type ,and what it is exactly.

Tom Kyte

Followup  

October 10, 2001 - 10:17 am UTC

It an an array, a collection, that can be stored as a single column with the table. Typically it would have more then 2 elements -- you would use it when you had say upto N values for something and did not want to have a column/attribute.

Thank You Tom

October 10, 2001 - 10:54 am UTC

Reviewer: Anitha from Ontario , Canada

Thanks for the reply ,Tom .
But I was looking, if we can display the information like :

EMPID EMPNAME email1 email2
---------- --------------------
1 tom x y
2 lori a b


Tom Kyte

Followup  

October 10, 2001 - 11:26 am UTC

You can but it won't preserve the ORDER of the array -- your array must be sortable:

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp values       ( 1, 'tom', emails_va( 'x', 'y' ) );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp values       ( 2, 'lori', emails_va( 'a', 'b' ) );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select empid, empname, 
                            max( decode( rn, 1, column_value, null ) ) email1,
  2                         max( decode( rn, 2, column_value, null ) ) email2
  3    from ( select empid, empname, x.*,
  4                  row_number() over ( partition by empid order by 
                                                             column_value ) rn
  5             from emp, table( emp.emails ) x
  6         )
  7  group by empid, empname
  8  /

     EMPID EMPNAME              EMAIL1                         EMAIL2
---------- -------------------- ------------------------------ ----------------
         1 tom                  x                              y
         2 lori                 a                              b

 

thank you

October 10, 2001 - 11:54 am UTC

Reviewer: Anitha from Ontario , Canada

thank you , Tom

Give the link for the documentation of this datatype-EMAILS_VA

October 10, 2001 - 4:15 pm UTC

Reviewer: A reader

EMAILS_VA

Tom Kyte

Followup  

October 10, 2001 - 6:36 pm UTC

read the question. Its a user defined type. He made it up. All of the documentation on the entire planet for EMAILS_VA is on this page.

What if we need to preserve order?

November 21, 2002 - 12:09 pm UTC

Reviewer: Benoit Hebert from Quebec City, Canada

It was a very helpful beginning of an answer for our problem. But what if we need to preserve the order of the varray, and the array itself is not sortable? We store numeric values in the array, and the position in the array is just as important as the data it contains. Is there a way of preserving the order in a select query like this?

Tom Kyte

Followup  

November 21, 2002 - 1:25 pm UTC

You would need a VARRAY or NESTED TABLE type of an object type, eg:

create or replace type myScalarType as object
( seqno number,
email_id varchar2(255)
)
/
create or replace type myArrayType as table of myScalarType


so you could order by seqno within the table to get your "order" back. Just like you would with ANYTHING in SQL. Unless you have something to order by -- don't expect anything to be ordered.

If you DON'T try to turn these into "columns" -- just leave them stuffed in the array in the first place -- order will be maintained (for varrays, not nested tables). simple select * from table would return the arrays in the order they went in.





OK

January 07, 2004 - 6:46 am UTC

Reviewer: Siva from Hyderabad,India

Dear sir,
well and wish the same from you.Could you please look into
the code and provide a solution as given below?

sql> set feedback off
sql> create type skills_va as varray(20) of varchar2(24);

sql> create table employee_skills(enm varchar2(30),skills skills_va);

sql> insert into employee_skills values('Jones',
skills_va('Java','C','C++','Oracle','Sybase','ASP','JSP'));

sql> insert into employee_skills values('James',
skills_va('Java','Oracle','Sybase','Proj_Mgmt'));

sql>insert into employee_skills values('Ward',
skills_va('Oracle','VB','VC++','COM','DCOM'));

sql>insert into employee_skills values('Turner',
skills_va('DW','PHP'));

My questions are
1)Can we select only the skills_va column so that it appears
in table format like
enm skills
--- ------
Jones Java
C
C++
..
for all the rows of employee_skills table?
2)I need a query which results in the output
enm count(skills) skills
---- ------------- -----
Jones 7 Java
C
c++
..
James 4 Java
Oracle
..
Ward 5 ..

Turner 2 ..
3)I need a query that shows the ename
i) with highest number of skills.
ii)with least number of skills.
Could you please help?
Thanks in advance.
Bye!

Tom Kyte

Followup  

January 07, 2004 - 8:22 am UTC

seems like you want to use normal relational tables to me!!!!!

really -- i've never used varray's or nested tables as a storage mechanism -- as a programming device in plsql, constantly.  as a physical storage device -- nope.



ops$tkyte@ORA9IR2> select enm, column_value
  2  from employee_skills E, table(e.skills);
 
ENM                            COLUMN_VALUE
------------------------------ ------------------------
Jones                          Java
Jones                          C
Jones                          C++
Jones                          Oracle
Jones                          Sybase
Jones                          ASP
Jones                          JSP
James                          Java
James                          Oracle
James                          Sybase
James                          Proj_Mgmt
Ward                           Oracle
Ward                           VB
Ward                           VC++
Ward                           COM
Ward                           DCOM
Turner                         DW
Turner                         PHP
 
18 rows selected.



  1  select enm, column_value, count(*) over (partition by enm) cnt
  2* from employee_skills E, table(e.skills)
ops$tkyte@ORA9IR2> /
 
ENM                            COLUMN_VALUE                    CNT
------------------------------ ------------------------ ----------
James                          Java                              4
James                          Oracle                            4
James                          Sybase                            4
James                          Proj_Mgmt                         4
Jones                          Java                              7
Jones                          C                                 7
Jones                          C++                               7
Jones                          Oracle                            7
Jones                          Sybase                            7
Jones                          ASP                               7
Jones                          JSP                               7
Turner                         DW                                2
Turner                         PHP                               2
Ward                           Oracle                            5
Ward                           VB                                5
Ward                           VC++                              5
Ward                           COM                               5
Ward                           DCOM                              5
 
18 rows selected.

ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
 
  1  select enm, cnt
  2  from (
  3  select enm, column_value, count(*) over (partition by enm) cnt
  4  from employee_skills E, table(e.skills)
  5  order by 3
  6  )
  7* where rownum = 1
ops$tkyte@ORA9IR2> /
 
ENM                                   CNT
------------------------------ ----------
Turner                                  2
 
ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
 
  1  select enm, cnt
  2  from (
  3  select enm, column_value, count(*) over (partition by enm) cnt
  4  from employee_skills E, table(e.skills)
  5  order by 3 desc
  6  )
  7* where rownum = 1
ops$tkyte@ORA9IR2> /
 
ENM                                   CNT
------------------------------ ----------
Jones                                   7

 

OK

March 18, 2004 - 12:31 am UTC

Reviewer: Naresh from Arlington,VA,USA

Dear Sir,
Regarding the above example,"How to select only the values
from Varray" like
sql>select column_value from table(..)
I used this but throws errors as "Illegal Identified".
Please do reply.


Tom Kyte

Followup  

March 18, 2004 - 7:22 am UTC

hows about your entire example -- no idea what you are trying to do really? what goes in the .. there?

OK

March 19, 2004 - 6:13 am UTC

Reviewer: Naresh from Singapore

Hi Tom,
I meant " If a varray object type becomes part of the
relational table,How to select values only from the varray"
?To be more obvious
sql>select column_value from table(relational table.varray);
Does this work?
Please do reply.
Bye!


Tom Kyte

Followup  

March 19, 2004 - 9:24 am UTC

select x.*
from relational_table r, TABLE(r.varray) X
/



VARRAY Querying

May 27, 2004 - 5:50 am UTC

Reviewer: bonus from Hk

For example if i hv varray players_var that stores name of the players

Game
----
Game_name
Player players_var

and if i like to query that results football plays by certain player with in varray

I am trying to query the game that john plays

example: select g.game_name,p.john
from game g, table(g.Player) a

I know this is wrong way to do how can i get my result
Thanx





Tom Kyte

Followup  

May 27, 2004 - 9:32 am UTC

select g.gname
from game g, table(g.player) a
where a.column_value = 'JOHN'

without a full exmaple, best I can do.

and shows why this object stuff (not just OR stuff, object stuff in general) seems cool but might not be.

You have modelled a relation where players is a "weak entity". You cannot query players except in light of a game. players is not strong enough to standalone.

But, I've never actually seen a true "weak entity" in real life. If you had modelled this as

"games" M:M "players"

You would be able to answer questions efficiently from either perspective (going from games -> list of players, going from players -> list of games)


i always suggest "use real tables"

continue

May 27, 2004 - 6:02 am UTC

Reviewer: Bonus from Hk

I tried

select g.game_name, p.john
from game g, table(g.players) p

this query works for me:

select g.game_name, p.*
from game g, table(g.players) p

But i need only certain player that i need like in relational DB ( where player='John')

And one more question if i hv VARRAY and Nested Table in same object table for instance:

created type stat_type as object
( run NUMBER,
Score Number
..
);

CREate type stat_table AS TABLE OF stat_type

Game
-----
game_name
game_duration
player Players_var (vararray)
game_stat stat_table (Nested Table)



here is my sample table.

Now i like to query using both Nested table and Varray

The query what i am looking for is:

i want to query:
to get avegare score for the game in which john get participates;

i tried to do like this:

select AVG(st.score)
from game.g, table(g.player) p, table(g.stat_table) st
where p.John

I hv got same problem here as my first one, would you please help me to get correct outcome
Thanx

Tom Kyte

Followup  

May 27, 2004 - 9:33 am UTC

given the above information, hopefully you figured it out. just use "where"

continue

May 27, 2004 - 10:39 pm UTC

Reviewer: Bonus from Hk

I didn';t got your point can u elaborate it!

can i use where clause at VARRAY and NESTED TABLE can u explain me more

Thanx!

Tom Kyte

Followup  

May 28, 2004 - 7:59 am UTC

actually in looking at your model, i fail to see the relationship between "john" and "scores"

You have a (unordered) list of players for a game.
You have an (ordered) list of stats for a game.

I see no relation between the two.

December 01, 2004 - 4:23 am UTC

Reviewer: sanjay

Dear sir,
Could you please look into
the code and help me to get the solution as given below?

create type skills_va as varray(20) of varchar2(24);

create table employee_skills(enm varchar2(30),skills skills_va , marks skils_va);

insert into employee_skills values('Jones',skills_va('Java','C','C++','Oracle','Sybase','ASP','JSP'),
skills_va('12','23','33','45','545','66','89')
);

insert into employee_skills values('James',
skills_va('Java','Oracle','Sybase','Proj_Mgmt'),
skills_va('56','78','88','900'));

i want the out put as
enm skills marks
--- ------
Jones Java 12
C 34
C++ 36

select enm , a.* , b.* from
employee_skills, table(SKILLS) a , table(marks) b
/
the output is
ENM COLUMN_VALUE COLUMN_VALUE
Jones Java 1
Jones Java 2
Jones Java 3
Jones Java 4
Jones Java 5
Jones Java 6
Jones Java 7


thanks
regards





Tom Kyte

Followup  

December 01, 2004 - 8:35 am UTC

yuck, varrays used to store data persistently.

yuckier still -- TWO arrays when the data is obviously correlated and should be a single array with two attributes.

yuckiest of all -- you want this data as it should be, as rows and columns.

I would fix my data model personally. All of your pain is "self inflicted" here.

can we do that instead of trying to square peg a very round hole?

You need two tables
You need a primary/foreign key

then, everything becomes easy.

December 14, 2004 - 12:31 am UTC

Reviewer: MacNeil Fernandes from India


As Always...the best

February 03, 2005 - 5:13 pm UTC

Reviewer: Srinivasan from India

As Always...the best

Syntax for inserting a varray column

April 25, 2005 - 10:47 am UTC

Reviewer: andrija from Zagreb, Croatia, Europe

I found this topic to be very useful, since I was inserting a record into a table which contained one varray column. I had some trouble with syntax of the insert statement before I googled up this page. My insert looked like this in the end:

INSERT INTO XT_SDW_ACCESS_RIGHTS
( ...some column names...,
ACCESS_PRIVS )
VALUES
( ...some values..., SDD_ACC_PRIV_LIST('Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL));

The column in question was ACCESS_PRIVS and its datatype was
SDD_ACC_PRIV_LIST defined as: CREATE OR REPLACE TYPE "SDD_ACC_PRIV_LIST" AS VARRAY(25) OF VARCHAR(1);

Thanks a lot, Tom.


Adding elements to VARRAY

November 18, 2005 - 5:46 pm UTC

Reviewer: A reader

Tom,
I am trying to add elements to VARRAY from a pl/sql program[in Oracle 10g].
Here is what i'm trying to do

create table EMP
(
EMPNO NUMBER not null,
EMPNAME VARCHAR2(20),
SALARY INTEGER
)


CREATE OR REPLACE package array_package as
Type my_array is VARRAY(50) of Varchar2(100);
end;
/
------

declare

cursor c1 is
select column_name from user_tab_columns where table_name = 'EMP';

myarray array_package.my_array:=array_package.my_array();
begin
for i in c1
loop
dbms_output.put_line('test '||i.column_name);
myarray.extend;
myarray:=array_package.my_array(i.column_name);


end loop;

for i in 1..myarray.count
loop
dbms_output.put_line(myarray(i)||' '||i);
end loop;
end;

----

But i am not getting all three columns in the array. only last column is being added to array

Am I Missing something?
I tried to use for loop like this

for i in c1
loop
myarray(i):=array_package.my_array(i.column_name);


end loop;

But I'm getting "Expression is of wrong type" (at myarray(i))

Thanks for any suggestions..

Tom Kyte

Followup  

November 19, 2005 - 9:59 am UTC

yes, because everytime through the loop you are setting myarray to a brand new array.



ops$tkyte@ORA9IR2> declare
  2   cursor c1 is
  3     select column_name from user_tab_columns where table_name = 'EMP';
  4
  5     myarray array_package.my_array:=array_package.my_array();
  6  begin
  7    for i in c1
  8    loop
  9      dbms_output.put_line('test '||i.column_name);
 10      myarray.extend;
 11      myarray(myarray.count) := i.column_name;
 12    end loop;
 13
 14    for i in 1..myarray.count
 15    loop
 16     dbms_output.put_line(myarray(i)||'  '||i);
 17    end loop;
 18  end;
 19  /
test EMPNO
test EMPNAME
test SALARY
EMPNO  1
EMPNAME  2
SALARY  3

PL/SQL procedure successfully completed.

<b>that fixes your code, but this is a better way:</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2     myarray array_package.my_array;
  3  begin
  4      select column_name bulk collect into myarray from user_tab_columns where table_name= 'EMP';
  5      for i in 1..myarray.count
  6      loop
  7           dbms_output.put_line(myarray(i)||'  '||i);
  8      end loop;
  9  end;
 10  /
EMPNO  1
EMPNAME  2
SALARY  3

PL/SQL procedure successfully completed.

 

Thanks a bunch

November 20, 2005 - 7:33 pm UTC

Reviewer: A reader

Tom, Thank you very much for this clear and precise solution. But why doesn't it work if i use myarray(i) [i subscript] but it works if you use myarray(myarray.count)?

Thank you

Tom Kyte

Followup  

November 21, 2005 - 8:25 am UTC

your code:

for i in c1
loop
dbms_output.put_line('test '||i.column_name);
myarray.extend;
myarray:=array_package.my_array(i.column_name);
end loop;


That assignment to myarray of array_package.my_array(i.column_name); isn't using a subscript, that is the same as:


myarray := array_package.my_array( 'Hello' );



you assigned an ENTIRELY NEW array each and every time to myarray in the loop.


I is not a subscript, I in your example is a record.

Thanks Tom.

November 21, 2005 - 10:10 am UTC

Reviewer: A reader

I was not thinking "I" as a record [Duh!!!!].

Thanks for all your help

referential integrity with Varrays

May 20, 2008 - 10:45 am UTC

Reviewer: Vipin

Tom,

As usual thanks for this great site.

Just for the kick of it, can you create referential integrity on values stored in the Varray. What I mean is suppose I have a Varray say empno_varray, if I use this for creating an object table say "people" with the following structure:-

People
(
people_id integer,
people_name varchar2(500),
people_zipcode integer,
subordinates empno_varray
)

Can I enforce the referential integrity that each of the value stored in the "subordinates" array is actually available in the EMP table?

Audience....I expect some sarcasm for seeking RDBMS functionality -referential integrity- in an Object Relational model, but I can't resist to ask this question ;)
Tom Kyte

Followup  

May 20, 2008 - 11:49 am UTC

RI works at the column level - there isn't a single column here, but an array. You'd use a real table for this and an object relational view

meaning, store the data relationally

expose it via an object relational view - if you really think you want it that way.

using varray

May 05, 2009 - 6:14 am UTC

Reviewer: Jitendera from INDIA

Dear Sir
I am having one Master table consisting of 14 columns of varchar2 type.

First col (rollno) is primary key and Last Col contains FINAL RESULT. Now remaning 12 columns are actually contains subject & marks obtained data of six sets (sub1 mrk1, sub2 mrk2,sub3 mrk3, sub4 mrk4, sub5 mrk5 , sub6 mrk6);
Now to declare a candidate PASS candidate must have scored moer then 50% marks in at leaset 5 subjects. IF candidate has cleared only 4 subjects then candidate is eligible for Compartment Exam. IF less then 4 then "FAIL"

Here some other conditions are also to be tested like
Subject Franch and Spanish can not be taken together as candidates are not allowed to take two foreign languages.

What i had done in my procedure to validate subject and calculate result and finally delare a candidate as "PASS" or "FAIL" or "COMP" or "ABSENT" is
that i am inserting data of particular rollno in one other table called subjects containing three fileds (subcode, mrk, result),
So inserting all six subject codes and marks in that table and then calculating result of Each Subject Sepratly if candidate has obtained more then 50% marks in that subject.

Then for performing validiations of subjects to just check wheather candidate has attemped Franch and Spanish like combinations using
select count(*) into c1 from subjects where subject in ('Franch','Spanish);
if count is 2 then
i return a flag to delcare it "W" Result withheld

Similarly if all combinations are valid then calculate result and verify that candidate has cleared atleast four subjects. IF cleared four subjects then i return a flag to delcare it "C" Result Compttment.
Similary if cleared atleaset five i return a flag to delcare it "P" Result PASS./
Sir now problem is that to process 200000 records it is taking more then 4 hours.
I dono't want to insert data in another table but to keep in varray or nested table in proceudre itself and flush it when cursor moves to next record of main file.
Please guide me how can i use varray/nested table for the same.
Thanks in advance
regards
Jitender


max value from varrray otr pl/sql table

May 26, 2010 - 11:59 am UTC

Reviewer: satin satin

Hello tom,

how can we get a max value from the varray or pl/sql table. Can we search for a specfic value on of above types.

EXAMPLE:

TYPE high_value_type IS table of dba_tab_partitions.%ROTYPE INDEX BY PLS_INTEGER;
l_high_value high_value_type;
select * BULK COLLECT INTO l_highvalue
FROM all_tab_partitions WHERE table_name = upper = 'FACT_REC';

Now If we can get max or search a "specific" value from the above pl/sql tables .that will help.

Basically I am looking the min(high_value) and max(high_value) and it is corrosponding partition_name.

Can you suggest me how do we do this

thanks
Tom Kyte

Followup  

May 26, 2010 - 12:24 pm UTC

not so much with a plsql table type - but with a collection - yes. You can use SQL on collections.

plsql table type - you'll be using iteration and procedural logic to find the min/max.



examples please

May 26, 2010 - 1:08 pm UTC

Reviewer: satin satin

Could you please give me an example and how do we get the max value and min value from set of values using collections ?
Tom Kyte

Followup  

May 26, 2010 - 1:25 pm UTC

ops$tkyte%ORA10GR2> create or replace type myScalarType as object
  2  ( userid number, username varchar2(30), created date )
  3  /

Type created.

ops$tkyte%ORA10GR2> create or replace type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_data myTableType;
  3          l_min  number;
  4          l_max  number;
  5  begin
  6          select myScalarType( user_id, username, created )
  7            bulk collect into l_data
  8            from all_users;
  9
 10          select min( userid ), max( userid )
 11            into l_min, l_max
 12            from table( l_data ) x;
 13
 14          dbms_output.put_line( 'min = ' || l_min || ' max = ' || l_max );
 15  end;
 16  /
min = 0 max = 612

PL/SQL procedure successfully completed.

Why from Collection

May 26, 2010 - 1:39 pm UTC

Reviewer: V.Hariharaputhran from Chennai India

Mr.Oracle,
Excellent example as always.

Satin
Between when you would be able to get max,min value from the table directly even before bulk collecting into a collection, do you still perfer performing 2 select statements(BULK COllect,TABLE Function) to get the min and max values.


Regards
V.Hari

VARRAY and nested table explaination helped

September 07, 2012 - 12:27 am UTC

Reviewer: Trupti S. Indi from India

Hi Tom,

Your answers always help me to solve problem in oracle.

Thanks,
Trupti

varray CAST very confusing

January 19, 2013 - 12:17 pm UTC

Reviewer: Biswaranjan from India

Hi Tom,

Hope you are doing fine.


#####################################################
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 19 23:18:44 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
#################################################

SQL> set serveroutput on
SQL> create or replace type xyz1 is varray(3) of number;
  2  /

Type created.

SQL> create or replace type xyz2 is varray(1) of number;
  2  /

Type created.

SQL> create or replace type xyz3 is varray(3) of number(1);
  2  /

Type created.

SQL> create or replace type xyz4 is varray(1) of number(1);
  2  /

Type created.

SQL> create or replace type xyz5 is varray(3) of varchar2(10);
  2  /

Type created.

SQL> create table test1(a number,b xyz1,c number);

Table created.

SQL> insert into test1 values(1,xyz1(200,300,400),5);

1 row created.

SQL> commit;

Commit complete.

In one book I found something like "for varray table column should be 
converted using 'as nested_table' ".
I just crosschecked and found that is wrong .Even it is accepting like "as varray" with CAST.
(may be it is good to use NESTEDTABLE with various table
coz of varing element in that varraycolumn of those tables for CAST method.
)


I did some more analysis and found some strange thing explained in 
the below testcase.

SQL> select column_value from table(select cast(b as xyz1) from test1);

COLUMN_VALUE
------------
         200
         300
         400

It seems fine.###(case1)


SQL> select column_value from table(select cast(b as xyz2) from test1);

COLUMN_VALUE
------------
         200
         300
         400
But here I was thinking it should throw error coz xyz2 varray has max capacity to store 
only one element.##(case2)

SQL> select column_value from table(select cast(b as xyz3) from test1);

COLUMN_VALUE
------------
         200
         300
         400

Here also I was thinking should throw error due to number(1).  ## case3

SQL> select column_value from table(select cast(b as xyz4) from test1);

COLUMN_VALUE
------------
         200
         300
         400
here too :) ###case4

SQL> select column_value from table(select cast(b as xyz5) from test1);
select column_value from table(select cast(b as xyz5) from test1)
                                           *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got DEV_USER.XYZ1

this is obvious as expected.

SQL> DECLARE
  2  AA XYZ2;
  3  BEGIN
  4  AA:=XYZ2(200,300,400);
  5  DBMS_OUTPUT.PUT_LINE(AA(1));
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 1

this is obvious as expected.

could you please tell,why it didn't give error in case 2,3 and 4.

Even I tried in version 9.0 too same result I got.(one thing
I too tried that method which you explained at the top of this page but same result).


Thanks & Regards,
Biswaranjan


cont. to confusing varray.

January 31, 2013 - 10:51 pm UTC

Reviewer: A reader from india

Hi Tom,

Good to see , you are back.
Hope your had a nice journey.

Can you please give your views on the question I asked above.

Another question about asktom site, I am seeing 500 question headier at your homepage. Are those the total number of question (I mean to say if new question will be added will that number increase above 500 )?
coz I plan to read all pages when i have more time.

sorry to ask this question.

regards,
Ranjan.
Tom Kyte

Followup  

February 01, 2013 - 8:32 am UTC

I agree that it doesn't look right - do you have access to support, if so, please file a bug. if not, let me know and I'll do that.

I don't know what you mean by 500 question headier at your homepage?



cont. to my last post.

February 01, 2013 - 11:51 pm UTC

Reviewer: Ranjan from india

Hi Tom,
thanks for your reply.
I dont have access to support.

and that 500 number I mean the total number of the SUBJECT.
I just counted by clicking next,next.... and found there are
500 total unique subjects.

thanks as always,
Biswaranjan.

varray memory allocation

November 06, 2013 - 10:33 am UTC

Reviewer: Sam from Charlotte,USA


Hi Tom,
Greetings

when we declare Varray at that time varray memory get allocated or at the time of insertingg the records.

Like

Type t_varray is varray(30) of varchar2(15).

then here only it get allocated or when we assign some value into that.

Can we delete records from varray from middle.

Thanks
Sam
Tom Kyte

Followup  

November 07, 2013 - 3:29 pm UTC

A type will not allocate memory.


varrays are not "sparse", if you want sparse you will use a plsql index by table.

Followup to original

January 07, 2014 - 8:31 pm UTC

Reviewer: Dale from United States

Tom,
Following up on your original answer, posted here for completeness, what is the best way to handle possible null values in the EMAIL_VA column? The join to the nested table does not return rows if the data in there is null.

I am converting a table similar to this, and came up with the below, but just want to validate it. See example:

--Create the type
create or replace type emails_va as varray(2) of varchar2(30);

--Create the table
create table EMP
(
EMPID NUMBER not null,
EMPNAME VARCHAR2(20),
EMAILS EMAILS_VA
);

--insert your 2 rows
insert into emp (empid, empname, emails) values ( 1, 'tom', emails_va( 'x', 'y' ) );

insert into emp (empid, empname, emails) values ( 2, 'lori', emails_va( 'a', 'b' ) );

--query successfully
SELECT empid
,empname
,x.*
FROM emp
,TABLE(emp.emails) x;

/*
1 tom x
1 tom y
2 lori a
2 lori b
*/

--insert new row with null email column
insert into emp (empid, empname) values ( 3, 'dale');

--select and not get the null value record
SELECT empid
,empname
,x.*
FROM emp
,TABLE(emp.emails) x;
/*
1 tom x
1 tom y
2 lori a
2 lori b
*/
--Add outer join to array and get all the rows
SELECT empid
,empname
,x.*
FROM emp
,TABLE(emp.emails)(+) x;
/*
1 tom x
1 tom y
2 lori a
2 lori b
3 dale
*/

Are there any pitfalls to adding the (+) directly to the table statement in the query? Is this the correct way to query this data and get all data in the table?
Thanks,
Dale

Tom Kyte

Followup  

January 08, 2014 - 6:04 pm UTC

when un-nesting like this - it is really a "join in disguise". So - just like if you had an EMAILS_VA *table* - a real table, not a varray - and joined to it - you'd need to outer join to get Dale.

So, you need to outer join.

this is documented here:

http://bit.ly/K6AAwh

(that is a link into oracle.com)