Oracle PL/SQL/PL SQL/varray

Материал из SQL эксперт
Перейти к: навигация, поиск

Содержание

assignments to varray elements, and the ORA-6532 and ORA-6533 errors.

   <source lang="sql">
   

SQL> SQL> SQL> DECLARE

 2    TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
 3    v_List Strings :=
 4      Strings("One", "Two", "Three", "Four");
 5  BEGIN
 6    v_List(2) := "TWO";
 7
 8    v_List.EXTEND;
 9    v_List(5) := "Five";
10
11    -- Attempt to extend the varray to 6 elements.  This will
12    -- raise ORA-6532 .
13    v_list.EXTEND;
14  END;
15  /

DECLARE

ERROR at line 1: ORA-06532: Subscript outside of limit ORA-06512: at line 13

SQL> SQL> SQL> SQL>


 </source>
   
  


Assigns a value to the indexed value.

   <source lang="sql">
  

SQL> SQL> DECLARE

 2    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 3    intArray INTEGER_VARRAY := integer_varray();
 4  BEGIN
 5    FOR i IN 1..3 LOOP
 6      intArray.EXTEND;
 7      intArray(i) := 10 + i;
 8    END LOOP;
 9
10    FOR i IN 1..3 LOOP
11      dbms_output.put     ("Integer Varray :"||i);
12      dbms_output.put_line(":"||intArray(i));
13    END LOOP;
14  END;
15  /

Integer Varray :1:11 Integer Varray :2:12 Integer Varray :3:13 PL/SQL procedure successfully completed.


 </source>
   
  


Assign values to subscripted members of the varray.

   <source lang="sql">
  

SQL> SQL> DECLARE

 2    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 3
 4    intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
 5  BEGIN
 6    intArray(1) := 11;
 7    intArray(2) := 12;
 8    intArray(3) := 13;
 9
10    dbms_output.put_line("Varray initialized as values.");
11    FOR i IN 1..3 LOOP
12      dbms_output.put_line("Integer Varray :"||i||":" ||intArray(i));
13    END LOOP;
14  END;
15  /

Varray initialized as values. Integer Varray :1:11 Integer Varray :2:12 Integer Varray :3:13 PL/SQL procedure successfully completed. SQL> SQL> SQL>


 </source>
   
  


Associative array example

   <source lang="sql">
  

SQL> CREATE OR REPLACE PROCEDURE myProcedure AS

 2    TYPE numTable IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
 3    carray numTable;
 4  BEGIN
 5    carray("J") := 1;
 6    carray("S") := 2;
 7    carray("F") := 3;
 8    carray("C") := 4;
 9
10    DBMS_OUTPUT.PUT_LINE("carray[""J""] = " || carray("J"));
11    DBMS_OUTPUT.PUT_LINE("carray[""S""] = " || carray("S"));
12    DBMS_OUTPUT.PUT_LINE("carray[""F""] = " || carray("F"));
13    DBMS_OUTPUT.PUT_LINE("carray[""C""] = " || carray("C"));
14  END myProcedure;
15  /

Procedure created. SQL>


 </source>
   
  


Avoid traversing an associative array where no elements are initialized.

   <source lang="sql">
   

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2
 3    
 4    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER;
 5
 6    
 7    charArray charArrayType;
 8
 9  BEGIN
10
11    IF charArray.COUNT <> 0 THEN
12      
13      DBMS_OUTPUT.PUT_LINE(charArray(1));
14    ELSE
15      
16      DBMS_OUTPUT.PUT_LINE("empty.");
17
18    END IF;
19
20  END;
21  /

empty. PL/SQL procedure successfully completed. SQL> SQL>



 </source>
   
  


Check the size of a varray

   <source lang="sql">
  

SQL> SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;

 2  /

Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS

 2    returnValue VARCHAR2(2000);
 3  BEGIN
 4      FOR i IN set_in.FIRST..set_in.LAST LOOP
 5        IF i = set_in.FIRST THEN
 6          IF set_in.COUNT = 1 THEN
 7            returnValue := set_in(i);
 8          ELSE
 9            returnValue := ":"||set_in(i);
10          END IF;
11        ELSIF i <> set_in.LAST THEN
12          returnValue := returnValue||", "||set_in(i);
13        ELSE
14          returnValue := returnValue||", "||set_in(i)||")";
15        END IF;
16      END LOOP;
17    RETURN returnValue;
18  END format_list;
19  /

Function created. SQL>


 </source>
   
  


Compare two varray variables

   <source lang="sql">
  

SQL> CREATE or replace TYPE addressType AS OBJECT (

 2    street VARCHAR2(15),
 3    city   VARCHAR2(15),
 4    state  CHAR(2),
 5    zip    VARCHAR2(5)
 6  );
 7  /

Type created. SQL> SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);

 2  /

SQL> SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;

 2  /

Type created. SQL> SQL> -- SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (

 2    id         INTEGER PRIMARY KEY,
 3    fname VARCHAR2(10),
 4    lname  VARCHAR2(10),
 5    addresses  addressTypeVArray
 6  );

Table created. SQL> SQL> CREATE TABLE empTable (

 2    id         INTEGER PRIMARY KEY,
 3    fname VARCHAR2(10),
 4    lname  VARCHAR2(10),
 5    addresses  addressTypeNestedTable
 6  )
 7  NESTED TABLE
 8    addresses
 9  STORE AS
10    nested_addresses2 TABLESPACE users;

Table created. SQL> SQL> SQL> -- equal/not equal example SQL> CREATE OR REPLACE PROCEDURE equal_example AS

 2    TYPE charTable IS TABLE OF VARCHAR2(10);
 3    emp1 charTable;
 4    emp2 charTable;
 5    emp3 charTable;
 6    result BOOLEAN;
 7  BEGIN
 8    emp1 := charTable("A", "B", "C");
 9    emp2 := charTable("A", "B", "C");
