CODEWITHSUNDEEP
jdbcoracle10g
Roger
Roger

Reputation: 2952

How to call a procedure with associative arrays in Oracle from Java

I have an stored procedure that looks like this:

TYPE ref_cursor IS REF CURSOR;    
TYPE parametro IS RECORD (
    nombre      VARCHAR2(50), -- I want to remove this value and make it the key of the table instead.
    valor       VARCHAR2(32000),
    tipo        VARCHAR2(1),
    sentencia   VARCHAR2(32000)
);
TYPE parametros IS TABLE OF parametro INDEX BY VARCHAR2(50);

PROCEDURE build_cursor (
    params IN parametros
    results OUT ref_cursor
);

And from the build_cursor procedure, I want to be able to access to the contents of the table by its key.

parametros('key');

However, I don't know how to build an associative array from Java, I have seen only examples of simple arrays, i.e: TYPE parametros IS TABLE OF parametro;

How can I call the build_cursor procedure from java?

I read this: How to call oracle stored procedure which include user-defined type in java? but I don't know what changes do I have to make to his java example for creating the associative array; Where do I put the Key of the current element?

This is a working test from Oracle.

params('key').nombre        := 'key'; -- I want this to be removed because it's the key.
params('key').valor         := 'Roger';
params('key').tipo          := 'V';
params('key').sentencia     := 'Something';
-- Call the procedure
pk_sql_utils.build_cursor(
    params => params,
    results => :results
);

Upvotes: 1

Views: 3357

Answers (3)

p3consulting
p3consulting

Reputation: 4640

The official Oracle samples at https://github.com/oracle-samples/oracle-db-examples/ has examples in the java/jdbc/AdvancedSamples/ folder.

You may need to replace some cast "(OracleArray)" by "(ARRAY)" because of error "(oracle.sql.ARRAY and oracle.jdbc.driver.OracleArray are in unnamed module of loader 'app')" with recent JDKs.

Upvotes: 0

Dan Sievewright
Dan Sievewright

Reputation: 1

The accepted answer is incorrect. If you're using the ojdbc driver, you can create a class that implements SQLData for parametro. Then create a class that implements OracleData for parametros. Below answer has an example with ROWTYPE, but the solution is essentially the same.

How do I call a PL/SQL procedure with associative arrays?

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

Only SQL objects can be referenced by jdbc, not PL/SQL objects. Associative arrays are PL/SQL objects, so you won't be able to "see" them from jdbc.

You could use a wrapper PL/SQL function with SQL objects (an associative array is analogous to one nested table of indexes and one nested table of values).

You could also use a temporary table: jdbc batch inserts into the temp table, a wrapper procedure (or PL/SQL block) reads the temp table and calls your procedure.

Upvotes: 2

Related Questions