Orindasoft

The Orindabuild reference manual

Working with Records

Topics

1. Supported Record Types
   
2. Creation of Classes to support Records that are parameters
   
3. Creation of classes to support tables of VARRAY or ARRAY that are parameters.
   
4. PL/SQL Index By Tables
   
5. Using Record Classes with generated code
   
6. Common problems with VARRAYS and ARRAYS
   
7. Working with PL/SQL Arrays that can not be directly accessed by JDBC
   

Supported Record Types

OrindaBuild supports the following types of records as parameters to stored procedures:

Record Type	Description
%ROWTYPE	A PL/SQL Record that matches the definition of a table in the data dictionary.
%TYPE	A PL/SQL variable that matches the definition of a column in a table in the data dictionary
PL/SQL Package Record Type	A PL/SQL Record that is declared as part of PACKAGE definition
Oracle TYPE - Scalars	A record created using the CREATE TYPE command that consists of one or more fields, each of which is a conventional Oracle datatype
PL/SQL INDEX BY Tables	A Table of NUMBER or VARCHAR2 indexed by BINARY INTEGER.
Oracle TYPE - ARRAYS and VARRAYS	A TYPE which is an ARRAY or VARRAY of a declared Oracle type that consists of scalars such as NUMBER, VARCHAR2 etc. In order for OrindaBuild to be able to use an ARRAY or VARRAY it must be declared outside of a PL/SQL package - ARRAYS and VARRAYS declared inside a PL/SQL package are not accessible by JDBC.

Creation of Classes to support Records that are parameters.

When OrindaBuild encounters a record that is a parameter to a stored procedure, it creates a matching record class.  If a record is used in multiple locations, only one class is created. Record classes are used for storing the fields of a record. To use a record class you must instantiate it and fill in its fields by calling its 'set' methods. Common methods for record classes are:

Record Class Method Name	Description
recordname (LogInterface theLog)	Default Constructor.
recordname(LogInterface theLog ,Object[] newValues)	Copy Constructor that uses an array of Object. This allows you to assign records that are identical but have different names using the getCurrentValues() method.
getCurrentValues()	Returns contents of this record as an array of Object.
setNewValues(Object[] newValues)	Assigns contents of this record using an array of Object. Basic sanity checking is performed to make sure that the array length is correct and the data types match.
setParamFieldName(String paramFieldName)	Sets a field
setParamFieldNameToNull()	Sets a field to null.
getFieldName()	Gets a field.
getFieldNameDataType()	Gets a field in a specific data type

Creation of classes to support tables of VARRAY or ARRAY that are parameters.

If the parameter is a VARRAY or ARRAY an additonal class will be created to represent the table of records. The class will always implement the Interface com.orindasoft.pub.PlsqlArray , which is used by generated code to load and unload records. The generated table class will also have these methods:

Table Class Method Name	Description
tablename (LogInterface theLog)	Default Constructor
LogInterface theLog ,recordName[] newValues)	Constructor that takes an array of recordName
setNewValues(recordName[] newValues)	Set contents of array. Each 'Object' must be a recordName.
setNewValuesAsObject(Object[] newValues)	Set contents of array. Each 'Object' must either be null, implement oracle.sql.Datum or be a recordName. Used to unload data returned fro the database. Part of  com.orindasoft.pub.PlsqlArray
addNewValue(recordName newValue)	Add a new recordName to the array. If the matching database object is an ARRAY then newValue can not be null. If the database object is a VARRAY the newValue can be null but adding the record must not exceed the size limit of the VARRAY, which will be stored in a public constant called VARRAY_LENGTH.
Object[] getCurrentValuesAsObject()	Returns Array of recordName. Each recordName will implement either OraData or CustomDatum and can be used to create an array. Part of  com.orindasoft.pub.PlsqlArray
getArrayName()	Returns String containing Oracle Type that this table represents. Used to create Array Descriptor. Part of  com.orindasoft.pub.PlsqlArray

PL/SQL Index By Tables

From Oracle 10.1.0 (10g) onwards OrindaBuild generates code for procedures that have PL/SQL INDEX BY tables as  parameters.

In order for an INDEX BY table to be usable it must meet the following conditions:

