Oracle JDBC Proxy driver

Introduction

Oracle JDBC Proxy driver is an alternative to the Oracle SQL Translation Framework for applications that use JDBC to connect to an Oracle Database. Oracle JDBC Proxy driver is designed to change the SQL statements being executed without changing the JVM based application (Spring/J2EE/servlets etc) itself. During initialization, Oracle JDBC Proxy driver must receive a URL pointing to the mapping file and connection parameters to the Oracle database. The mapping file is read into memory (ConcurrentHashMap or Chronicle Map). When an application executes a SQL statement, the driver computes the SQL_ID without roundtrip to the database, and if that SQL_ID is contained in the mapping, it replaces the original SQL text with the SQL text from mapping structure.

Project goals

Since Oracle SQL Translation Framework for JDBC: * For Oracle Database 12c+ * Uses LOB storage in the SQLTXL_SQL$ table to store translations * For the Oracle JDBC driver to work when the parameter oracle.jdbc.sqlTranslationProfile is set, an additional undocumented jar library dependency is required (version depends on version of Oracle JDBC driver)

this project was made. The project’s goals are: * support for the immutability of the JVM based application (Spring/J2EE/servlets etc) using the Oracle Database while changing database engine to another technology * support for JDK versions (and corresponding JDBC specifications) 1.6, 1.8, 11, and 17 * using the human-readable YAML format to store the mapping between SQL_ID of original SQL statement and text of it’s replacement * no additional dependencies when using YAML format to store the mapping, you just need to add one small JAR library to Java Classpath, application server, web application, $JAVA_HOME/jre/lib/ext, etc * applications often contain thousands of SQL statements that need to be changed and the texts of these SQL statements are tens and hundreds of megabytes, and in this case, storing mapping in Java Heap using ConcurrentHashMap is resource-intensive and therefore Chronicle Map is used as an option for Oracle JDBC Proxy driver with JDK 1.8+, providing off-heap storage.

MigDevOps

To simplify database migration and reduce TCO, we are planning to launch a service in the near future, which will automatically generate a mapping file based on trace files (SQL Trace, or 'event 10046, level 1', etc) containing the user activity. If you need such a file/automation immediately, then write us an email at oracle@a2-solutions.eu

Using the Oracle JDBC Proxy Driver

To work with the Oracle JDBC Proxy driver you need to change the original connection string as follows:

1. Change connection string prefix from jdbc:oracle:thin to jdbc:proxy:oracle

2. Add mandatory Oracle JDBC Proxy driver parameter a2.mapping.file.url

For example, the original JDBC connection string

jdbc:oracle:thin:@ora001.a2-solutions.eu:1521/TESTDATA

should be changed to

jdbc:proxy:oracle:@ora001.a2-solutions.eu:1521/TESTDATA?a2.mapping.file.url=file:///Users/averemee/projects/TESTDATA/mapping/TG4ODBC.yaml

Oracle JDBC Proxy Driver parameters

a2.mapping.file.url - mandatory parameter, URL pointing to a file containing mapping

a2.mapping.file.type - optional parameter, type of a file containing mapping. Allowed values: yaml and chronical (only for JDK1.8+). Default value - yaml

Distribution and releases

Oracle JDBC Proxy driver versions correspond to different branch names. The first number in the version string, a.k.a. versionMajor, is the JDK version, the second number in the version string, a.k.a. versionMinor, is the major version of the Oracle JDBC driver being proxied. The table below contains additional information about versions and releases

Driver Version	Branch Name	Maven Central	JDK Version	Oracle JDBC
6.11.0	ojdbc6-11.2.0.4	https://mvnrepository.com/artifact/solutions.a2.oracle/orajdbc-proxy/6.11.0	1.6	ojdbc6-11.2.0.4
8.12.0	ojdbc8-12.2.0.1	https://mvnrepository.com/artifact/solutions.a2.oracle/orajdbc-proxy/8.12.0	1.8	ojdbc8-12.2.0.1
8.18.0	ojdbc8-18.15.0.0	https://mvnrepository.com/artifact/solutions.a2.oracle/orajdbc-proxy/8.18.0	1.8	ojdbc8-18.15.0.0
8.19.0	ojdbc8-19.19.0.0	https://mvnrepository.com/artifact/solutions.a2.oracle/orajdbc-proxy/8.19.0	1.8	ojdbc8-19.19.0.0
8.21.0	ojdbc8-21.10.0.0	https://mvnrepository.com/artifact/solutions.a2.oracle/orajdbc-proxy/8.21.0	1.8	ojdbc8-21.10.0.0

Building from source

After you’ve downloaded the code from GitHub, you can build it using Gradle. Use this command:

gradle clean build

The generated jar files can be found at: build/libs/.

An uber jar containing the library and all its relocated dependencies except the Oracle JDBC driver can also be built. Use this command:

gradle clean shadowJar

The generated uber jar file can also be found at: build/libs/. At runtime, the uber jar expects to find the Oracle JDBC driver on the classpath.

Validating secure dependencies

To ensure no security vulnerabilities in the dependency libraries, run the following.

gradle dependencyCheckAnalyze

If the above reports any vulnerabilities, upgrade dependencies to use the respective latest versions.

Version and release history

6.11.0 (MAY-2023)

JDK1.6 and ojdbc6-11.2.0.4

8.12.0 (MAY-2023)

JDK1.8 and ojdbc8-12.2.0.1

Authors

Aleksej Veremeev - Initial work - A2 Rešitve d.o.o.

License

This project is licensed under the Apache-2.0 License.