Using SQL Translation Framework to Rewrite Bad SQL on the Fly
Oracle 12c introduced a new feature, SQL Translation Framework (STF), that allows the database to replace SQL statements on the fly. That is to say, the database can be set up to recognize specific SQL statements and replace them with other SQL statements or make programmatic changes to all SQL. The feature was designed to allow applications written for other databases to run without changes, via the ability to replace non-compatible syntax with Oracle equivalent syntax. For example, there is a pre-built Sybase ASE translation package that is designed to translate the Sybase dialect of SQL into the Oracle SQL dialect. When I saw the feature my first thought was that I might be able to use it to fix badly written SQL behind the scenes (i.e. without changing the application code). And as it turns out, SFT gives us the tool kit we need to do just that. This presentation digs into the techniques used to implement this capability with real world examples.