10    emp3 := charTable("B", "C", "D");
11
12    result := emp1 = emp2;
13    IF result THEN
14      DBMS_OUTPUT.PUT_LINE("emp1 equal to emp2");
15    END IF;
16
17  END equal_example;
18  /

Procedure created. SQL> SQL> drop type addressType force; Type dropped. SQL> drop type addressTypeVArray force; Type dropped. SQL> drop TYPE addressTypeNestedTable force; Type dropped. SQL> drop TABLE empTable; Table dropped.


 </source>
   
  


Constructs a two varrays and one nested table type in the database

   <source lang="sql">
  

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE unitType

 2    AS VARRAY(13) OF VARCHAR2(5 CHAR);
 3  /

Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE categoryType

 2    AS VARRAY(4) OF VARCHAR2(8 CHAR);
 3  /

Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE charArrayType

 2    AS TABLE OF VARCHAR2(17 CHAR);
 3  /

Type created. SQL> SQL> DECLARE

 2
 3    counter INTEGER := 0;
 4
 5    
 6    suits categoryType :=categoryType("A","B","C","D");
 7
 8    
 9    units unitType :=unitType("Ace","Two","Three","Four","Five","Six","Seven");
10
11    
12    charArray charArrayType := charArrayType();
13
14  BEGIN
15    FOR i IN 1..suits.COUNT LOOP
16      FOR j IN 1..units.COUNT LOOP
17
18        counter := counter + 1;
19        charArray.EXTEND;
20
21        
22        charArray(counter) := units(j)||" of "||suits(i);
23
24      END LOOP;
25
26    END LOOP;
27
28    FOR i IN 1..counter LOOP
29      dbms_output.put_line("["||charArray(i)||"]");
30    END LOOP;
31
32  END;
33  /

[Ace of A] [Two of A] [Three of A] [Four of A] [Five of A] [Six of A] [Seven of A] [Ace of B] [Two of B] [Three of B] [Four of B] [Five of B] [Six of B] [Seven of B] [Ace of C] [Two of C] [Three of C] [Four of C] [Five of C] [Six of C] [Seven of C] [Ace of D] [Two of D] [Three of D] [Four of D] [Five of D] [Six of D] [Seven of D] PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Control varray index with if statement

   <source lang="sql">
  

SQL> SQL> create table product(

 2          product_id          integer     primary key
 3          ,price                  number(7,2)
 4          ,description            varchar2(75)
 5          ,onhand                 number(5,0)
 6          ,reorder                number(5,0)
 7          ,supplier_no            integer
 8  );

Table created. SQL> -- product Table Inserts: SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"PC",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"Disk",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"Monitor",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"Mouse",50,10); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Vase",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"Keyboard",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Cable",3,5); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Notebook",null,null); 1 row created. SQL> SQL> CREATE SEQUENCE Product_seq

 2  INCREMENT BY 1
 3  START WITH 9
 4  NOMAXVALUE
 5  NOCYCLE;

Sequence created. SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)

 2  AS
 3      i  number := 1;
 4      idIndex  number := 1;
 5      newOrder   product.reorder%TYPE;
 6
 7      TYPE names IS VARRAY(10) OF VARCHAR2(75);
 8      v_names names := names("A","B","C","D","E","F","G","H","I","J");
 9
10      TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2);
11      v_prices    Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5);
12
13      TYPE Prod_Onhand IS VARRAY(10) OF NUMBER;
14      v_onhand    Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55);
15
16  begin
17
18      WHILE i <= v_ctr LOOP
19          IF idIndex > 10 THEN
20              idIndex := 1;
21          END IF;
22
23          IF v_onhand(idIndex) >= 30 THEN
24             newOrder := v_onhand(idIndex) - 1;
25          ELSE
26             newOrder := v_onhand(idIndex) + 5;
27          END IF;
28
29          INSERT INTO product (PRODUCT_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO)
30          VALUES (product_seq.NEXTVAL, v_prices(idIndex), v_names(idIndex), v_onhand(idIndex), newOrder, v_supplier);
31
32          i := i + 1 ;
33          idIndex := idIndex + 1;
34
35      END LOOP;
36  end;
37  /

Procedure created. SQL> SQL> SQL> drop table product; Table dropped. SQL> drop sequence product_seq; Sequence dropped. SQL> SQL>


 </source>
   
  


Create a varray based on user defined type

   <source lang="sql">
   

SQL> SQL> SQL> create type employee_type as object (

 2      employee_id       number,
 3      first_name        varchar2(30),
 4      last_name         varchar2(30)
 5    );
 6    /

Type created. SQL> SQL> create type employee_list_type as varray(50) of employee_type

 2    /

Type created. SQL> SQL> SQL> create table departments (

 2      department_id   number,
 3      department_name varchar2(30),
 4      manager         employee_type,
 5      employees       employee_list_type )
 6    /

Table created. SQL> SQL> SQL> SQL> drop table departments; Table dropped. SQL> drop type employee_list_type; Type dropped. SQL> drop type employee_type; Type dropped.


 </source>
   
  


Create type prices with a varray of number

   <source lang="sql">
  

SQL> CREATE OR REPLACE TYPE prices AS VARRAY(20) OF

 2   NUMBER(12,2)
 3   /

Type created. SQL> SQL> SQL> SQL>


 </source>
   
  


Creating and Using VARRAYs

   <source lang="sql">
   

SQL> SQL> create type employee_type as object (

 2    employee_id       number,
 3    first_name        varchar2(30),
 4    last_name         varchar2(30)
 5  );
 6  /

