Oracle VARRAY

The SQL Anywhere 16 - Oracle ODBC driver 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:

Create a table called my_table that contains 3 columns.

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

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 required to be twice as big as that specified for my_table.

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:

The ODBC data source must have the Enable Microsoft distributed transactions checkbox cleared.
BLOB and CLOB VARRAYs are not supported.
If VARRAY is a data type of CHAR, VARCHAR, NCHAR or NVARCHAR, the user-defined VARRAY type must be twice as big as the length specified for the table column.
The number of rows in the VARRAY that are sent by the MobiLink server to the Oracle consolidated database is set by the -s option, not the size of the VARRAY declared in the VARRAY type. The -s option must not be bigger than the smallest VARRAY type size in use by synchronization scripts. If it is bigger, the MobiLink server issues an error. See -s mlsrv16 option.

Discuss this page in DocCommentXchange.