VARRAYs in Oracle 8.0.3

From: Gunjeet Singh <gunjeet_at_sunsparkle.corp.sun.com>
Date: 16 Sep 1998 23:13:30 GMT
Message-ID: <6tpgmq$p17$1@corpnews1.Corp.Sun.COM>

Hi,

I have a requirement of creating a table where for a given id, i have to either store three cost components (for a given qtr), or store 4 sets of such components (4 * 3 for a year). I'm looking at the possiblity of designing this table with a VARRAY column where each of the elements of VARRAY will store three cost components and the size of the VARRAY will be 4.

Is it possible to directly do a select against such a table and select the cost component values in such a form that it can be directly used by the front end - or will I be forced to use PL/SQL to parse the output of a select against this table to extract the values ?

Here's what I have done so far :

SQL> create or replace type cost_comps_typ as object (b_cost float, t_cost float, m_cost float); 2 /

Type created.

SQL> create or replace type cost_comps_vry_typ as VARRAY(4) of cost_comps_typ; 2 /

Type created.

SQL> create table plist_details (id number, cost cost_comps_vry_typ, ifactor1 float);

Table created.

SQL> desc plist_details
Name Null? Type

-------- ---- ID NUMBER COST COST_COMPS_VRY_TYP IFACTOR1 FLOAT(126)

SQL> insert into plist_details values (1,cost_comps_vry_typ(cost_comps_typ(10,20,30), cost_comps_typ(100,200,300), cost_comps_typ(1000,2000,500)),15.5678);

1 row created.

SQL> select p.cost from plist_details p;

COST_COMPS_VRY_TYP(COST_COMPS_TYP(10, 20, 30), COST_COMPS_TYP(100, 200, 300), COST_COMPS_TYP(1000, 2000, 500)) SQL> select p.cost.COST_COMPS_TYP.B_COST from plist_details p; select p.cost.COST_COMPS_TYP.B_COST from plist_details p

*
ERROR at line 1:
ORA-00904: invalid column name

How do I get the values for the cost components in a form that I can directly use ? Also, will it be possible to use aggregate functions like SUM against these cost columns ?

Any examples will he very helpful.

Thanks,

Gunjeet Received on Wed Sep 16 1998 - 18:13:30 CDT