Type created. SQL> SQL> create type employee_list_type as varray(50) of employee_type;

 2  /

Type created. SQL> create table departments (

 2    department_id   number,
 3    department_name varchar2(30),
 4    manager         employee_type,
 5    employees       employee_list_type );

Table created. SQL> SQL> insert into departments ( department_id,

 2                            department_name,
 3                            manager,
 4                            employees )
 5  values ( 10,
 6           "Accounting",
 7           employee_type( 1, "Danielle", "Steeger" ),
 8           employee_list_type(
 9             employee_type( 2, "Madison", "Sis" ),
10             employee_type( 3, "Robert", "Cabove" ),
11             employee_type( 4, "Michelle", "Sechrist" ))
12  );

1 row created. SQL> SQL> SQL> insert into departments ( department_id,

 2                            department_name,
 3                            manager,
 4                            employees )
 5  values ( 20,
 6           "Research",
 7           employee_type( 11, "Ricky", "Lil" ),
 8           employee_list_type(
 9             employee_type( 12, "Ricky", "Ricardo" ),
10             employee_type( 13, "Lucy", "Ricardo" ),
11             employee_type( 14, "Fred", "Mertz" ),
12             employee_type( 15, "Ethel", "Mertz" ))
13  );

1 row created. SQL> SQL> column department_name format a13 SQL> column employees format a63 word_wrapped SQL> select department_name, employees

 2    from departments;

DEPARTMENT_NA EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME)


---------------------------------------------------------------

Accounting EMPLOYEE_LIST_TYPE(EMPLOYEE_TYPE(2, "Madison", "Sis"),

             EMPLOYEE_TYPE(3, "Robert", "Cabove"), EMPLOYEE_TYPE(4,
             "Michelle", "Sechrist"))

Research EMPLOYEE_LIST_TYPE(EMPLOYEE_TYPE(12, "Ricky", "Ricardo"),

             EMPLOYEE_TYPE(13, "Lucy", "Ricardo"), EMPLOYEE_TYPE(14, "Fred",
             "Mertz"), EMPLOYEE_TYPE(15, "Ethel", "Mertz"))

SQL> SQL> SQL> drop table departments; Table dropped. SQL> SQL> drop type employee_list_type; Type dropped. SQL> SQL> drop type employee_type; Type dropped. SQL> SQL>


 </source>
   
  


Declare an array initialized as a no-element collection.

   <source lang="sql">
  

SQL> SQL> SQL> DECLARE

 2    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 3    intArray INTEGER_VARRAY := integer_varray();
 4  BEGIN
 5    FOR i IN 1..3 LOOP
 6      dbms_output.put     ("Integer Varray:"||i);
 7      dbms_output.put_line(":"||intArray(i));
 8    END LOOP;
 9  END;
10  /

DECLARE

ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 7


 </source>
   
  


Declare the varray with null values.

   <source lang="sql">
  

SQL> SQL> DECLARE

 2    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 3    intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
 4  BEGIN
 5    dbms_output.put_line("Varray initialized as nulls.");
 6    FOR i IN 1..3 LOOP
 7      dbms_output.put     ("Integer Varray:"||i);
 8      dbms_output.put_line(":"||intArray(i));
 9    END LOOP;
10
11  END;
12  /

Varray initialized as nulls. Integer Varray:1: Integer Varray:2: Integer Varray:3: PL/SQL procedure successfully completed.


 </source>
   
  


Declaring a VARRAY of scalar variable

   <source lang="sql">
  

SQL> SQL> DECLARE

 2      TYPE number_varray IS VARRAY(10) OF NUMBER;
 3      list NUMBER_VARRAY := number_varray(1,2,3,4,5,6,7,8,NULL,NULL);
 4  BEGIN
 5      FOR i IN 1..list.LIMIT LOOP
 6        dbms_output.put("["||list(i)||"]");
 7      END LOOP;
 8      dbms_output.new_line;
 9  END;
10  /

[1][2][3][4][5][6][7][8][][] PL/SQL procedure successfully completed.


 </source>
   
  


Define a varray of integer with 3 rows

   <source lang="sql">
  

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2
 3    
 4    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 5
 6    
 7    intArray INTEGER_VARRAY :=
 8      integer_varray(NULL,NULL,NULL);
 9
10  BEGIN
11
12
13    FOR i IN 1..3 LOOP
14
15      dbms_output.put     ("Integer Varray :"||i);
16      dbms_output.put_line(":"||intArray(i));
17
18    END LOOP;
19
20    intArray(1) := 11;
21    intArray(2) := 12;
22    intArray(3) := 13;
23
24
25    FOR i IN 1..3 LOOP
26      dbms_output.put_line("Integer Varray :"||i||", "||intArray(i));
27    END LOOP;
28
29  END;
30  /

Integer Varray :1: Integer Varray :2: Integer Varray :3: Integer Varray :1, 11 Integer Varray :2, 12 Integer Varray :3, 13 PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Define a varray of twelve strings.

   <source lang="sql">
  

SQL> SQL> DECLARE

 2    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
 3
 4  BEGIN
 5    null;
 6  END;
 7  /

PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Define a varray with a null element constructor and extends it one element at a time.

   <source lang="sql">
  

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2
 3    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 4
 5    intArray INTEGER_VARRAY := integer_varray();
 6
 7  BEGIN
 8
 9    FOR i IN 1..3 LOOP
10
11      intArray.EXTEND;
12
13      intArray(i) := 10 + i;
14
15    END LOOP;
16
17    FOR i IN 1..3 LOOP
18
19      dbms_output.put     ("Integer Varray :"||i);
20      dbms_output.put_line(":"||intArray(i));
21
22    END LOOP;
23
24  END;
25  /

