EclipseLink/Development/DBWS/ParseDDLforMetadata/UseCases

The existing o.e.p.platform.database.oracle.publisher classes cannot handle the following use cases:

• default values for StoredProcedure/StoredFunction arguments (information not available in Data Dictionary prior to 11g)
• strongly-typed REF CURSOR as OUT argument when underlying RECORD type is also used as an argument or package-level global variable

CREATE OR REPLACE PACKAGE CURSOR_TEST AS
   TYPE EREC IS RECORD (
        FLAG PLS_INTEGER,
	EMPNO NUMBER(4),
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9)
   );
   TYPE EREC_CURSOR IS REF CURSOR RETURN EREC;
   FUNCTION EFUNC(PARM1 IN VARCHAR2, PARM2 IN EREC) RETURN EREC_CURSOR;
END CURSOR_TEST;

• object-graph distortion between optional arguments and overloading

Unfortunately, the information in the Data Dictionary views can be (under certain circumstances) incomplete or inaccurate, or the object-graph can be distorted. For example, an argument to a StoredProcedure may have a default value assigned to it, thus making it optional. This in turn means that the calling sequence can change (fewer arguments need be passed in):

CREATE OR REPLACE PACKAGE SOMEPKG AS
  TYPE EMP_REC IS RECORD(
    EMPNO EMP.EMPNO%TYPE,
    FNAME EMP.FNAME%TYPE,
    LNAME EMP.LNAME%TYPE
  );
  PROCEDURE DOSOMETHING(MYREC IN OUT EMP_REC, STUFF IN VARCHAR2,
    P_EMPNO IN NUMBER := 20, FLAG IN BOOLEAN DEFAULT FALSE,
    WHATEVER IN VARCHAR2 DEFAULT 'bogus');
END;

The object-graph contains four StoredProcedure 'pseudo' representations:

DOSOMETHING(MYREC,STUFF)
DOSOMETHING(MYREC,STUFF, P_EMPNO)
DOSOMETHING(MYREC,STUFF, P_EMPNO, FLAG)
DOSOMETHING(MYREC,STUFF, P_EMPNO, FLAG, WHATEVER)

however, there are an additional four 'pseudo' representations not generated:

DOSOMETHING(MYREC,STUFF, FLAG)
DOSOMETHING(MYREC,STUFF, WHATEVER)
DOSOMETHING(MYREC,STUFF, FLAG, WHATEVER)
DOSOMETHING(MYREC,STUFF, P_EMPNO, WHATEVER)

This highlights an issue where the number of in-memory objects could expand geometrically at the rate of 2^N. In addition, if the StoredProcedure DOSOMETHING was overloaded, the 'pseudo' representations could conflict.

• object-graph disconnect between PL/SQL composite arguments and JDBC composite arguments (bug 329435)

CREATE OR REPLACE PACKAGE TEST_PKG AS
   TYPE TESTREC IS TEST_EMP%ROWTYPE;
...
END;
 
CREATE OR REPLACE TYPE PHONE_OBJECT AS OBJECT (
  AREACODE VARCHAR2(3),
  PHONENUMBER VARCHAR2(20),
  PHONETYPE VARCHAR2(20)
);
CREATE OR REPLACE TYPE PHONE_LIST AS VARRAY(2) OF PHONE_OBJECT;
CREATE TABLE TEST_EMP (
    EMPNO NUMBER(4) NOT NULL,
    FNAME VARCHAR2(40),
    LNAME VARCHAR2(40),
    PHONES PHONE_LIST,
    PRIMARY KEY (EMPNO)
);

Internally, the type TESTREC points to table TEST_EMP, but when the field PHONES is referenced, the traversal algorithm breaks down.