Enviando e recebendo coleções no Oracle
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) #region [ Métodos para Conversão ]
/// <summary>Efetua a conversão para lista de int</summary> /// <summary>Efetua a conversão para lista de int</summary> /// <summary>Efetua a conversão para o objeto Oracle</summary> /// <summary>Efetua a conversão para o objeto Oracle</summary> { public virtual IOracleCustomType CreateObject() { return new OracleListNumber(); } public Array CreateArray(int numElems) public Array CreateStatusArray(int numElems) |
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.
