How to pass array to oracle stored procedure

Before digging into the main topic lets understand Oracle collection types in brief.If you already know,please skip this.

Oracle Collection:
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

Collection Types:

• Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
•  Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables

Associative Arrays (Index-By Tables):

Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string. associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string

here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:

TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;

Define Associative Array
TYPE type_name IS TABLE OF element_type [NOT NULL]
   INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
   INDEX BY key_type;



The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760). The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.

An initialization clause is not required (or allowed).

When you reference an element of an associative array that uses a VARCHAR2-based key, you can use other types, such as DATE or TIMESTAMP, as long as they can be converted to VARCHAR2 with the TO_CHARfunction.

Index-by tables can store data using a primary key value as the index, where the key values are not sequential. In the example below, you store a single record in the index-by table, and its subscript is 7468 rather than 1.
DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   emp_tab EmpTabTyp;
BEGIN
   /* Retrieve employee record. */
   SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7468;
END;

Nested Tables:


Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.



PL/SQL nested tables are like one-dimensional arrays. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables



Define Nested Tables


TYPE type_name IS TABLE OF element_type [NOT NULL];

Defining SQL Types Equivalent to PL/SQL Collection Type Nested table

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)  -- define type
/
CREATE TYPE Student AS OBJECT (  -- create object
   id_num  INTEGER(4),
   name    VARCHAR2(25),
   address VARCHAR2(35),
   status  CHAR(2),
   courses CourseList)  -- declare nested table as attribute
/






Difference between Arrays and Nested tables:



1. Arrays have a fixed upper bound, but nested tables are unbounded . So, the size of a nested table can increase dynamically. 



2. Arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts.

Varrays:

Items of type VARRAY are called varrays. They allow you to associate a single identifier with an entire collection. This association lets you manipulate the collection as a whole and reference individual elements easily. To reference an element, you use standard subscripting syntax . For example, Grade(3) references the third element in varray Grades




A varray has a maximum size, which you must specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, the current upper bound for varray Grades is 7, but you can extend it to 8, 9, 10, and so on. Thus, a varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.




Define Varrays:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) 
   OF element_type [NOT NULL];

size_limit is a positive integer literal representing the maximum number of elements in the array. When defining a VARRAY type, you must specify its maximum size. In the following example, you define a type that stores up to 366 dates:

Defining SQL Types Equivalent to PL/SQL Collection Type Nested table

The script below creates a database column that stores varrays. Each varray element contains a VARCHAR2.

-- Each project has a 16-character code name.
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
/
CREATE TABLE department (  -- create database table
   dept_id  NUMBER(2),
   name     VARCHAR2(15),
   budget   NUMBER(11,2),
-- Each department can have up to 50 projects.
   projects ProjectList)

Choosing Between Nested Tables and Associative Arrays

Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables are appropriate for important data relationships that must be stored persistently.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers when appropriate.

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to associative arrays.

Choosing Between Nested Tables and Varrays

Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the table is stored and retrieved, because the order and subscripts are not preserved when a nested table is stored in the database.

HOW TO PASS ARRAY TO ORACLE STORED Procedure:

Here Nested table technique is used to pass an array to proc

Before we use an array to pass as a parameter in stored procedure it should be created as a type in Oracle Schema 

CREATE OR REPLACE TYPE SCHEMA_NAME."name_list"  is table of varchar2(100)  

  //array of String,can use AS instead of IS

CREATE OR REPLACE TYPE SCHEMA_NAME."id_list"      is table of NUMBER   

   //Array of integers,Can Use AS instead of AS

how to pass array in sql proc as param:

procedure insert_organisation_subcategory_category
             (p_company_ids IN SCHEMA_NAME.name_list,
              p_discount_category_ids IN SCHEMA_NAME.id_list,

              p_discount_subcategory_id IN discount_owner.compdiscount_subcategory_id%type,
              p_add_by_userid IN discount_owner.co_xref.add_by_userid%type,
              p_change_by_userid IN discount_owner.co_xref.change_by_userid%type
             ) is

inserting arrays of company and categories in the table:

insert into discount_test.company_category_subcategory_xreference
         (companyid,
          discount_subcategory_id,
          discount_category_id,
          add_by_userid,
          change_by_userid)
         select oids.column_value,
                p_discount_subcategory_id,
                dcids.column_value,
                p_add_by_userid,
                p_change_by_userid
         from table(cast(p_company_ids as SCHEMA_NAME."name_list")) oids,
              table(cast(p_discount_category_ids as SCHEMA_NAME."id_list")) dcids,
              discount_owner.comp_page_tab_xref optx
         where oids.column_value = optx.companyid and
               dcids.column_value = optx.discount_page_tab_id
         minus
         select companyid,
                p_discount_subcategory_id,
                discount_category_id,
                p_add_by_userid,
                p_change_by_userid
         from discount_owner.comp_cat_subcat_xref
         where discount_subcategory_id = p_discount_subcategory_id;
end;

Java Code:

String array[] = {"one", "two", "three","four"}; 

             

      oracle.sql.ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.name_list", con);

            oracle.sql.ARRAY name_array_to_pass = new ARRAY(des,con,array);

    ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.id_list", con);

            ARRAY id_array_to_pass = new ARRAY(des,con,array);

    

    CallableStatement st =   con.prepareCall("call discount_test.insert_organisation_subcategory_category(?,?,?)");

            // Passing an array to the procedure - 

            st.setArray(1, array_to_pass);

            st.setArray(2, array_to_pass);

            ................

            ................

  st.execute();




NOTE:This is not a complete exampel but just to give practical understanding of the solution.