Using Oracle varray

The iAnywhere Solutions 11 - ODBC driver for Oracle supports the use of Oracle varray in stored procedures. Using varray in upload scripts (upload_insert, upload_update, and upload_delete) that are written in stored procedures may improve performance of the MobiLink server, compared with upload scripts written in stored procedures that do not use varray. Simple SQL statements such as INSERT, UPDATE and DELETE without stored procedures usually offer the best performance, however using stored procedures, including the varray technique, provides an opportunity to apply business logic that the simple statements do not.

varray example

The following is a simple example that uses varray:

  1. Create a table called my_table that contains 3 columns.

    create table my_table ( pk integer primary key, c1 number(20), c2 varchar2(4000) )

  2. Create user-defined collection types using varrays.

    create type my_integer is varray(100) of integer;
create type my_number is varray(100) of number(20);
create type my_varchar is varray(100) of varchar2(8000);

    my_varchar is defined as a varray that contains 100 elements and each element is a data type of varchar2 and width of 8000. The width is twice as big as that specified for my_table.

  3. Create stored procedures for insert.

    create or replace procedure my_insert_proc( pk_v my_integer, c1_v my_number, c2_v my_varchar )
is
c2_value my_varchar;
begin
      c2_value := c2_v;  -- Work around an Oracle bug
      FORALL i in 1 .. pk_v.COUNT
            insert into my_table ( pk, c1, c2 ) values( pk_v(i), c1_v(i), c2_value(i) );
end;
varray restrictions

The following restrictions apply when using varray in stored procedures:

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1