Um dos grandes problemas que enfrentamos em nossos projetos é o envio e retorno de strings concatenadas entre o Oracle e o C#. Geralmente usamos esse recurso para retornar as associações de uma entidade ou efetuar alguma operação com as N associações, por exemplo:

Existem diversas técnicas para isso, porém todas que utilizamos até hoje se baseavam em converter a tabela de valores em uma string concatenada e depois aplicarmos alguma função de split no destino para poder efetuar as manipulações necessárias. Trabalhamos inclusive com funções não documentadas, mas ao atuar com uma massa grande de dados sempre tivemos problema de performance.

Trabalhando com o provider da Oracle ODP.NET temos suporte ao mapeamento de UDTs (User Defined Types), que permite a representação de entidades complexas do Oracle no C#, fazendo tarefas como esta de forma simples e rápida.

Comparação entre algumas técnicas:

FUNÇÃO LIMITAÇÃO TEMPO (retorno de 5.000 itens)
WM_CONCAT Função não documentada 83s
LISTAGG Limite de 4000 caracteres 5s
UDT Não 0.9s

Nesse post eu vou mostrar como utilizar o UDT para enviar e receber coleções de números do Oracle.

Implementando

Siga os passos abaixo:

Banco de Dados

Crie o UDT no Oracle com o tipo tabela de números.

create or replace TYPE TABLE_NUMBER AS TABLE OF NUMBER;

Vamos criar e popular uma tabela para simular os dados agrupados.

CREATE TABLE TB_PEDIDO
(
IDPEDIDO NUMBER
, IDPRODUTO NUMBER
);
DECLARE
pedido  NUMBER := 1;
produto NUMBER := 1;
itens   NUMBER := 1;
BEGINWHILE itens <= 100000
LOOP
INSERT INTO TB_PEDIDO VALUES (pedido, produto);produto := produto + 1;
itens   := itens + 1;IF produto > 20 THEN
pedido := pedido + 1;
produto := 1;
END IF;
END LOOP;COMMIT;
END;

Estamos simulando uma tabela de Pedidos X Produtos, vamos trabalhar sempre com as chaves de produtos relacionadas a um pedido.

Crie as procedures para busca e envio de dados ao Oracle.

create or replace PROCEDURE PC_SELPEDIDOPRODUTO
(
O_CURSOR out sys_refcursor
)
IS
BEGIN
–Retorna o pedido e as chaves de produtos associadas
OPEN O_CURSOR for
SELECT
CAST(COLLECT(IdProduto) AS TABLE_NUMBER) AS IdProdutos,
IdPedido as IdPedido
FROM
TB_PEDIDO
GROUP BY IdPedido;END;
create or replace PROCEDURE PC_INSPEDIDOPRODUTO
(
P_PRODUTOS IN TABLE_NUMBER,  –Recebe as chaves de produtos
O_CURSOR out sys_refcursor
)
IS
BEGIN
–Retorna os dados recebidos
OPEN O_CURSOR FOR
SELECT COLUMN_VALUE IdProdutos FROM TABLE(P_PRODUTOS);END;

Aplicação

Vamos criar as classes para mapeamento do UDT. Para isso, é necessário a criação de duas classes.

  • Custom Type Factory: Deve implementar as interfaces IOracleCustomTypeFactory e IOracleArrayTypeFactory, é utilizada pelo ODP.NET pra efetuar o mapeamento para o tipo customizado (o mapeamento é realizado em tempo de execução via reflection).
  • Custom Type: Deve implementar a interface IOracleCustomType , é a classe que possui a definição do tipo customizado.

Segue o código das classes para mapeamento

#region [ Oracle – Custom Types ]
public class OracleCustomType
{
public const string ORACLETYPE_NUMBER = “TABLE_NUMBER”;
}#region [ Array de Número ]
public class OracleListNumber : IOracleCustomType, INullable
{#region [ Propriedades ]
[OracleArrayMapping()]
public Int64[] Itens;public OracleUdtStatus[] Status { get; set; }private bool isNull;
public bool IsNull
{
get
{
return isNull;
}
}public static OracleListNumber Null
{
get
{
OracleListNumber obj = new OracleListNumber();
obj.isNull = true;
return obj;
}
}
#endregionpublic OracleListNumber() { }public OracleListNumber(object valores)
{
if (valores is List<Int64>)
this.Itens = ((List<Int64>)valores).ToArray();
else if (valores is Int64[])
this.Itens = (Int64[])valores;
else
throw new InvalidCastException(String.Format(“Conversão para o tipo {0} não implementada”, valores.GetType().FullName));
}public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
object objectStatusArray = null;
Itens = (Int64[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
Status = (OracleUdtStatus[])objectStatusArray;
}

public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, Itens, Status);
}

#region [ Métodos para Conversão ]
/// <summary>Efetua a conversão para lista de int</summary>
/// <param name=”item”>Instância do item</param>
/// <returns>Lista de parâmetros</returns>
public static implicit operator List<Int64>(OracleListNumber item)
{
if (item == null)
return null;
return new List<Int64>(item.Itens);
}

/// <summary>Efetua a conversão para lista de int</summary>
/// <param name=”item”>Instância do item</param>
/// <returns>Lista de parâmetros</returns>
public static implicit operator Int64[] (OracleListNumber item)
{
if (item == null)
return null;
return item.Itens;
}

/// <summary>Efetua a conversão para o objeto Oracle</summary>
/// <param name=”item”>Lista de itens</param>
/// <returns>Lista de parâmetros</returns>
public static implicit operator OracleListNumber(List<Int64> valores)
{
OracleListNumber retorno = new OracleListNumber();
retorno.Itens = valores.ToArray();
return retorno;
}

/// <summary>Efetua a conversão para o objeto Oracle</summary>
/// <param name=”item”>Lista de itens</param>
/// <returns>Lista de parâmetros</returns>
public static implicit operator OracleListNumber(Int64[] valores)
{
OracleListNumber retorno = new OracleListNumber();
retorno.Itens = valores;
return retorno;
}
#endregion
}

[OracleCustomTypeMappingAttribute(OracleCustomType.ORACLETYPE_NUMBER)]
public class OracleListNumberFactory : IOracleArrayTypeFactory, IOracleCustomTypeFactory
{
public virtual IOracleCustomType CreateObject()
{
return new OracleListNumber();
}

public Array CreateArray(int numElems)
{
return new Int64[numElems];
}

public Array CreateStatusArray(int numElems)
{
return new OracleUdtStatus[numElems];
}
}
#endregion
#endregion

Com as classes de mapeamento do Oracle já implementadas, o processo para a recuperação do tipo customizado e mapeamento para a nossa classe criada OracleListNumber é feito pelo ODP.NET. Ele faz isso através de reflection em tempo de execução, procurando no assembly do código a classe que possui o annotation (OracleCustomTypeMappingAttribute) com a definição do tipo que estamos usando.

Para retornarmos o tipo customizado do banco de dados, basta realizarmos a execução da procedure definindo o campo retornado para o tipo customizado do Oracle que criamos.

Exemplo de como os dados são retornados:

Para enviarmos o tipo customizado para o banco de dados, basta realizarmos a execução passando como parâmetro o tipo customizado.

Notem que estou convertendo o tipo OracleListNumber diretamente para List<Int64>. Isso é possível porque eu fiz a implementação da conversão implícita na classe  OracleListNumber.

Conclusão

A utilização de mapeamento de tipos customizados (UDT) pelo OPD.NET é um recurso pouco explorado e que pode facilitar muito nosso dia a dia, além de apresentar um ótimo desempenho.

Referências