Integer Varray :1:11 Integer Varray :2:12 Integer Varray :3:13 PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


Define a varray with a null element constructor and extends it one element at a time by a formula

   <source lang="sql">
  

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE integer_varray

 2    AS VARRAY(100) OF INTEGER NOT NULL;
 3  /

Type created. SQL> SQL> DECLARE

 2
 3    
 4    intArray INTEGER_VARRAY := integer_varray();
 5
 6  BEGIN
 7
 8    
 9    FOR i IN 1..intArray.LIMIT LOOP
10
11      
12      intArray.EXTEND;
13
14    END LOOP;
15
16      dbms_output.put_line(intArray.COUNT);
17
18  END;
19  /

100 PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Define a varray with a three element constructor of null elements.

   <source lang="sql">
  

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2
 3    
 4    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 5
 6    
 7    intArray INTEGER_VARRAY :=
 8      integer_varray(NULL,NULL,NULL);
 9
10  BEGIN
11
12
13    FOR i IN 1..3 LOOP
14
15      dbms_output.put     ("Integer Varray :"||i);
16      dbms_output.put_line(":"||intArray(i));
17
18    END LOOP;
19
20    intArray(1) := 11;
21    intArray(2) := 12;
22    intArray(3) := 13;
23
24
25    FOR i IN 1..3 LOOP
26      dbms_output.put_line("Integer Varray :"||i||", "||intArray(i));
27    END LOOP;
28
29  END;
30  /

Integer Varray :1: Integer Varray :2: Integer Varray :3: Integer Varray :1, 11 Integer Varray :2, 12 Integer Varray :3, 13 PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Define a varray with a three element constructor of null elements and attempt to populate it beyond three elements.

   <source lang="sql">
  

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE integer_varray

 2    AS VARRAY(3) OF INTEGER;
 3  /

Type created. SQL> SQL> DECLARE

 2
 3    
 4    intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
 5
 6  BEGIN
 7
 8    FOR i IN 1..3 LOOP
 9
10      intArray(i) := 10 + i;
11
12    END LOOP;
13
14
15    
16    FOR i IN 1..3 LOOP
17
18      dbms_output.put     ("Integer Varray ["||i||"] ");
19      dbms_output.put_line("["||intArray(i)||"]");
20
21    END LOOP;
22
23  END;
24  /

Integer Varray [1] [11] Integer Varray [2] [12] Integer Varray [3] [13] PL/SQL procedure successfully completed. SQL>


 </source>
   
  


exceeded maximum VARRAY limit

   <source lang="sql">
  

SQL> CREATE or replace TYPE phoneVArray AS VARRAY(3) OF VARCHAR2(14);

 2  /

Type created. SQL> SQL> CREATE or replace TYPE addressType AS OBJECT (

 2    street        VARCHAR2(15),
 3    city          VARCHAR2(15),
 4    state         CHAR(2),
 5    zip           VARCHAR2(5),
 6    phone_numbers phoneVArray
 7  );
 8  /

Type created. SQL> SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;

 2  /

Type created. SQL> SQL> SQL> -- create the tables SQL> SQL> CREATE TABLE emp (

 2    id         INTEGER PRIMARY KEY,
 3    fname VARCHAR2(10),
 4    lname  VARCHAR2(10),
 5    addresses  addressTypeNestedTable
 6  )
 7  NESTED TABLE
 8    addresses
 9  STORE AS
10    nested_addresses;

Table created. SQL> SQL> INSERT INTO emp VALUES (

 2    1, "Steve", "Brown",
 3    addressTypeNestedTable(
 4      addressType("2 Street", "town", "MA", "12345",
 5        phoneVArray(
 6          "(800)-555-1211",
 7          "(800)-555-1213"
 8        )
 9      ),
10      addressType("4 Street", "L Town", "CA", "54321",
11        phoneVArray(
12          "(800)-555-1211",
13          "(800)-555-1212",
14          "(800)-555-1213",
15          "(800)-555-1214"
16        )
17      )
18    )
19  );
     phoneVArray(
     *

ERROR at line 11: ORA-22909: exceeded maximum VARRAY limit

SQL> SQL> SQL> drop type addressTypeNestedTable force; Type dropped. SQL> drop type addressType force; Type dropped. SQL> drop type phoneVArray force; Type dropped. SQL> drop TABLE emp; Table dropped.


 </source>
   
  


Extend with null element to the maximum limit size.

   <source lang="sql">
  

SQL> SQL> DECLARE

 2    TYPE number_varray IS VARRAY(5) OF INTEGER;
 3    number_list NUMBER_VARRAY := number_varray(1,2,3);
 4
 5    PROCEDURE print_list(list_in NUMBER_VARRAY) IS
 6    BEGIN
 7      FOR i IN list_in.FIRST..list_in.COUNT LOOP
 8        DBMS_OUTPUT.PUT_LINE("List Index ["||i||"] "||"List Value ["||list_in(i)||"]");
 9      END LOOP;
10    END print_list;
11  BEGIN
12    print_list(number_list);
13
14    number_list.EXTEND(number_list.LIMIT - number_list.LAST);
15
16    print_list(number_list);
17  END;
18  /

List Index [1] List Value [1] List Index [2] List Value [2] List Index [3] List Value [3] List Index [1] List Value [1] List Index [2] List Value [2] List Index [3] List Value [3] List Index [4] List Value [] List Index [5] List Value [] PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Hard code value in varray and use for loop to insert them to a table

   <source lang="sql">
  

SQL> create table gift(

 2          gift_id          integer     primary key
 3          ,price                  number(7,2)
 4          ,description            varchar2(75)
 5          ,onhand                 number(5,0)
 6          ,reorder                number(5,0)
 7          ,supplier_no            integer
 8  );

Table created. SQL> -- gift Table Inserts: SQL> insert into gift(gift_id, price, description, onhand, reorder)values (1,2.50,"Happy Birthday",100,20); 1 row created. SQL> insert into gift(gift_id, price, description, onhand, reorder)values (2,23.00,"Happy Birthday",null,null); 1 row created. SQL> insert into gift(gift_id, price, description, onhand, reorder)values (3,null,"Happy New Year",null,null); 1 row created. SQL> insert into gift(gift_id, price, description, onhand, reorder)values (4,1.50,"Happy New Year",50,10); 1 row created. SQL> SQL> SQL> CREATE SEQUENCE gift_seq

 2  INCREMENT BY 1
 3  START WITH 9
 4  NOMAXVALUE
 5  NOCYCLE;

Sequence created. SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)

 2  AS
 3      i  number := 1;
 4      idIndex  number := 1;
 5      newOrder   gift.reorder%TYPE;
 6
 7      TYPE names IS VARRAY(10) OF VARCHAR2(75);
 8      v_names names := names("A","B","C","D","E","F","G","H","I","J");
 9
