The Need
Utilities that automate the creation of Data Access Objects for Oracle and Java have been around for years, but suffered from a fundamental limitation - anyone who has worked with real-world Oracle databases for any length of time can tell you that simply being able to access the tables isn't good enough, and that much of the functionality of an Oracle database will be implemented in PL/SQL procedures and complicated SQL statements.
Access to the raw data itself is at most 80% of the picture, and implementing the other 20% using a DAO paradigm involves re-implementing the same logic that lives in PL/SQL and complex SQL statements. So if you work with Java and you want to access an existing Oracle application you can really benefit from a utility that automates the task of writing all the Java JDBC calls you would otherwise need to write.
To make things more complicated, there's a fundamental disconnect between the parameters you can pass to a PL/SQL procedure and those supported by Oracle's implementation of JDBC. Real-world PL/SQL makes extensive use of different kinds of records and arrays as parameters and return values for PL/SQL procedures and functions, but you can't directly use JDBC in every case because Oracle's JDBC implementation only recognize one kind of record and array.
So while you can pass arrays of oracle Type objects to java via JDBC you can't pass arrays of records that are defined inside a PL/SQL package.
This creates a problem for our Java developer, as they now have to either write a new PL/SQL API on top of the existing one or re-implement the logic expressed in the PL/SQL with individual SQL statements.
What we used to do
The earlier versions of OrindaBuild worked within this limitation. We failed to understand how serious a limitation this was and assumed that because JDBC couldn't do it we couldn't do it either and therefore everyone had to live with this restriction. What we didn't initially realize was that the majority of the world's enterprise grade PL/SQL uses arrays or records that aren't supported by JDBC.
No more limitations - pass any kind of array you want to PL/SQL from Java
Over time it became clearer and clearer that we needed to address this issue. OrindaBuild 6, which went into Beta today, now supports access to all possible arrays and records used by PL/SQL from Java, without you having to modify the PL/SQL.
For example: OrindaBuild can write the Java classes Java< needed to call these PL/SQL Procedures. Follow the links to see the generated Java classes:
PACKAGE PACKAGE_ARRAY_EXAMPLE AS
/*
* THIS EXAMPLE SHOWS HOW TO CALL PL/SQL PROCEDURE
* THAT TAKES A PARAMETER DEFINED IN A PL/SQL PACKAGE
* AS AN ARRAY OF %ROWTYPE (TABLE RECORD).
*
* THERE IS NO DIRECT WAY TO CALL THIS PL/SQL PROCEDURE FROM JAVA.
*
* INSTEAD WE USE ORINDABUILD TO CREATE JAVA JDBC CODE THAT
* CAN BYPASS THIS LIMITATION AND RETURN OR ACCEPT A PL/SQL
* ARRAY FROM JAVA USING JDBC.
*
*/
--
TYPE FLIGHTS_PLSQL_ARRAY IS TABLE OF FLIGHTS%ROWTYPE;
--
TYPE BOOKINGS_PLSQL_ARRAY IS VARRAY(30) OF BOOKINGS%ROWTYPE;
--
PROCEDURE GET_PLSQL_ARRAY_OF_FLIGHTS
(P_CITY IN FLIGHTS.DEPARTURE_CITY%TYPE
,P_FLIGHTS_FROM OUT NOCOPY FLIGHTS_PLSQL_ARRAY);
--
PROCEDURE ADD_BOOKINGS_PLSQL_ARRAY
(P_CUSTOMER IN CUSTOMERS%ROWTYPE
,P_BOOKING_TABLE IN BOOKINGS_PLSQL_ARRAY
,P_STATUS_MESSAGE OUT NOCOPY VARCHAR2);
--
END PACKAGE_ARRAY_EXAMPLE;
No other product has this capability.
For an example of a generated Java service that uses Java to call PL/SQL procedures that take PL/SQL Package Arrays as parameters see here.
Getting OrindaBuild
OrindaBuild demo versions expire after roughly a month. We do the following kinds of Demos:
Full windows install - OrindaBuild as a full stand alone Java Swing GUI.
Oracle SQL Developer Extension - Works for SQL Developer 1.5.5440 and higher.
Eclipse Plugin - Works for versions 3.3 and 3.4
Licencing
OrindaBuild is not free software. We have no problem with you downloading it as often as you want but we expect you to buy a license if you use it in a commercial environment. Note that OrindaBuild does not have a run time engine - it has a run time library, but license holders get the source and thus end up with a 100% source code solution. In practice you only need 1 copy of OrindaBuild per project.
Labels: Arrays, JDBC, OrindaBuild, PL/SQL
