Oracle Stored procedure Structured Parameters

Question

I wish to pass a bunch of 'unknown' values to an Oracle stored procedure from a spring java application. The data is somewhat structured, so currently we have a stored proc that has accepts 2 clobs what are key/value pairs of the data. The first clob represents a single record amd the second clob, represents many child records of clob 1.

This seems a very inefficient way to pass the data as we have to construct strings in java in the forst plave and then the data has to be parsed in the stored proc.

I have looked into Oracle record structures, however it appears as though you have to map each field in the record structure to a db table field. the problem with this approach is a) the data items that we send each time differ (although there is a core set of data that remains the same) and b) some of the data items are there for decision making purposes only and are not actualy persisted to the database.

So my question is: what is the most efficient vehicle to pass such data to the Oracle stored proc? We want to maintain the flexibilty of being able to send variable parameter sets along with having some semblance of structure around the data.

Thanks in advance.

Barry

I've got an answer for u if u accept answers from some of your previous questions! — tbone, Feb 13 '12 at 12:37
@tbone answers now accepted. Would appreciate your suggestion :) — Baz R, Feb 13 '12 at 13:58
at first was thinking along lines of XMLType, but seeing Lukas' comments u rejected that approach. Can u give an example of the data you need to pass to Oracle? — tbone, Feb 13 '12 at 14:34

score 3 · Answer 1 · edited May 23 '17 at 12:20

Have you considered passing your data to the stored procedure as XML? Oracle can handle XML data types. There are also some related questions on Stack Overflow:

However, XML may be a performance killer in some situations. Another option is to use REF CURSOR types:

PreparedStatement stmt = connection.prepareStatement(
    "DECLARE "
  + "  records SYS_REFCURSOR; "
  + "BEGIN "
  + "  OPEN records FOR "
  + "  SELECT * FROM TABLE(?); "
  + "  my_proc(records); "
  + "END;");

// Set the records as an array
stmt.setArray(1, records);

That would be a way to somewhat structure the data and yet operate on weakly typed cursors. The above select could have any form. In this example, I'm assuming that you will be binding something like this:

CREATE TYPE rec AS OBJECT (ID NUMBER(7), VALUE CLOB);
CREATE TYPE tab AS TABLE OF rec;

A simple example procedure implementation expecting a TABLE OF VARCHAR2 REF CURSOR:

CREATE OR REPLACE PROCEDURE my_proc(cur IN SYS_REFCURSOR) IS
  -- Using a pre-existing TABLE TYPE from the SYS schema for the example
  array ORA_MINING_VARCHAR2_NT;
BEGIN
  FETCH cur BULK COLLECT INTO array;

  FOR i IN array.FIRST .. array.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE(array(i));
  END LOOP;
END;

The JDBC binding would then be

String[] strings = new String[] {"a", "b", "c"};
ArrayDescriptor desc = new ArrayDescriptor("ORA_MINING_VARCHAR2_NT", c);
ARRAY array = new ARRAY(desc, c, strings);
stmt.setArray(1, array);
stmt.executeUpdate();

With TABLE OF OBJECT data types, the binding is a bit more tricky...

I should have mentioned that we will be passing thorugh thousands of these records, so performance is an issue, we rejected xml for this reason. — Baz R, Feb 13 '12 at 11:23
Thanks, this may be an option. The problem I guess is that we want to keep the data slightly unstructured for flexibility, but marshall it around in a structured way, whist maintaining a reasonable level of performance. So having our cake and eating it :) — Baz R, Feb 13 '12 at 14:37
@BazR: The only two usable options I know of for "type-half-safety" in Oracle are `XML` and `REF CURSOR` - both of which are kind of a pain to use through JDBC... I've been adding support for such features to http://www.jooq.org, but there are constant surprises :-) I'd be curious how the cursor solution works out for you, though — Lukas Eder, Feb 13 '12 at 14:47

Oracle Stored procedure Structured Parameters

1 Answers1