Call PL/SQL precedent with object collection

Asked

Viewed 2,639 times

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 I must somehow create a oracle.sql.ARRAY of meu_tipo_tabela, however, I couldn’t find any example of how to do this (called with ARRAY of complex objects).

java oracle jdbc pl-sql

  • I found that example with an array of primitives. The same strategy did not work for my complex type array.

  • In the final line String called = "{call PKG_TEST.MEU_PRODECURE(? )}"; did you not try to exchange Bing for the direct passage ? something like end String called = "{call PKG_TEST.MEU_PRODECURE(1,'John')}"; ?

  • Unfortunately this does not work (not simple parameters, it is a collection of complex objects according to the example PL/SQL call).

  • I understand , with a CAST maybe String called = "{call PKG_TEST.MEU_PRODECURE( cast(1,'John') as meu_type)}"; but these solutions I speak to sound like "gambiarra" should have an even more correct form in java

  • 1

    What is the error that occurs when trying to turn the example with a collection of primitives into a collection of complex objects? Have you seen the Oracle JDBC driver documentation? The link to the 10g version is this: http://docs.oracle.com/cd/B19306_01/java.102/b14355/toc.htm There are examples of how to use STRUCT and ARRAY, although you also haven’t found an example combining the two yet.

1 answer

2


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

The commenting of Marcus helped me kill the riddle.

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

A brief description of the technique used:

  1. The call of Procedure follows the template question. The input parameter is of the proprietary type 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();
    }
}

  2. The method toArray creates an array to type STRUCT[] from the list items. The STRUCT[] is then wrapped with a oracle.sql.ARRAY. To this end, the descriptors for MEU_TIPO and MEU_TIPO_TABELA:

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

  3. Finally the method toStruct creates a Object[] from an item to type MeuTipo. As in step 2 o Object[] is wrapped with a oracle.sql.STRUCT. The values in the Object[] must obey the order of attributes of the declared object 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?

Browser other questions tagged java oracle jdbc pl-sql

You are not signed in. Login or sign up in order to post.