10      TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2);
11      v_prices    Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5);
12
13      TYPE Prod_Onhand IS VARRAY(10) OF NUMBER;
14      v_onhand    Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55);
15
16  begin
17
18      WHILE i <= v_ctr LOOP
19          IF idIndex > 10 THEN
20              idIndex := 1;
21          END IF;
22
23          IF v_onhand(idIndex) >= 30 THEN
24             newOrder := v_onhand(idIndex) - 10;
25          ELSE
26             newOrder := v_onhand(idIndex) - 5;
27          END IF;
28
29          INSERT INTO gift (gift_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO)
30          VALUES (gift_seq.NEXTVAL, v_prices(idIndex), v_names(idIndex), v_onhand(idIndex), newOrder, v_supplier);
31
32          i := i + 1 ;
33          idIndex := idIndex + 1;
34
35      END LOOP;
36  end;
37  /

Procedure created. SQL> SQL> show errors No errors. SQL> SQL> drop SEQUENCE gift_seq; Sequence dropped. SQL> SQL> drop table gift; Table dropped. SQL> SQL>


 </source>
   
  


Initialization and assignment with a numeric index value to an associative array.

   <source lang="sql">
  

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2
 3    
 4    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
 5
 6    
 7    TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
 8      INDEX BY VARCHAR2(9 CHAR);
 9
10    
11    month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August");
12
13    
14    calendar CALENDAR_TABLE;
15
16  BEGIN
17
18    
19    IF calendar.COUNT = 0 THEN
20
21      
22      FOR i IN month.FIRST..month.LAST LOOP
23
24        
25        
26        calendar(month(i)) := "";
27
28        
29        DBMS_OUTPUT.PUT_LINE("Index :"||month(i)||" is "||i);
30
31      END LOOP;
32
33      
34      FOR i IN calendar.FIRST..calendar.LAST LOOP
35        
36        DBMS_OUTPUT.PUT_LINE("Index :"||i||" is "||calendar(i));
37
38      END LOOP;
39
40    END IF;
41
42  END;
43  /

Index :January is 1 Index :February is 2 Index :March is 3 Index :April is 4 Index :May is 5 Index :June is 6 Index :July is 7 Index :August is 8

SQL>


 </source>
   
  


Initialization and assignment with a unique string index value to an associative array.

   <source lang="sql">
  

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE

 2
 3    current VARCHAR2(9 CHAR);
 4    element INTEGER;
 5
 6    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
 7
 8    TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)INDEX BY VARCHAR2(9 CHAR);
 9
10    month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August");
11
12    calendar CALENDAR_TABLE;
13
14  BEGIN
15
16    IF calendar.COUNT = 0 THEN
17
18      FOR i IN month.FIRST..month.LAST LOOP
19
20        calendar(month(i)) := TO_CHAR(i);
21
22        DBMS_OUTPUT.PUT_LINE("Index :"||month(i)||" is "||i);
23
24      END LOOP;
25      FOR i IN 1..calendar.COUNT LOOP
26
27        IF i = 1 THEN
28
29          current := calendar.FIRST;
30
31          element := calendar(current);
32
33        ELSE
34
35          IF calendar.NEXT(current) IS NOT NULL THEN
36
37            current := calendar.NEXT(current);
38
39            element := calendar(current);
40
41          ELSE
42
43            EXIT;
44
45          END IF;
46
47        END IF;
48
49        DBMS_OUTPUT.PUT_LINE("Index :"||current||" is "||element);
50
51      END LOOP;
52
53    END IF;
54
55  END;
56  /

Index :January is 1 Index :February is 2 Index :March is 3 Index :April is 4 Index :May is 5 Index :June is 6 Index :July is 7 Index :August is 8 Index :April is 4 Index :August is 8 Index :February is 2 Index :January is 1 Index :July is 7 Index :June is 6 Index :March is 3 Index :May is 5 PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Initialize the array and create two entries using the constructor

   <source lang="sql">
   

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2      TYPE dept_array IS VARRAY(100) OF VARCHAR2(30);
 3
 4      depts dept_array;
 5      inx1 PLS_INTEGER;
 6
 7  BEGIN
 8      depts := dept_array ("Dept One","Dept Two");
 9
10      FOR inx1 IN 1..2 LOOP
11          DBMS_OUTPUT.PUT_LINE(depts(inx1));
12      END LOOP;
13  END;
14  /

Dept One Dept Two PL/SQL procedure successfully completed. SQL> SQL> SQL> --


 </source>
   
  


