JDBC 12c: Do you reimplement Forms with PL/SQL Types in ADF ? You should look at Database 12c first !

Many customers who want to go  the next step in their Oracle technology strategy towards ADF have  a lot of Forms and PL/SQL Code in the back.

One of a widely used design pattern in Oracle Forms is the use of PL/SQL Types (type mytyp of employees%rowtype, type mytbl table of mytyp) to build up a clean interface between Forms and the Database.

Before Database 12cR1 there was no way to use this PL/SQL Types with the Oracle JDBC drivers. Beginning with the JDBC 12.1.0.x and the 12gR1 database now this is possible.

So you don't need anymore to write or generate PL/SQL Types to SQL Object Types and back again.

Often time the tool JPublisher is used for this part of code generation.

So here my test drive:

Environment 

- JDeveloper 12cR1

- Oracle Database 12cR1

- JDBC Driver 12cR1

- HR Schema

1.) Database Code with PL/SQL Type 

JDeveloper 12c

First of all the new JDeveloper doesn't use the 12c JDBC driver out of the box. So you have to bring the JARs by yourself into the Classpath. Do not replace the old driver in oracle.modules - path. This was a no go for me. Separate the things !

Download: http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html

You need: ojdbc7dms.jar and orai18n.jar (7 for JDK  / 6 for JDK 6)

For more informationen about JDBC 12c with WLS 12c and 10.3.6 take a look at MOS Article: 1564509.1

1.) Use JDBC 12c with JDeveloper 12c

For the test drive put the JDBC driver in front of everything to be sure the right classes are used by the class loader. Maybe you setup up a JDeveloper Library. 

2.) Java Sample Code

3.) Output

Conclusion 

I tried the same with Database 11gR2 Express Edition and i got the following error:

SQLException invalid name pattern: ….

I think this feature is only available with Database 12cR1. 

So if your timeline allows you to bring Database 12c in your project and you have  a lot of PL/SQL Types inside your Database you should have to think twice. Maybe you can save all the plsql wrapper code with Database 12c.

More Information

Oracle® Database JDBC Developer's Guide - 12c Release 1 (12.1)

http://docs.oracle.com/cd/E16655_01/java.121/e17657/apxref.htm#JJDBC29032