Background

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 );

Where the ...map... can include any number of variable references, taking the following form:

account.id = $id &&
person.last_name = $surname && ...

The array_parameters, in theory, could be populated as:

array_parameters[0].v_name = "$id";
array_parameters[0].v_value = "123456789";
array_parameters[1].v_name = "$surname";
array_parameters[1].v_value = "O'Malley, The \"Great\"";

Problem

JDBC4 defines a method called createArrayOf, which is the New South China Mall of APIs:

• 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).

Question

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?

Idea #1

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.

Idea #2

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.

Idea #3

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.