Excellent
A reader, October 09, 2001 - 6:28 pm UTC
VARRAY
William, October 10, 2001 - 8:46 am UTC
Exellent
But waht EMAILS_VA data type mean
Mohammed Esmael, October 10, 2001 - 10:00 am UTC
yes, useful,but could you please explain to me the benfit of using EMAILS_VA data Type ,and what it is exactly.
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
Anitha, October 10, 2001 - 10:54 am UTC
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
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
Anitha, October 10, 2001 - 11:54 am UTC
thank you , Tom
Give the link for the documentation of this datatype-EMAILS_VA
A reader, October 10, 2001 - 4:15 pm UTC
EMAILS_VA
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?
Benoit Hebert, November 21, 2002 - 12:09 pm UTC
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?
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
Siva, January 07, 2004 - 6:46 am UTC
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!
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
Naresh, March 18, 2004 - 12:31 am UTC
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.
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
Naresh, March 19, 2004 - 6:13 am UTC
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!
March 19, 2004 - 9:24 am UTC
select x.*
from relational_table r, TABLE(r.varray) X
/
VARRAY Querying
bonus, May 27, 2004 - 5:50 am UTC
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
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
Bonus, May 27, 2004 - 6:02 am UTC
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
May 27, 2004 - 9:33 am UTC
given the above information, hopefully you figured it out. just use "where"
continue
Bonus, May 27, 2004 - 10:39 pm UTC
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!
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.
sanjay, December 01, 2004 - 4:23 am UTC
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
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.
MacNeil Fernandes, December 14, 2004 - 12:31 am UTC
As Always...the best
Srinivasan, February 03, 2005 - 5:13 pm UTC
As Always...the best
Syntax for inserting a varray column
andrija, April 25, 2005 - 10:47 am UTC
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
A reader, November 18, 2005 - 5:46 pm UTC
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..
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
A reader, November 20, 2005 - 7:33 pm UTC
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
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.
A reader, November 21, 2005 - 10:10 am UTC
I was not thinking "I" as a record [Duh!!!!].
Thanks for all your help
referential integrity with Varrays
Vipin, May 20, 2008 - 10:45 am UTC
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 ;)
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
Jitendera, May 05, 2009 - 6:14 am UTC
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
satin satin, May 26, 2010 - 11:59 am UTC
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
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
satin satin, May 26, 2010 - 1:08 pm UTC
Could you please give me an example and how do we get the max value and min value from set of values using collections ?
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
V.Hariharaputhran, May 26, 2010 - 1:39 pm UTC
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
Trupti S. Indi, September 07, 2012 - 12:27 am UTC
Hi Tom,
Your answers always help me to solve problem in oracle.
Thanks,
Trupti
varray CAST very confusing
Biswaranjan, January 19, 2013 - 12:17 pm UTC
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.
A reader, January 31, 2013 - 10:51 pm UTC
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.
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.
Ranjan, February 01, 2013 - 11:51 pm UTC
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
Sam, November 06, 2013 - 10:33 am UTC
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
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
Dale, January 07, 2014 - 8:31 pm UTC
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
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)