Use table function on varray type value : table function « Object Oriented « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> column numlist format a60
SQL>
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;

Table created.

SQL>
SQL>
SQL> insert into employees values(1,'Jason',  'N',  'TRAINER', 2,   date '1965-12-18',  800 , NULL,  10);

1 row created.

SQL> insert into employees values(2,'Jerry',  'J',  'SALESREP',3,   date '1966-11-19',  1600, 300,   10);

1 row created.

SQL> insert into employees values(3,'Jord',   'T' , 'SALESREP',4,   date '1967-10-21',  1700, 500,   20);

1 row created.

SQL> insert into employees values(4,'Mary',   'J',  'MANAGER', 5,   date '1968-09-22',  1800, NULL,  20);

1 row created.

SQL> insert into employees values(5,'Joe',    'P',  'SALESREP',6,   date '1969-08-23',  1900, 1400,  30);

1 row created.

SQL> insert into employees values(6,'Black',  'R',  'MANAGER', 7,   date '1970-07-24',  2000, NULL,  30);

1 row created.

SQL> insert into employees values(7,'Red',    'A',  'MANAGER', 8,   date '1971-06-25',  2100, NULL,  40);

1 row created.

SQL> insert into employees values(8,'White',  'S',  'TRAINER', 9,   date '1972-05-26',  2200, NULL,  40);

1 row created.

SQL> insert into employees values(9,'Yellow', 'C',  'DIRECTOR',10,  date '1973-04-27',  2300, NULL,  20);

1 row created.

SQL> insert into employees values(10,'Pink',  'J',  'SALESREP',null,date '1974-03-28',  2400, 0,     30);

1 row created.

SQL>
SQL>
SQL>
SQL> create table e
  2  as
  3  select empno, ename, init, mgr, deptno
  4  from   employees;

Table created.

SQL>
SQL>
SQL> create or replace type numberVarray as varray(4) of varchar2(20);
  2  /

Type created.

SQL>
SQL>
SQL> alter table e add (numlist numberVarray);

Table altered.

SQL>
SQL> describe e;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(8)
 INIT                                               VARCHAR2(5)
 MGR                                                NUMBER(4)
 DEPTNO                                             NUMBER(2)
 NUMLIST                                            NUMBERVARRAY

SQL>
SQL>
SQL> update e set numlist = numberVarray('4231','06-12345678');

10 rows updated.

SQL>
SQL>
SQL> break on empno
SQL>
SQL> select empno, n.* from e, TABLE(e.numlist) n;

 EMPNO COLUMN_VALUE
------ --------------------
     1 4231
       06-12345678
     2 4231
       06-12345678
     3 4231
       06-12345678
     4 4231
       06-12345678
     5 4231
       06-12345678
     6 4231
       06-12345678
     7 4231
       06-12345678
     8 4231
       06-12345678
     9 4231
       06-12345678
    10 4231

 EMPNO COLUMN_VALUE
------ --------------------
    10 06-12345678

20 rows selected.

SQL>
SQL> select empno, numlist from e;
ERROR:
OCI-22303: type "SYS"."KOTAD" not found
OCI-21522: attempted to use an invalid connection in OCI (object mode only)


SQL>
SQL> drop table e;

Table dropped.

SQL>
SQL> drop table employees;

Table dropped.








32.19.table function
32.19.1.Use table function on varray type value
32.19.2.Table function with varray column
32.19.3.Table Functions with table of numbers
32.19.4.Table Functions involving Object Types
32.19.5.Pipelined Table Functions
32.19.6.Use table function to convert table collection to a table
32.19.7.Table function with aggregate function and group by
32.19.8.Update statement with table function