Friday, March 6, 2009

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

Not all PL/SQL arrays can be directly accessed using JDBC. If you want to pass a VARRAY parameter to PL/SQL from Java or return a PL/SQL ARRAY to Java using JDBC you won't be able to do without extra work. This is because 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. As of version 6 OrindaBuild creates the extra code required to run your stored procedures.

Chapter 18 of the Oracle10g 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 three choices:

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

This 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.

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 additional 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;
.
/

No comments: