SQL against VARRAY returning subscripts/sequences ? [message #347304] |
Thu, 11 September 2008 04:51  |
carsten.jorgensen
Messages: 18 Registered: May 2005
|
Junior Member |
|
|
Hi experts,
Does anybody know if it possible via SQL to retrieve the element positions (subscripts) of a VARRAY ?
Eg.
CREATE TYPE LINE_OBJECT AS OBJECT
(NAME VARCHAR2(10) )
/
CREATE OR REPLACE TYPE LINE_ARRAY_OBJECT AS VARRAY(10)
OF LINE_OBJECT
/
CREATE TABLE TEST
(NAME_ITEMS LINE_ARRAY_OBJECT);
INSERT INTO TEST
VALUES (LINE_ARRAY_OBJECT
(LINE_OBJECT('MARC'),
LINE_OBJECT('SOPHIE'),
LINE_OBJECT('NADIA')
)
);
SELECT B.NAME
FROM TEST A,
TABLE(A.NAME_ITEMS) B
WHERE B.NAME LIKE '%A%'
NAME
----------
MARC
NADIA
In this example I wish to return a column containing 1 for the first row and 3 for the 2nd row (since MARC is stored as 1st element and NADIA is stored as 3rd element in the VARRAY).
|
|
|
Re: SQL against VARRAY returning subscripts/sequences ? [message #347339 is a reply to message #347304] |
Thu, 11 September 2008 05:47   |
 |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
There not exist a direct way to index a vararray so you can only use this workaround.
CREATE OR REPLACE TYPE LINE_ARRAY_OBJECT AS VARRAY(10)
OF varchar2(10)
/
CREATE TABLE TEST (NAME_ITEMS LINE_ARRAY_OBJECT);
INSERT INTO TEST
VALUES (LINE_ARRAY_OBJECT('MARC','SOPHIE','NADIA'));
INSERT INTO TEST
VALUES (LINE_ARRAY_OBJECT('MARC2','SOPHIE2','NADIA2'));
commit
/
Processing ...
select idx,column_value
from (
select column_value,
row_number() over (partition by rid order by null) as idx
from (
SELECT a.rowid as rid,b.column_value
FROM TEST A
cross join TABLE(a.NAME_ITEMS) b
)
)
WHERE column_value LIKE '%A%'
Query finished, retrieving results...
IDX COLUMN_VALUE
-------------------------------------- ------------
1 MARC
3 NADIA
1 MARC2
3 NADIA2
4 row(s) retrieved
Bye Alessandro
[Updated on: Thu, 11 September 2008 05:48] Report message to a moderator
|
|
|
Re: SQL against VARRAY returning subscripts/sequences ? [message #347343 is a reply to message #347304] |
Thu, 11 September 2008 05:54   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I slighty modified your case (thanks for posting it) in order to add a primary key to the table:
SQL> create table test (id integer primary key,
2 NAME_ITEMS LINE_ARRAY_OBJECT)
3 /
Table created.
SQL> INSERT INTO TEST
2 VALUES (1,
3 LINE_ARRAY_OBJECT
4 (LINE_OBJECT('MARC'),
5 LINE_OBJECT('SOPHIE'),
6 LINE_OBJECT('NADIA')
7 )
8 );
1 row created.
SQL> INSERT INTO TEST
2 VALUES (2,
3 LINE_ARRAY_OBJECT
4 (LINE_OBJECT('MICHEL'),
5 LINE_OBJECT('PAUL'),
6 LINE_OBJECT('NORBERT')
7 )
8 );
1 row created.
SQL> SELECT B.ID, B.NAME, B.RN
2 FROM TEST A,
3 (select id, name,
4 row_number() over (partition by id order by null) rn
5 from TEST A,TABLE(A.NAME_ITEMS)) B
6 WHERE B.NAME LIKE '%A%'
7 AND A.ID = B.ID
8 ORDER BY B.ID, B.RN
9 /
ID NAME RN
---------- ---------- ----------
1 MARC 1
1 NADIA 3
2 PAUL 2
3 rows selected.
Regards
Michel
|
|
|
Re: SQL against VARRAY returning subscripts/sequences ? [message #347365 is a reply to message #347343] |
Thu, 11 September 2008 06:49   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I did wonder about using Row_number like that to give an order to the values returned from the table, but I've never been able to find anything in the docs about the order that the data is returned in. In practice, it always seems to return rows in their index order, but I've never extensiely testedit, and without a guarantee from Oracle would testing really prove anything?
|
|
|
Re: SQL against VARRAY returning subscripts/sequences ? [message #347502 is a reply to message #347365] |
Thu, 11 September 2008 22:20   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I agree with JR here.
If Oracle doesn't promise to return rows in the index order, then we should not assume that will always be the case. It's the same argument as trying to use GROUP BY or DISTINCT to sort data.
The "safe" solution would be to create a (pipelined) table function that returns an object type with the index added explicitly as a column.
Ross Leishman
|
|
|
|
|
|
Re: SQL against VARRAY returning subscripts/sequences ? [message #347633 is a reply to message #347573] |
Fri, 12 September 2008 08:05  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
For information:
Oracle Expert by T. Kyte p.880 | VARRAYs are true arrays. The data will remain
inherently ordered as you left it. In our example
above, the addresses are appended to the array.
This implies that the oldest address is the first
address, and the last previous address is the last
address found in the array. A nested table
implementation would need another attribute in
order to identify the relative age of an address.
|
Regards
Michel
|
|
|