Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Development/DBWS/OracleUseCases"
| Line 11: | Line 11: | ||
An extreme (!) use-case: an object type wrapped in an array type, wrapped in another object type which is again wrapped in an array type and (finally!) wrapped in a third object type. | An extreme (!) use-case: an object type wrapped in an array type, wrapped in another object type which is again wrapped in an array type and (finally!) wrapped in a third object type. | ||
* Scalar PL/SQL datatypes - some PL/SQL datatypes have no JDBC equivalent and must be converted. The strategy is to use an anonymous PL/SQL block and handle the type conversion before the invocation of the Stored Procedure: | * Scalar PL/SQL datatypes - some PL/SQL datatypes have no JDBC equivalent and must be converted. The strategy is to use an anonymous PL/SQL block and handle the type conversion before the invocation of the Stored Procedure: | ||
| − | ** BOOLEAN <source lang=sql> | + | ** BOOLEAN |
| + | ::<source lang=sql> | ||
declare | declare | ||
x_target boolean := SYS.SQLJUTL.INT2BOOL(:1); | x_target boolean := SYS.SQLJUTL.INT2BOOL(:1); | ||
begin | begin | ||
bool_test(x=>x_target); | bool_test(x=>x_target); | ||
| − | end;</source> | + | end; |
| − | ** numeric types : PLS_INTEGER, SMALLINT, NUMERIC, BINARY_INTEGER, PLS_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, DEC, DECIMAL, LONG, LONG_RAW, RAW <br />Using the JDBC type with the widest precision <code>java.sql.Types.NUMERIC</code>, a simple assignment will do: <source lang=sql> | + | </source> |
| + | ** numeric types : PLS_INTEGER, SMALLINT, NUMERIC, BINARY_INTEGER, PLS_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, DEC, DECIMAL, LONG, LONG_RAW, RAW <br />Using the JDBC type with the widest precision <code>java.sql.Types.NUMERIC</code>, a simple assignment will do: | ||
| + | ::<source lang=sql> | ||
procedure int_test(y IN PLS_INTEGER) | procedure int_test(y IN PLS_INTEGER) | ||
| Line 24: | Line 27: | ||
begin | begin | ||
int_test(y=>y_target); | int_test(y=>y_target); | ||
| − | end;</source> | + | end; |
| + | </source> | ||
* Advanced PL/SQL types as arguments to Stored Procedure calls | * Advanced PL/SQL types as arguments to Stored Procedure calls | ||
**Record types | **Record types | ||
Revision as of 16:38, 17 December 2009
Supported Use-Cases for Oracle Platforms
All of the use-cases support for non-Oracle Platforms are supported on Oracle
The design-time utility DBWSBuilder uses custom Data Dictionary queries for the Oracle Platform (the java.sql.DatabaseMetaData API suffers from well-documented problems of accuracy and scope1).
By doing so, additional use-cases can be supported:
- Advanced JDBC types as arguments to Stored Procedure calls
- Object Types
java.sql.Struct's are realized in Oracle using proprietary types (oracle.sql.STRUCT) and APIs that are encapsulated in EclipseLink'sDatabasePlatform(specificallyorg.eclipse.persistence.platform.database.oracle.Oracle8Platformor higher)
- Array Types
java.sql.Array's are realized in Oracle using proprietary types (oracle.sql.VARRAY) and APIs that are encapsulated in EclipseLink'sDatabasePlatform(specificallyorg.eclipse.persistence.platform.database.oracle.Oracle8Platformor higher)
An extreme (!) use-case: an object type wrapped in an array type, wrapped in another object type which is again wrapped in an array type and (finally!) wrapped in a third object type.
- Scalar PL/SQL datatypes - some PL/SQL datatypes have no JDBC equivalent and must be converted. The strategy is to use an anonymous PL/SQL block and handle the type conversion before the invocation of the Stored Procedure:
- BOOLEAN
DECLARE
x_target BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
BEGIN
bool_test(x=>x_target);
END;- numeric types : PLS_INTEGER, SMALLINT, NUMERIC, BINARY_INTEGER, PLS_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, DEC, DECIMAL, LONG, LONG_RAW, RAW
Using the JDBC type with the widest precisionjava.sql.Types.NUMERIC, a simple assignment will do:
- numeric types : PLS_INTEGER, SMALLINT, NUMERIC, BINARY_INTEGER, PLS_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, DEC, DECIMAL, LONG, LONG_RAW, RAW
PROCEDURE int_test(y IN PLS_INTEGER)
DECLARE
y_target PLS_INTEGER := :1;
BEGIN
int_test(y=>y_target);
END;- Advanced PL/SQL types as arguments to Stored Procedure calls
- Record types
- this is something
- Collection types
- And again, something more
1 Compliance to the meta-data APIs available through the
java.sql.DatabaseMetaDatais weak - no vendor implements the APIs fully, nor in a standard fashion. For example, the simple act of getting the names of available databases is different across platforms: on Oracle thegetSchemasmethod is used while for DB2/Sybase/MS SQLServer thegetCatalogsmethod is used. Another example of cross-platform meta-data issues is thegetColumnsmethod: for PostgreSQL, the name of the table must be lower-case; for Oracle, it must be upper-case; others support mixed-case. link is a case-study of some of the issues.- Advanced PL/SQL types as arguments to Stored Procedure calls