OraFAQ Forum: SQL & PL/SQL » Varrays in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Varrays in Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Varrays in Oracle [message #8045]

Wed, 23 July 2003 02:21

shaan
Messages: 9
Registered: July 2003

Junior Member

How do i retrieve values from Varray in Oracle .
Plz Help me out !!!
The design of the table is

create type prop as object
(
properties varchar2(4)
);

create type proplist as VARRAY(20) of prop;

create table tbl_product_charateristics (
prd_code varchar(4) not null,
prd_properties proplist
)

Report message to a moderator

Re: Varrays in Oracle [message #8055 is a reply to message #8045]

Wed, 23 July 2003 15:33

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

I couldn't get it to work with the prop type, but if I changed the varray(20) of prop to varray(20) of varchar2(4), which is the same as the prop type, then it works O.K. I have included a few different selects below to demonstrate how to retrieve varray data based on either the prd_code or a value within the varray.

SQL> CREATE TYPE proplist AS VARRAY (20) OF VARCHAR2 (10);
  2  /

Type created.

SQL> CREATE TABLE tbl_product_characteristics
  2    (prd_code	     VARCHAR2 (4) NOT NULL,
  3  	prod_properties      proplist)
  4  /

Table created.

SQL> INSERT INTO tbl_product_characteristics
  2  VALUES ('pc_1', proplist ('pp1a', 'pp1b'))
  3  /

1 row created.

SQL> INSERT INTO tbl_product_characteristics
  2  VALUES ('pc_2', proplist ('pp2a', 'pp2b'))
  3  /

1 row created.

SQL> SELECT * FROM tbl_product_characteristics
  2  /

PRD_                                                                            
----                                                                            
PROD_PROPERTIES                                                                 
--------------------------------------------------------------------------------
pc_1                                                                            
PROPLIST('pp1a', 'pp1b')                                                        
                                                                                
pc_2                                                                            
PROPLIST('pp2a', 'pp2b')                                                        
                                                                                

SQL> SELECT prd_code, pp.*
  2  FROM   tbl_product_characteristics,
  3  	    TABLE (tbl_product_characteristics.prod_properties) pp
  4  /

PRD_ COLUMN_VAL                                                                 
---- ----------                                                                 
pc_1 pp1a                                                                       
pc_1 pp1b                                                                       
pc_2 pp2a                                                                       
pc_2 pp2b                                                                       

SQL> SELECT prd_code, pp.*
  2  FROM   tbl_product_characteristics,
  3  	    TABLE (tbl_product_characteristics.prod_properties) pp
  4  WHERE  prd_code = 'pc_2'
  5  /

PRD_ COLUMN_VAL                                                                 
---- ----------                                                                 
pc_2 pp2a                                                                       
pc_2 pp2b                                                                       

SQL> SELECT prd_code, pp.column_value
  2  FROM   tbl_product_characteristics,
  3  	    TABLE (tbl_product_characteristics.prod_properties) pp
  4  WHERE  pp.column_value = 'pp1b'
  5  /

PRD_ COLUMN_VAL                                                                 
---- ----------                                                                 
pc_1 pp1b

Report message to a moderator

Previous Topic:	QUERY
Next Topic:	Characteristic Functions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:12:53 CDT 2024