Developers

Omnis Technical Note TNSQ0026 July 2009

PL/SQL Array Binding with Oracle

For Omnis Studio
By Gary Ashford, Omnis Engineering

Introduction

This tech note accompanies the Oracle Server-Specific Programming section in the Omnis Studio Programming manual and discusses the issues involved in binding arrays of scalar data types for return to an Omnis library.
With the advent of Oracle RefCursors, this technique might be considered a less-efficient way to retrieve result-set information from Oracle stored procedures although it does provide a way to place arrays directly into Omnis list columns.

What is Array Binding?

If you have used Oracle's "Procedural Language/Structured Query Language" (PL/SQL) before, you will be familiar with stored procedures and the method used to pass parameters to these procedures from Omnis, for example:

Do StatObj.$plsql("begin myproc(1,@[param1], @[param2]); end;") Returns #F

The $plsql() method, binds the two Omnis variables supplied, that is- it passes the memory addresses of these variables to the stored procedure. This allows the stored procedure to read from and write directly to the Omnis variables.
As with standard bind variables, once a statement has been "$plsql()" prepared, it can be re-executed using different values for param1 and param2 each time if required.
Array binding allows multiple values to be passed using a single PL/SQL parameter. The Oracle DAM accomplishes this by passing the address of a single-column Omnis list to the stored procedure. To do this, the caller binds the list variable to the stored procedure for each single-column list that will be returned. The column number to be used in each case is specified via comma separated values after the SQL text, for example:

Do StatObj.$plsql("begin testproc(1,@[lCreditList], @[lCreditList]); end;", 1, 2 ) Returns #F

This will bind column 1 of lCreditList to the second method parameter and column 2 of lCreditList to the third method parameter.

How big will the array be?

PL/SQL limits the maximum size of a PL/SQL array to 32512 bytes. Dependant on the size of each element, this restricts the maximum number of rows that can be returned/sent. The array size reserved for each single-column list is governed by the $plsqlarraysize statement property so that the array size can be set to a smaller value if required.
For example, to bind an array of Omnis Character(256) columns, the number of rows that can be returned is therefore equivalent to:

nRows = $plsqlarraysize / (256 * nBytes-per-character)         where $plsqlarraysize defaults to 32512

This equates to 127 rows for the Non-Unicode DAM or 63 rows for the Unicode DAM (which passes character data using the UTF-16 encoding). Note- Oracle does not support array binding of large objects (CLOBS, BLOBS and LONG data).

Handling Unicode Data

If the data types of the PL/SQL array elements do not match with the element types expected by the stored procedure, then error PLS-00418 typically results. This can happen when mixing Oracle Unicode and Non-Unicode data types, for example when creating custom data types inside stored procedures:

create or replace package test as   type account_name is table of accounts.name%TYPE index by binary_integer;   type account_balance is table of accounts.balance%TYPE index by binary_integer; end test; create or replace procedure credit(accnum IN number, amount IN number,pname out test.account_name, pbalance out test.account_balance) is   cursor c1 is select name,balance from accounts where balance > limit;   row_count BINARY_INTEGER;   begin     row_count := 1;     update accounts set balance = balance + amount where id = accnum;     open c1;     LOOP       FETCH c1 INTO pname(row_count),pbalance(row_count);       row_count := row_count + 1;       exit when c1%NOTFOUND;     end LOOP;     close c1;   end;

In the above example, the custom type account_type is created from the data type of column name in table accounts.
If the name column of table accounts is defined as VARCHAR2, then the corresponding Omnis parameter used when calling the procedure should be single-column list of non-Unicode character data.
Likewise, if the name column of table accounts is defined as NVARCHAR2, then the caller needs to ensure that the corresponding Omnis parameter is a single-column list of Unicode character data.  Note that this will not be possible using the non-Unicode DAM.  Instead, the custom data type should be maually defined as VARCHAR2, thus:

type account_name is table of accounts.name%TYPE index by binary_integer;      becomes  type account_name is table of VARCHAR2(256) index by binary_integer;

The procedure can then be called using the non-Unicode DAM and PL/SQL will perfrom the necessary conversion when retrieving Unicode data from the database table.

Where the custom type refers to a non-Unicode data type, the Unicode DAM can be configured to call the procedure using non-Unicode character data either by

• setting the $unicode session property to kFalse before executing the stored procedure call, or by
• setting $nationaltonvarchar to kTrue and and supplying Character data as opposed to National data, for example:

Do lCreditList.$define(lName,lBalance)                     ;; lName defined as Character 256, lBalance is Number 2dp Do cStat.$plsql('begin credit(2,490,@[lCreditlist], @[lCreditlist]); end;',1,2) Returns #F Do cSess.$nationaltonvarchar.$assign(kTrue)       ;; Force Character data to be mapped to VARCHAR2 Do cStat.$execute() Returns #F

To summarize, it is important that the character-set form of the bound list column matches with the custom parameter type referred to by the PL/SQL procedure.

Binding Omnis Lists

This final point concerns binding Omnis lists to PL/SQL parameters. Because the DAM needs to bind an Omnis list (binary) type for each single-column table to be returned, care needs to taken to ensure that the maximum size of a PL/SQL parameter (32767 bytes) is not exceeded when allocating the bind buffers.
You should specify this before the PL/SQL statement is executed by setting the session $blobsize property to the size required, for example:

Do cSess.$blobsize.$assign(32767)                 ;; Prevents ORA-06505

Sample Library

The Omnis Studio 4.3.1 library provided below contains code used in this TechNote. It creates and calls a stored procedure which returns two single-column lists back to Omnis. It also generates a sample table and data.
If you convert this library for use with the Unicode version of Studio (e.g. Studio 5.0), you can try the code changes suggested above to make the library work with non-Unicode table data.

plsqlarray.lbs (8KB zipped)

This TechNote is the product of a recent bug-fix. If using Studio 4.2/4.3, please ensure you are using the latest Oracle DAM- available from the ODPP download area. Studio 5.0 already contains a suitable DAM.