Home » SQL & PL/SQL » SQL & PL/SQL » SQL against VARRAY returning subscripts/sequences ? (Oracle server 9.2.0.7.0)
SQL against VARRAY returning subscripts/sequences ? [message #347304] Thu, 11 September 2008 04:51 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #347530 is a reply to message #347502] Fri, 12 September 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
table function that returns an object type with the index added explicitly as a column

How do you get the index?

Regards
Michel
Re: SQL against VARRAY returning subscripts/sequences ? [message #347573 is a reply to message #347530] Fri, 12 September 2008 03:45 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When referenced in PL/SQL, the VARRAY does have subscripts. So we loop through the array piping out the loop index plus the record contents.

Ross Leishman
Re: SQL against VARRAY returning subscripts/sequences ? [message #347595 is a reply to message #347573] Fri, 12 September 2008 05:01 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
So the for loop inside PL/SQL retrieves the elements in the correct order while the a collection iterator, used by a SQL query (if you take a look at an explain plan you can notice it) doesn't?

But notice that an Iterator has the same properties as the next and prior methods you use in PL/SQL to retrieve successive and precedent array element.

If you need oracle words about it take a look at Note:47957.1 on Metalink.

Bye Alessandro
Re: SQL against VARRAY returning subscripts/sequences ? [message #347633 is a reply to message #347573] Fri, 12 September 2008 08:05 Go to previous message
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
Previous Topic: DFF
Next Topic: How to get error number for exception_init
Goto Forum:
  


Current Time: Sat Feb 15 15:05:57 CST 2025