Skip to main content

Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Development/DBWS/ParseDDLforMetadata/UseCases"

(New page: xx)
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
xx
+
__NOTOC__
 +
<css>
 +
  .source-sql {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-plsql {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-java5 {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-xml {padding:1em;border:1px solid black; background-color: white;}
 +
  .source-text {padding:1em;border:1px dashed black; background-color: white;}
 +
</css>
 +
The existing <code>o.e.p.platform.database.oracle.publisher</code> 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 <tt>RECORD</tt> type is also used as an argument or package-level global variable
 +
<source lang="plsql">
 +
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;
 +
</source>
 +
* 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):
 +
<source lang="plsql">
 +
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;
 +
</source>
 +
The object-graph contains four StoredProcedure 'pseudo' representations:
 +
<source lang="text">
 +
DOSOMETHING(MYREC,STUFF)
 +
DOSOMETHING(MYREC,STUFF, P_EMPNO)
 +
DOSOMETHING(MYREC,STUFF, P_EMPNO, FLAG)
 +
DOSOMETHING(MYREC,STUFF, P_EMPNO, FLAG, WHATEVER)
 +
</source>
 +
however, there are an additional four 'pseudo' representations '''not''' generated:
 +
<source lang="text">
 +
DOSOMETHING(MYREC,STUFF, FLAG)
 +
DOSOMETHING(MYREC,STUFF, WHATEVER)
 +
DOSOMETHING(MYREC,STUFF, FLAG, WHATEVER)
 +
DOSOMETHING(MYREC,STUFF, P_EMPNO, WHATEVER)
 +
</source>
 +
This highlights an issue where the number of in-memory objects could expand geometrically at the rate of 2<sup>N</sup>. In addition, if the StoredProcedure <tt>DOSOMETHING</tt> was overloaded, the 'pseudo' representations could conflict.
 +
* object-graph disconnect between PL/SQL composite arguments and JDBC composite arguments ([https://bugs.eclipse.org/bugs/show_bug.cgi?id=329435 bug 329435])
 +
<source lang="plsql">
 +
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)
 +
);
 +
</source>
 +
Internally, the type <tt>TESTREC</tt> points to table <tt>TEST_EMP</tt>, but when the field <tt>PHONES</tt> is referenced, the traversal algorithm  breaks down.

Latest revision as of 10:39, 29 June 2011


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 2N. 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.

Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/Development/DBWS/ParseDDLforMetadata/UseCases&oldid=259245"

This page was last modified 10:39, 29 June 2011 by Eclipsepedia anonymous user Unnamed Poltroon.

Back to the top