• It must be indexed by BINARY INTEGER
• It must be an array of Numbers or Strings
  

.OrindaBuild uses the class com.orindasoft.pub.PlsqlIndexByTable to represent index by parameters. The important methods are:

Method Name	Description
PlsqlIndexByTable()	Constructor that initializes array to a zero length array of numbers.
setElementMaxCount(int elementMaxCount)	Sets how many values can be in this array.
setElementMaxLength()	Sets the maximum length of a String in this array.
setDataTypeNumber(), setDataTypeString()	Defines the data type of elements in the array. This must be set even if the array is empty.
setArray(Object[] dataArray)	Loads the array with an array of Object[].
Object[] getArray()	Retrieves the array. Numbers will always come back as java.math.BigDecimal
setArray(int[] newArray), setArray(long[] newArray) setArray(float[] newArray) setArray(double[] newArray)	Sets the array using an array of int, long, float or double.
getArrayAsInt(int nullToken) getArrayAsLong(long nullToken) getArrayAsFloat(float nullToken) getArrayAsDouble(double nullToken)	Gets the array as int[], long[], float[] or double[]. Because the returned array can contain null you need to use nullToken to specify how nulls are to be treated.
String[] getArrayAsString()	Get the array as String[]. Some of the elements may be null.

Using Record Classes with generated code

Pass the record class into a generated procedure class as a parameter.  If working with arrays you must create an instance of the array class and add instances of its record class to it. Procedures that return records create instances of record classes obtained using the appropriate Get method. Refer to the example code on the OrindaBuild web site as it shows this process in detail. Below is a simple example of records in use:

// This example assumes the following TYPEs and PROCEDUREs exist: // // CREATE TYPE TYPE_ARRAY_COMMANDS AS OBJECT // (COMMAND_NAME VARCHAR2(100) // ,OS_NAME VARCHAR2(512) // ,JAVA_CLASS_FILE_NAME VARCHAR2(512) // ,BUILTIN_Y_OR_N VARCHAR2(1) // ,required_number      number null // ,obligatary_date      date   NULL // ,EXE_FILE_NAME VARCHAR2(512) // ,COMMAND_DESCRIPTION VARCHAR2(512)); // //  CREATE OR REPLACE TYPE TBL_ARRAY_COMMANDS_TYPE //  AS TABLE OF TYPE_array_COMMANDS; // //  CREATE OR REPLACE TYPE TBL_VARRAY_COMMANDS_TYPE //  AS VARRAY(100) OF TYPE_array_COMMANDS; // //  create or replace package oracle_arraysv8 as //  -- //  PROCEDURE arraytest3(p_param1 in out TBL_VARRAY_COMMANDS_TYPE //                     ,(p_param2 in out TBL_ARRAY_COMMANDS_TYPE); //  -- //  END; // //  The generated class is called 'oracleArraysv8Arraytest3'. //  It will take instances of tblVarrayCommandsType and tblArrayCommandsType as //  parameters. Both of these are tables of typecommandsVArrayayCommands. // // Create instance of generated class. It needs a Connection and something which // implements com.orindasoft.pub.LogInterface. // oracleArraysv8Arraytest3 proc = new oracleArraysv8Arraytest3(theConnection, theLog); // Create both the Array classes we need. tblVarrayCommandsType commandsVArray = new tblVarrayCommandsType(theLog); tblArrayCommandsType  commandsArray  = new tblArrayCommandsType(theLog); // Create a single row - we'll reuse it... typeArrayCommands row = new  typeArrayCommands(theLog); // Populate both commandsVArrayays with 100 records... for (int i=0; i < 100; i++)   {   // Create a row we can add to our Varray.   // 'typeArrayCommands' has fields that match those   // in TYPE_ARRAY_COMMANDS.   row = new  typeArrayCommands(theLog);   row.paramBuiltinYOrN = "Y";   row.paramCommandDescription = "Desc";   row.paramCommandName = "VArray Entry #" + i;   row.paramExeFileName = "A filename";   row.paramJavaClassFileName = "A java class";   row.setParamObligataryDate(System.currentTimeMillis());   row.setParamRequiredNumber(52);   if (i % 12 == 0)     {     // Varrays can have null elements. Demonstrate this     // by making every twelth entry in the array null...     commandsVArray.addNewValue(null);     }   else     {     commandsVArray.addNewValue(row);     }   // Now create a row to add to the array class.   row = new  typeArrayCommands(theLog);   row.paramBuiltinYOrN = "N";   row.paramCommandDescription = "Desc";   row.paramCommandName = "Array entry #" + i;   row.paramExeFileName = "a filename";   row.paramJavaClassFileName = "a java class";   row.setParamObligataryDate(System.currentTimeMillis());   row.setParamRequiredNumber(52);   commandsArray.addNewValue(row);   } // Call the set methods so that the procedure has both // sets of parameters. proc.setParamPParam1(commandsVArray); proc.setParamPParam2(commandsArray); // Call the execute method. proc.executeProc(); // Unload the data returned by the procedure... tblVarrayCommandsType commandsVArrayOut = proc.getParamPParam1(); tblArrayCommandsType commandsArrayOut = proc.getParamPParam2();