legal and illegal varray assignments.

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
 3
 4    v_List Strings := Strings("One", "Two", "Three");
 5  BEGIN
 6    v_List(2) := "TWO";
 7
 8    -- Subscript beyond count, raises ORA-6533.
 9    v_List(4) := "!!!";
10  END;
11  /

DECLARE

ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 9

SQL> SQL>


 </source>
   
  


Nested varray

   <source lang="sql">
  

SQL> SQL> SQL> DECLARE

 2      TYPE x IS VARRAY(30) OF INTEGER;
 3      TYPE y IS VARRAY(20) OF x;
 4
 5      xArray x := x(234,2,3,4);   
 6      yArray y := y( x(0), xArray, x(4,5,3,4) ); 
 7  BEGIN
 8      dbms_output.put_line(yArray(3)(2) );
 9  END;
10  /

5 PL/SQL procedure successfully completed. SQL>


 </source>
   
  


ORA-06533: Subscript beyond count

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
 3
 4    v_List Strings := Strings("S", "D", "U");
 5  BEGIN
 6    v_List(2) := "DAVID";
 7
 8    v_List(4) := "!!!";
 9  END;
10  /

DECLARE

ERROR at line 1: ORA-06512: at line 8

SQL>


 </source>
   
  


Query a stored varray.

   <source lang="sql">
   

SQL> SQL> CREATE OR REPLACE TYPE BookList AS VARRAY(10) OF NUMBER(4);

 2  /

Type created. SQL> SQL> SQL> CREATE TABLE class_material (

 2    department       CHAR(3),
 3    course           NUMBER(3),
 4    required_reading BookList
 5  );

Table created. SQL> SQL> CREATE TABLE books (

 2    catalog_number NUMBER(4)     PRIMARY KEY,
 3    title          VARCHAR2(40),
 4    author1        VARCHAR2(40),
 5    author2        VARCHAR2(40),
 6    author3        VARCHAR2(40),
 7    author4        VARCHAR2(40)
 8  );

Table created. SQL> SQL> INSERT INTO books (catalog_number, title, author1)

 2             VALUES (1000, "Oracle8i Advanced PL/SQL Programming", "Urman, Scott");

1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1, author2, author3)

 2             VALUES (1001, "Oracle8i: A Beginner""s Guide", "Abbey, Michael", "Corey, Michael J.", "Abramson, Ian");

1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1, author2, author3, author4)

 2             VALUES (1002, "Oracle8 Tuning", "Corey, Michael J.", "Abbey, Michael", "Dechichio, Daniel J.", "Abramson, Ian");

1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1, author2)

 2             VALUES (2001, "A History of the World", "Arlington, Arlene", "Verity, Victor");

1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1)

 2             VALUES (3001, "Bach and the Modern World", "Foo, Fred");

1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1)

 2             VALUES (3002, "Introduction to the Piano", "Morenson, Mary");

1 row created. SQL> CREATE OR REPLACE PROCEDURE PrintRequired(

 2    p_Department IN class_material.department%TYPE,
 3    p_Course IN class_material.course%TYPE) IS
 4
 5    v_Books class_material.required_reading%TYPE;
 6    v_Title books.title%TYPE;
 7  BEGIN
 8    SELECT required_reading
 9      INTO v_Books
10      FROM class_material
11      WHERE department = p_Department
12      AND course = p_Course;
13
14    DBMS_OUTPUT.PUT("Required reading for " || RTRIM(p_Department));
15    DBMS_OUTPUT.PUT_LINE(" " || p_Course || ":");
16
17    FOR v_Index IN 1..v_Books.COUNT LOOP
18      SELECT title
19        INTO v_Title
20        FROM books
21        WHERE catalog_number = v_Books(v_Index);
22      DBMS_OUTPUT.PUT_LINE(
23        "  " || v_Books(v_Index) || ": " || v_Title);
24    END LOOP;
25  END PrintRequired;
26  /

Procedure created. SQL> SQL> DECLARE

 2    CURSOR c_Courses IS
 3      SELECT department, course
 4        FROM class_material
 5        ORDER BY department;
 6  BEGIN
 7    FOR v_Rec IN c_Courses LOOP
 8      PrintRequired(v_Rec.department, v_Rec.course);
 9    END LOOP;
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> drop table class_material; Table dropped. SQL> SQL> drop table books; Table dropped. SQL> SQL> --Overloading packaged subprograms based on object types. CREATE OR REPLACE TYPE t11 AS OBJECT (

 f NUMBER

) / CREATE OR REPLACE TYPE t21 AS OBJECT (

 f NUMBER

) / CREATE OR REPLACE PACKAGE Overload AS

 PROCEDURE Proc(p_Parameter1 IN t11);
 PROCEDURE Proc(p_Parameter1 IN t21);

END Overload; / CREATE OR REPLACE PACKAGE BODY Overload AS

 PROCEDURE Proc(p_Parameter1 IN t11) IS
 BEGIN
   DBMS_OUTPUT.PUT_LINE("Proc(t11): " || p_Parameter1.f);
 END Proc;
 PROCEDURE Proc(p_Parameter1 IN t21) IS
 BEGIN
   DBMS_OUTPUT.PUT_LINE("Proc(t21): " || p_Parameter1.f);
 END Proc;

END Overload; / set serveroutput on DECLARE

 v_Obj1 t11 := t11(1);
 v_Obj2 t21 := t21(2);

BEGIN

 Overload.Proc(v_Obj1);
 Overload.Proc(v_Obj2);

END; / drop type t11; drop type t21; --


 </source>
   
  


Reference elements in varray

   <source lang="sql">
   

