SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: CREATE TYPE numbers AS TABLE (i … Continue reading jOOQ 3.10 Supports SQL Server’s Table Valued Parameters
Tag: stored procedures
Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ
The current edition of the Java magazine has an article about Big Data Best Practices for JDBC and JPA by Josh Juneau: http://www.javamagazine.mozaicreader.com/MayJune2016 The article shows how to use a stored procedure with JDBC (notice how resources aren't closed, unfortunately. This is commonly forgotten, even in Java Magazine articles) // Using JDBC to call upon … Continue reading Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ
Access PL/SQL Procedures From Java with jOOQ, a JPublisher Alternative
A procedural language combined with SQL can do miracles in terms of productiveness, performance and expressivity. In this article, we'll see later on, how we can achieve the same with SQL (and PL/SQL) in Java, using jOOQ, which offers much more functionality than Oracle's own now desupported JPublisher. But first, a little bit of history... … Continue reading Access PL/SQL Procedures From Java with jOOQ, a JPublisher Alternative
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
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?
Stored procedures returning multiple cursors
When adding support for Sybase ASE to jOOQ, I came across a peculiar procedure that can be used instead of the dictionary tables, to query for schema meta information (see also my previous post about database schema navigation). With Sybase ASE, you can call a procedure (or pragma?) called sp_help, which will return a cursor … Continue reading Stored procedures returning multiple cursors
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