Common problems with VARRAYS and ARRAYS

VARRAYS and ARRAYS are subject to various datatype limitations. OrindaBuild created code can generate the following error messages when working with VARRAYS and ARRAYS:

Error Message	Meaning
ORA-00600: internal error code, arguments: [12760], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [kope2upic954], [], [], [], [], [], [], []	This error is known to occur in 8i  when the combination of array size and record length being used exceeds the capability of the driver. If you get this message when working with an 8.1.7 server your Connection object may become unusable.
"Protocol Exception" SQLException followed by "OALL8 is in an inconsistent state" SQLException	This is known to occur under when you are using too big an array size. How big your array can be depends on the length of each record and the Driver/Database combination. If you exceed the maximum size you will get the "OALL8" SQLException and your connection object may become unusable. This message is also created if you are using the following: 9.0.1 Database 10.1.0 JDBC Driver Generated Code that passes in an ARRAY or VARRAY of VARCHAR2 as a Parameter

Common problems with INDEX BY tables

OrindaBuild generated code can generate the following error messages if the methods for PlsqlIndexByTable aren't used properly:

Error Message	Meaning
ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array	This error is known to occur when your array size is too small to handle the number of rows returned.
java.lang.OutOfMemoryError - or - ORA-03120: two-task conversion routine: integer overflow	These errors are known to occur when an unreasonably large array has been requested or the maximum length for an array element is too long. Be aware that the Oracle 10.1.0 JDBC driver uses these numbers to allocate memory before data is returned. Never set the maximum number of elements to Integer.MAX_VALUE.
ORA-06502: PL/SQL: numeric or value error: host bind array too small	This is known to occur when the max length of an array item has been set to a value smaller than one or more of the values that are supposed to be returned. Newer builds of OrindaBuild (> 4.0.1818) set this value to the length specified in the database just before setPlsqlIndexTable or getPlsqlIndexTable is called.

Working with PL/SQL Arrays that can not be directly accessed by JDBC

Not all PL/SQL arrays can be direcly accessed using JDBC. Procedures or Functions that take arrays and are declared inside packages instead of with "CREATE TYPE" can not be called using JDBC. This is a limitation of Oracle, not OrindaBuild. Chapter 11 of the Oracle9i JDBC Developer's Guide and Reference says:

Because Oracle supports only named collections, you must declare a particular VARRAY type name or nested table type name. "VARRAY" and "nested table" are not types themselves, but categories of types. A SQL type name is assigned to a collection when you create it, as in the following SQL syntax: CREATE TYPE <sql_type_name> AS <datatype>;

In the quote above 'support' refers to the abilty to use as a parameter in JDBC. A 'Named Collection' is one that is created using 'CREATE TYPE'. The data dictionary view USER_COLL_TYPES contains the named collections that are part of your schema. If your procedure uses an array parameter that is not listed in USER_COLL_TYPES then it can not be run directly using JDBC.  If you have a procedure that uses a collection defined inside a PL/SQL package and you want to access it with Java you have two choices:

1. Rewrite the procedure so it uses an array created with CREATE TYPE. 