SQL> SQL> create table department

 2  ( dept_id       number(2),
 3    dept_name     varchar2(14),
 4    no_of_emps    varchar2(13)
 5  )
 6  /

Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2      CURSOR all_depts IS SELECT * FROM department ORDER BY dept_name;
 3
 4      TYPE dept_array IS VARRAY(100) OF department%ROWTYPE;
 5
 6      depts dept_array;
 7      inx1 PLS_INTEGER;
 8      inx2 PLS_INTEGER;
 9  BEGIN
10      inx1 := 0;
11
12      depts := dept_array ();
13
14      FOR dept IN all_depts LOOP
15          inx1 := inx1 + 1;
16          depts.extend();
17          depts(inx1).dept_id := dept.dept_id;
18          depts(inx1).dept_name := dept.dept_name;
19          depts(inx1).no_of_emps := dept.no_of_emps;
20      END LOOP;
21
22      FOR inx2 IN 1..depts.count LOOP
23          DBMS_OUTPUT.PUT_LINE (
24              depts(inx2).dept_id ||
25              " " || depts(inx2).dept_name);
26      END LOOP;
27  END;
28  /

10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES PL/SQL procedure successfully completed. SQL> SQL> drop table department; Table dropped. SQL> SQL> --


 </source>
   
  


Reference varray.count in for loop

   <source lang="sql">
  

SQL> CREATE OR REPLACE TYPE unitType AS VARRAY(13) OF VARCHAR2(5 CHAR);

 2  /

Type created. SQL> CREATE OR REPLACE TYPE categoryType AS VARRAY(4) OF VARCHAR2(8 CHAR);

 2  /

Type created. SQL> CREATE OR REPLACE TYPE charArrayType AS TABLE OF VARCHAR2(17 CHAR);

 2  /

Type created. SQL> SQL> DECLARE

 2    counter INTEGER := 0;
 3
 4    suits categoryType :=categoryType("A","B","C","D");
 5    units unitType :=unitType("1","2","3","4","Five","Six","Seven");
 6
 7    charArray charArrayType := charArrayType();
 8  BEGIN
 9
10    FOR i IN 1..suits.COUNT LOOP
11      FOR j IN 1..units.COUNT LOOP
12        counter := counter + 1;
13        charArray.EXTEND;
14        charArray(counter) := units(j)||" of "||suits(i);
15      END LOOP;
16    END LOOP;
17
18  END;
19  /

PL/SQL procedure successfully completed.


 </source>
   
  


Store 12 months in varray of string

   <source lang="sql">
  

SQL> DECLARE

 2    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
 3
 4    TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER;
 5
 6    month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August","September","October","November","December");
 7
 8    calendar CALENDAR_TABLE;
 9  BEGIN
10    IF calendar.COUNT = 0 THEN
11      FOR i IN month.FIRST..month.LAST LOOP
12        calendar(i) := "";
13        DBMS_OUTPUT.PUT_LINE(i||" is "||calendar(i));
14        calendar(i) := month(i);
15      END LOOP;
16
17
18    END IF;
19  END;
20  /

1 is 2 is 3 is 4 is 5 is 6 is 7 is 8 is 9 is 10 is 11 is 12 is PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Store pre-defined constants in VARRAY

   <source lang="sql">
   

SQL> SQL> create table ord(

 2           order_no               integer
 3          ,cust_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_orders (v_ord_ctr IN number, v_item_ctr IN number,

 2  v_cust_no IN number, v_emp_no IN number)
 3  AS
 4     v_loop  number := 1;
 5     v_type_ctr      number := 1;
 6     v_curr_order ord.order_no%TYPE;
 7
 8     TYPE PayMethods IS VARRAY(10) OF VARCHAR2(2);
 9     v_paymethods    PayMethods := PayMethods("VS","CA","VG","AX","CK","MC","DI","CA","CK","VS");
10
11     TYPE Orderdates IS VARRAY(10) OF DATE;
12     v_odates        Orderdates :=  Orderdates(add_months(sysdate, -45),
13                                                     add_months(sysdate, -14),
14                                                     add_months(sysdate, -22),
15                                                     add_months(sysdate, -38),
16                                                     add_months(sysdate, -46),
17                                                     add_months(sysdate, -59),
18                                                     add_months(sysdate, -19),
19                                                     add_months(sysdate, -11),
20                                                     add_months(sysdate, -74),
21                                                     add_months(sysdate, -6));
22  begin
23             WHILE v_loop <= v_ord_ctr LOOP
24                     IF v_type_ctr > 10 THEN
25                        v_type_ctr := 1;
26                     END IF;
27
28                     INSERT INTO ord (ORDER_NO, CUST_NO, ORDER_DATE, TOTAL_ORDER_PRICE, DELIVER_DATE,
29                                      PAYMENT_METHOD, EMP_NO)
30                     VALUES (999, v_cust_no, v_odates(v_type_ctr), 0, v_odates(v_type_ctr) + 10,
31                               v_paymethods(v_type_ctr), v_emp_no );
32
33                     SELECT 11111
34                       INTO v_curr_order
35                       FROM dual ;
36
37                     v_loop := v_loop + 1 ;
38                     v_type_ctr := v_type_ctr + 1 ;
39             END LOOP;
40  end;
41  /

Procedure created. SQL> SQL> show error No errors. SQL> SQL> SQL> drop table ord; Table dropped. SQL> SQL> SQL> --


 </source>
   
  


Subscript index values begin at 1, not 0

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    
 3    TYPE integer_varray IS VARRAY(3) OF INTEGER;
 4
 5    
 6    intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
 7  BEGIN
 8    
 9
10    FOR i IN 1..3 LOOP
11      dbms_output.put     ("Integer Varray:"||i);
12      dbms_output.put_line(":"||intArray(i));
13    END LOOP;
14
15  END;
16  /

Integer Varray:1: Integer Varray:2: Integer Varray:3: PL/SQL procedure successfully completed.



 </source>
   
  


Table of numbers and varray of numbers

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    TYPE t_IndexBy IS TABLE OF NUMBER
 3      INDEX BY BINARY_INTEGER;
 4    TYPE t_Nested IS TABLE OF NUMBER;
 5    TYPE t_Varray IS VARRAY(10) OF NUMBER;
 6
 7    v_IndexBy t_IndexBy;
 8    v_Nested t_Nested;
 9    v_Varray t_Varray;
10  BEGIN
11    v_IndexBy(1) := 1;
12    v_IndexBy(2) := 2;
13    v_Nested := t_Nested(1, 2, 3, 4, 5);
14    v_Varray := t_Varray(1, 2);
15  END;
16  /

PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


TYPE Strings IS VARRAY(5) OF VARCHAR2(10)

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
 3    v_List Strings := Strings("One", "Two", "Three", "Four");
 4  BEGIN
 5    v_List(2) := "TWO";
 6
 7    v_List.EXTEND;
 8    v_List(5) := "Five";
 9
10    v_list.EXTEND;
11  END;
12  /

DECLARE

ERROR at line 1: ORA-06532: Subscript outside of limit ORA-06512: at line 10

SQL> SQL>


 </source>
   
  


Use table() function to display varray type column

   <source lang="sql">
  

SQL> SQL> create table emp

 2  ( empno      NUMBER(4)    constraint E_PK primary key
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , sal        NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2)    default 10
11  ) ;

Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> create table e

 2  as
 3  select empno, ename, init, mgr, deptno
 4  from   emp;

