Re: VARRAYs in Oracle 8.0.3

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/22
Message-ID: <3611b487.91562289@192.86.155.100>

A copy of this was sent to gunjeet_at_sunsparkle.corp.sun.com (Gunjeet Singh) (if that email address didn't require changing) On 21 Sep 1998 20:24:45 GMT, you wrote:

>
>
>Hi,
>
>
>Could someone please tell me if the new 'THE' operator is supported for
>VARRAYs ? It doesn't seem to be supported in 8.0.3 but is it supported
>in 8.0.4 ? Are there any operators that work on individual elements of
>VARRAYs in SQL*PLUS (please specify the Oracle version which supports
>the same).
>

thats one of the major differences between varrays and nested tables from a functional perspective -- varrays are not accessible on SQL unless you have coded methods in an object type that has a varray to access them (unless you wrote some pl/sql and call the pl/sql from SQL).

THE works on nested tables, not varrays.

Here is an example that models the classic emp/dept relationship as a DEPT object with a VARRAY of emps. In SQL, we want to query up the highest paid emp from the VARRAY, the average sal, and the total number of emps in the VARRAY. This shows you how to use PLSQL (or C if you want to use C and External procedures) to accomplish this:

SQL> create or replace type empType as object   2 ( empno number,

  3          ename    varchar2(10),
  4          job      varchar2(9),
  5          hiredate date,
  6          sal      number,
  7          comm     number

  8 );
  9 /
Type created.

SQL> create or replace type empVarrayType as varray(50) of empType;   2 /
Type created.

SQL> create or replace type deptType as object

  2  (       deptno  number,
  3          dname   varchar2(14),
  4          loc     varchar2(13),
  5          emps    empVarrayType,
  6  
  6          member function highest_paid return varchar2,
  7          pragma restrict_references(highest_paid,wnds,rnds,wnps,rnps),
  8  
  8          member function emp_cnt return number,
  9          pragma restrict_references(emp_cnt,wnds,rnds,wnps,rnps),
 10  
 10          member function avg_sal return number,
 11          pragma restrict_references(avg_sal,wnds,rnds,wnps,rnps)

 12 );
 13 /
Type created.

SQL> create or replace type body deptType   2 as
  3
  3 member function highest_paid return varchar2   4 is

  5          l_ename         varchar2(20) default null;
  6          l_highsal   number default 0;
  7  begin
  8          if ( emps is null or emps.count = 0 ) then
  9                  return NULL;
 10          end if;
 11          for i in emps.first..emps.last loop
 12                  if ( emps(i).sal > l_highsal ) then
 13                          l_ename := emps(i).ename;
 14                  end if;
 15          end loop;
 16          return l_ename;

 17 end highest_paid;
 18
 18
 18 member function emp_cnt return number  19 is
 20 begin

 21          if ( emps is null ) then
 22                  return NULL;
 23          end if;
 24          return emps.count;

 25 end emp_cnt;
 26
 26
 26 member function avg_sal return number  27 is
 28 l_salsum number default 0;  29 begin

 30          if ( emps is NULL or emps.count = 0 ) then
 31                  return NULL;
 32          end if;
 33  
 33          for i in emps.first..emps.last loop
 34                  l_salsum := emps(i).sal +l_salsum;
 35          end loop;
 36          return l_salsum/emps.count;

 37 end avg_sal;
 38
 38 end;
 39 /
Type body created.

SQL> create table dept_obj of deptType;
Table created.

SQL> insert into dept_obj
  2 values ( deptType( 50, 'NEW DEPT', 'NEW LOC',

  3      empVArrayType( empType( '1000', 'BOB', 'JOB1', sysdate, 400, 100),
  4                    empType( '1001', 'SUE', 'JOB2', sysdate, 500, 200),
  5                    empType( '1002', 'TOM', 'JOB3', sysdate, 550, 250) ) ) );

1 row created.

SQL> insert into dept_obj
  2 values ( deptType( 60, 'NEW DEPT 2', 'NEW LOC 2', NULL ) ); 1 row created.

SQL>
SQL> select d.deptno, d.dname, d.loc, d.highest_paid(), d.emp_cnt(), d.avg_sal()   2 from dept_obj d
  3 /

    DEPTNO DNAME LOC D.HIGHEST_ D.EMP_CNT() D.AVG_SAL() ---------- -------------- ------------- ---------- ----------- -----------

        50 NEW DEPT       NEW LOC       TOM                  3  483.333333
        60 NEW DEPT 2     NEW LOC 2

SQL>
>
>Also, do the Oracle JDBC thin drivers support the object features ?
>

don't know, I use stored procedures (pl/sql) for all database work so the calling language only needs to do sp's and doens't have to deal with the data itself so much...

>
>Thanks,
>
>Gunjeet
>
>
>
>In article 1_at_corpnews1.Corp.Sun.COM, gunjeet_at_sunsparkle.corp.sun.com (Gunjeet Singh) writes:
>> 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 ?
>>
>> ------------- Example deleted --------------
>>
>> Any examples will he very helpful.
>>
>> Thanks,
>>
>> Gunjeet
>>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Tue Sep 22 1998 - 00:00:00 CDT