Call PL / SQL procedure with collection of objects

Question

Call PL / SQL procedure with collection of objects

Navigation

#1 by (2 votes)

6

Consider the following types:

CREATE TYPE meu_tipo AS OBJECT (
  meu_id       NUMBER(6),
  meu_nome     VARCHAR2(200)
);

CREATE TYPE meu_tipo_tabela AS TABLE OF meu_tipo;

And the following package:

create or replace package pkg_test
is
    procedure meu_procedure(tabela in meu_tipo_tabela);
end;

On the PL / SQL side we could call this procedure as follows:

declare
    var_minha_tabela meu_tipo_tabela := meu_tipo_tabela(
        meu_tipo(1,'John'),
        meu_tipo(2,'Doe'),
        meu_tipo(3,'Snow'));
begin
    pkg_test.meu_procedure(var_minha_tabela);
end;

SQL Fiddle

My question is: How do I make this same call with Java and JDBC?

public void chamarProcedure(List<MeuTipo> minhaLista) {
    final String chamada = "{call PKG_TEST.MEU_PROCEDURE(?)}";
    try (Connection connection = getDataSource().getConnection();
         CallableStatement callableSt = connection.prepareCall(chamada)) {
         // ??? - Código para criar um ARRAY, STRUCT ou algo assim a partir da lista.
         callableSt.setArray(1, ???);
         callableSt.executeUpdate();   
    } catch (SQLException e) {
        logger.error("Erro ao chamar procedure", e);
        // Lançamento de exceção não checada
    }
}

In particular I believe that I should somehow create a oracle.sql.ARRAY of meu_tipo_tabela , however, I could not find any examples of how to do this (called with ARRAY of complex objects).

java oracle jdbc pl-sql

asked by anonymous 03.12.2014 / 14:22

1 answer

Image Cutting How to list and monitor the app's that are installed and / or in use?

score 2 · Accepted Answer

Posting the solution to other users who may encounter the same problem.

The comment marcus helped me kill the charade.

Actually what we need to build is ARRAY of STRUCTS . Each of these structures requires a TypeDescriptor and an array of attributes (in the case of STRUCT we can also use Map of attributes, see the builders of STRUCT ). Since the solution demo is somewhat complex, I created a repository on GitHub .

Here is a brief description of the technique used:

The procedure call follows the template of the question. The input parameter is of type owner oracle.sql.ARRAY :

public void chamarProcedure(List<MeuTipo> minhaLista) {
    final String chamada = "{call PKG_TEST.MEU_PROCEDURE(?)}";
    try (Connection connection = getConnection();
         CallableStatement callableSt = connection.prepareCall(chamada)) {

        final ARRAY minhaTabela = toArray(minhaLista, connection);
        callableSt.setArray(1, minhaTabela);
        callableSt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The toArray method creates an array to type STRUCT[] from list elements. The STRUCT[] is then wrapped with a oracle.sql.ARRAY . To do so, the descriptors for MEU_TIPO and MEU_TIPO_TABELA are also created:

private ARRAY toArray(List<MeuTipo> minhaLista, Connection connection) 
        throws SQLException {
    final STRUCT[] structArray = new STRUCT[minhaLista.size()];
    final ListIterator<MeuTipo> iterator = minhaLista.listIterator();
    final StructDescriptor structDescriptor = StructDescriptor
            .createDescriptor("MEU_TIPO", connection);
    while (iterator.hasNext()) {
        structArray[iterator.nextIndex()] = 
               toStruct(iterator.next(), structDescriptor, connection);
    }
    final ArrayDescriptor arrayDescriptor = ArrayDescriptor
            .createDescriptor("MEU_TIPO_TABELA", connection);

    return new ARRAY(arrayDescriptor, connection, structArray);
}

Finally, the toStruct method creates a Object[] from an item to type MeuTipo . As in step 2 the Object[] is wrapped with a oracle.sql.STRUCT . The values in Object[] must obey the order of the object attributes declared on the Oracle side:

private STRUCT toStruct(MeuTipo meuTipo, StructDescriptor structDescriptor, Connection connection) throws SQLException {
    final Object[] attributes = new Object[]{meuTipo.getMeuId(), meuTipo.getMeuNome()};
    return new STRUCT(structDescriptor, connection, attributes);
}

Reference: SOen - How to call oracle stored procedure which include user-defined type in java? / a>