Table created. SQL> SQL> SQL> create or replace type numberlist_t

 2  as varray(4) of varchar2(20);
 3  /

Type created. SQL> SQL> column numlist format a60 SQL> SQL> alter table e add (numlist numberlist_t); 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                                            NUMBERLIST_T

SQL> SQL> SQL> break on empno SQL> SQL> select empno, n.*

 2  from   e
 3  ,      TABLE(e.numlist) n;

no rows selected SQL> SQL> SQL> SQL> drop table e; Table dropped. SQL> drop table emp; Table dropped.


 </source>
   
  


Varray constructors.

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    TYPE Numbers IS VARRAY(20) OF NUMBER(3);
 3    v_NullList Numbers;
 4    v_List1 Numbers := Numbers(1, 2);
 5
 6    v_List2 Numbers := Numbers(NULL);
 7  BEGIN
 8    IF v_NullList IS NULL THEN
 9      DBMS_OUTPUT.PUT_LINE("v_NullList is NULL");
10    END IF;
11
12    IF v_List2(1) IS NULL THEN
13      DBMS_OUTPUT.PUT_LINE("v_List2(1) is NULL");
14    END IF;
15  END;
16  /

v_NullList is NULL v_List2(1) is NULL PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


VARRAY of VARCHAR2 and Varray of number

   <source lang="sql">
   

SQL> SQL> create table product(

 2          product_id              integer           primary key
 3          ,price                  number(7,2)
 4          ,description            varchar2(75)
 5          ,onhand                 number(5,0)
 6          ,reorder                number(5,0)
 7          ,supplier_no            integer
 8  );

Table created. SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"Oracle",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"SQL Server",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"MySQL",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"DB2",50,10); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Java",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"C++",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Javascript",3,5); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Ruby",null,null); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)

 2  AS
 3     v_loop  number := 1;
 4     v_type_ctr      number := 1;
 5     v_reorder       product.reorder%TYPE;
 6
 7     TYPE Prod_Names IS VARRAY(10) OF VARCHAR2(75);
 8     v_names Prod_Names := Prod_Names("Widget ","Gadget");
 9
10     TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2);
11     v_prices        Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5);
12
13     TYPE Prod_Onhand IS VARRAY(10) OF NUMBER;
14     v_onhand        Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55);
15
16  begin
17     WHILE v_loop <= v_ctr LOOP
18             IF v_type_ctr > 10 THEN
19                     v_type_ctr := 1;
20             END IF;
21             IF v_onhand(v_type_ctr) >= 30 THEN
22                v_reorder := v_onhand(v_type_ctr) - 10;
23             ELSE
24                v_reorder := v_onhand(v_type_ctr) - 5;
25             END IF;
26
27             INSERT INTO product (PRODUCT_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO)
28             VALUES (11111, v_prices(v_type_ctr), v_names(v_type_ctr),
29                     v_onhand(v_type_ctr), v_reorder, v_supplier);
30
31             v_loop := v_loop + 1 ;
32             v_type_ctr := v_type_ctr + 1;
33
34     END LOOP;
35  end;
36  /

Procedure created. SQL> show error No errors. SQL> SQL> exec p_add_prod(1,2); BEGIN p_add_prod(1,2); END;

ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C006114) violated ORA-06512: at "sqle.P_ADD_PROD", line 27 ORA-06512: at line 1

SQL> SQL> select * from product; PRODUCT_ID PRICE DESCRIPTION ONHAND REORDER SUPPLIER_NO


---------- --------------------------------------------------------------------------- ---------- ---------- -----------
        1        2.5 Oracle                                                                             100         20
        2         23 SQL Server
        3            MySQL
        4        1.5 DB2                                                                                 50         10
        5       10.5 Java                                                                               100         20
        6         45 C++
        7      19.99 Javascript                                                                           3          5
        8        4.5 Ruby

8 rows selected. SQL> SQL> SQL> drop table product; Table dropped. SQL> SQL> --


 </source>