This may not be that much work. Often it involves moving the record and array definitions from inside the package definition to CREATE TYPE statements in the same source file. If the PL/SQL code in question is already in use and being called by other programs then you will have to modify them as well. This is the best long term solution to the problem as it avoids any duplication of effort. Once this has been done to all your array parameters OrindaBuild will be able to generate code to run the procedure.

Original Code:		Modified Code:
/* This package definition contains        * the definition of the array the  * procedure uses as well as the  * procedure.  *  * It can not be directly accessed  * using JDBC */             CREATE OR REPLACE PACKAGE NEW_CUSTOMER AS -- TYPE CUST_REC IS RECORD (NAME VARCHAR2(80) ,PHONE VARCHAR2(10)); --           TYPE CUST_REC_TAB IS TABLE OF CUST_REC INDEX BY BINARY_INTEGER; -- PROCEDURE INS(CUST_REC_ROWS  IN OUT CUST_REC_TAB); -- END; . /		/* The record and array definitions  * have been removed from the  * package definiton and are  * now creted with CREATE TYPE  */ /* Record Declaration */ CREATE OR REPLACE TYPE CUST_REC_JDBC AS OBJECT (NAME  VARCHAR2(80) ,PHONE VARCHAR2(10) ); . / /* Array declaration */ CREATE OR REPLACE TYPE CUST_REC_TAB_JDBC AS TABLE OF CUST_REC_JDBC; . / /* This package definition contains  * a procedure that uses an array  * defined with CREATE TYPE.  *  * It can be directly accessed using  * JDBC.  */ CREATE OR REPLACE PACKAGE NEW_CUSTOMER AS -- PROCEDURE INS(CUST_REC_ROWS    IN OUT CUST_REC_TAB_JDBC); -- END; . /

2. Write an additonal wrapper procedure that converts an array created with CREATE TYPE into the format required by your procedure

This is more work and leads to issues with maintenance in the future. If you can not change the procedure itself  or it is already used by a large base of existing code you may have to write an additional wrapper procedure that takes parameters usable by JDBC:

Original Code: /* This package definition contains  * the definition of the array the  * procedure uses as well as the  * procedure.  *  * It can not be directly accessed  * using JDBC  */ CREATE OR REPLACE PACKAGE NEW_CUSTOMER AS -- TYPE CUST_REC IS RECORD (NAME  VARCHAR2(80) ,PHONE VARCHAR2(10)); -- TYPE CUST_REC_TAB IS TABLE OF CUST_REC INDEX BY BINARY_INTEGER; -- PROCEDURE INS(CUST_REC_ROWS IN OUT CUST_REC_TAB); -- END NEW_CUSTOMER; . /

Additional Code:

/* Additional record and array * definitions are now created * with CREATE TYPE */ /* Record Declaration */ CREATE OR REPLACE TYPE CUST_REC_JDBC AS OBJECT (NAME VARCHAR2(80) ,PHONE VARCHAR2(10) ); . / /* Array declaration */ CREATE OR REPLACE TYPE CUST_REC_TAB_JDBC AS TABLE OF CUST_REC_JDBC; . / /* This procedure definition uses an * array defined with CREATE TYPE * to call our original procedure. * * It can be directly accessed using * JDBC. */ CREATE OR REPLACE PROCEDURE NEW_CUSTOMER_JDBC_INS (CUST_REC_ROWS IN OUT CUST_REC_TAB_JDBC) AS -- l_array NEW_CUSTOMER.CUST_REC_TAB; l_record NEW_CUSTOMER.CUST_REC; -- BEGIN -- -- Load our JDBC table into the PL/SQL one... -- l_array.delete; -- FOR i IN CUST_REC_ROWS.FIRST .. CUST_REC_ROWS.LAST LOOP -- l_record := NULL; -- l_record.NAME := CUST_REC_ROWS(i).NAME; l_record.PHONE := CUST_REC_ROWS(i).PHONE; -- l_array(i) := l_record; -- END LOOP; -- -- Call the procedure... -- NEW_CUSTOMER.INS(l_array); -- -- Unload the array we got back... -- FOR i IN l_array.FIRST .. l_array.LAST LOOP -- CUST_REC_ROWS(i).NAME := l_array(i).NAME; CUST_REC_ROWS(i).PHONE := l_array(i).PHONE; -- END LOOP; -- END; . /