Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows:
CREATE TYPE array_parameters AS ( v_name VARCHAR2(255), v_value CLOB );
This structure, which can have equivalent definitions in most modern relational databases, is being proposed as a way to pass an arbitrary number of name/value pairs into a stored procedure. The stored procedure call resembles:
SELECT rxm( '...map...', array_parameters );
...map... can include any number of variable references, taking the following form:
account.id = $id && person.last_name = $surname && ...
array_parameters, in theory, could be populated as:
array_parameters.v_name = "$id"; array_parameters.v_value = "123456789"; array_parameters.v_name = "$surname"; array_parameters.v_value = "O'Malley, The \"Great\"";
- Unsupported by Oracle
- Unsupported by MySQL
- Unsupported by Microsoft SQL Server
- Unsupported by Apache Derby
- Unsupported by Sybase
Without the ability to create the name/value pair array, I can see no obvious way to pass in the values without resorting to database-specific implementations (such as using Oracle's ARRAY, or obtuse contortions to support MySQL).
How would you define and then call a stored procedure that can take an arbitrary number of name/value pairs in a database-agnostic fashion?
One idea would be to define two string arrays, rather than an object array structure, and call the stored procedure as follows:
SELECT rxm( '...map...', array_names, array_values );
The two arrays would be index-linked, but this likely depends on
createArrayOf(), as well.
It might be possible to pass the pairings as comma-separated strings. However, the values could contain commas themselves, which makes parameter encoding using comma-separated strings tricky. (Generally speaking, any separator can appear as a character somewhere in the values, which includes escaped separators as well, such as
This seems like the most database-agnostic solution, but implementing a CSV decoding routine across multiple databases in PL/SQL is neither trivial nor efficient.
Use Hibernate as an abstraction layer, then implement a JPQL routine that passes in the array of name/value pairs. For example, calling query.setParameterList, which might only work for
IN clauses, rather than stored procedure parameters.