Using jOOQ's code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: CREATE OR REPLACE PROCEDURE p ( p_i1 IN number, p_o1 OUT number, p_i2 IN varchar2, p_o2 OUT varchar2 ) IS BEGIN p_o1 := p_i1; p_o2 := p_i2; … Continue reading Calling Procedures with Default Parameters using JDBC or jOOQ
Tag: stored functions
The Best Way to Call Stored Procedures from Java: With jOOQ
jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This … Continue reading The Best Way to Call Stored Procedures from Java: With jOOQ
Vendor Agnostic, Dynamic Procedural Logic with jOOQ
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic, Dynamic Procedural Logic with jOOQ
jOOQ as a “PL/Java” language
Some people who get in touch with PL/SQL, PL/pgSQL, T-SQL, or any other proprietary procedural language for SQL interaction are probably missing out on a couple of language integration features in the Java world. Most Java APIs see SQL as an external domain-specific language that is "best" dealt with using string concatenation. Such APIs include: … Continue reading jOOQ as a “PL/Java” language
Use jOOQ inside your H2 database
I recently became aware of an interesting use-case for jOOQ when I was optimising my own H2 database integration tests: H2 stored functions H2 knows two operation modes for stored functions: "Inline mode" with source code provided "Reference mode" referencing a public static method of a Java class on the databases' classpath The above terms … Continue reading Use jOOQ inside your H2 database
What are procedures and functions after all?
Many RDBMS support the concept of "routines", usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are: Ada BASIC Pascal etc... The general distinction between (stored) procedures and (stored) functions can be summarised like this: Procedures: Are … Continue reading What are procedures and functions after all?
Oracle scalar subquery caching
The importance of being able to fully control executed SQL (using jOOQ, or plain JDBC) on large-scale systems becomes obvious every time you need to fine-tune your SQL queries for a specific RDBMS. In this case, we're looking at Oracle and its miraculous scalar subquery caching mechanisms: Usually, the context switch from SQL to PL/SQL … Continue reading Oracle scalar subquery caching
Oracle’s object-oriented PL/SQL extensions
I have recently re-discovered an interesting feature of Oracle's PL/SQL language. Not only can you define your own types very easily, you can also associate "methods" to them, as in other object-oriented languages. Oracle calls those "methods" member functions and member procedures. This is documented here, for example: https://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm#i477669 So you can define your own … Continue reading Oracle’s object-oriented PL/SQL extensions
The power of REF CURSOR types
Many RDBMS have started implementing support for some sort of CURSOR, REF CURSOR or ARRAY/TABLE types. These types have roughly the same semantics as JDBC's java.sql.Array and java.sql.ResultSet. In principle, such types can appear anywhere in SQL, even if some RDBMS limit support to a certain functionality subset. What exactly are these types? ARRAY types … Continue reading The power of REF CURSOR types