Home » SQL & PL/SQL » SQL & PL/SQL » distinct values from varray (oracle 10g)
distinct values from varray [message #402739] Tue, 12 May 2009 04:48 Go to next message
Developer
Messages: 19
Registered: January 2004
Junior Member
Hi All,
I am facing a problem while selecting values from varray column.
CREATE OR REPLACE
TYPE v_arr AS VARRAY(2) OF NUMBER(16);

CREATE TABLE varray_test
(  detail_id          NUMBER(16),
   itb_id             v_arr);

INSERT INTO VARRAY_TEST ( DETAIL_ID, ITB_ID ) 
VALUES (1, v_arr(12, 32));

INSERT INTO VARRAY_TEST ( DETAIL_ID, ITB_ID ) 
VALUES (2, v_arr(12, 33));

COMMIT;



Now i want to get distinct values from itd_id column for all detail_id
when I use this select, i get ora-01427.

SELECT column_value
  FROM TABLE (SELECT CAST (itb_id AS v_arr)
                FROM varray_test)


Thanks!
Re: distinct values from varray [message #402743 is a reply to message #402739] Tue, 12 May 2009 05:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT distinct column_value
  FROM varray_test v
      ,TABLE (v.itb_id);


It's also easy with a nested table:
create or replace type v_nt as table of number(16);
/

CREATE TABLE nested_table_test
(  detail_id          NUMBER(16),
   itb_id             v_nt)
   nested table itb_id store as itb_id_store;
INSERT INTO nested_table_TEST ( DETAIL_ID, ITB_ID ) 
VALUES (1, v_nt(12, 32));

INSERT INTO nested_table_TEST ( DETAIL_ID, ITB_ID ) 
VALUES (2, v_nt(12, 33));

COMMIT;

select set(itb_id) from nested_table_test;
Previous Topic: XMLTYPE in Oracle
Next Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Goto Forum:
  


Current Time: Fri Dec 02 18:30:38 CST 2016

Total time taken to generate the page: 0.31825 seconds