Uma vez, ao implementar um programa que precisava comunicar dois bancos de dados Oracle 11g diferentes, e em tempo real, uma limitação do próprio banco de dados em enviar de dados CLOB de um banco para o outro via DB link tornou-se um grande problema. Caso este problema não fosse contornado de alguma maneira, não seria possível fazer a integração entre os bancos em tempo real. Como esta limitação está relacionada ao tamanho que os dados CLOB comportam, a solução foi dividir as informações a serem transferidas em blocos informações menores e em um tipo de dado que permitisse a transferência das informações entre os bancos de dados, neste caso, o VARCHAR2.
Mas o que é um DB Link?
Conforme a documentação fornecida pela Oracle, um DB Link é um objeto de esquema que permite o acesso aos objetos à partir de outro banco de dados.
Após a criação do link, é possível acessar os objetos do outro banco através de instruções SQL, adicionando "@nomedblink" logo após o nome do objeto. Também é possível executar outras instruções SQL, como por exemplo INSERT, UPDATE, DELETE, LOCK TABLE.
Por sorte, a própria Oracle já fornece algumas funções para a manipulação de dados de tipo "LOB" (Large Objects). Estas funções se encontram dentro do pacote "dbms_lob".
Para a solução deste problema foram usadas apenas 4 funções, que são:
Função "dbms_lob.substr"
Utilizada para ler um tipo de dado CLOB, NCLOB ou BLOB à partir de um offset ("deslocamento").
"[…] É uma função passada-por-valor que retorna um tipo de dados RAW para tipos BFILE e BLOB e VARCHAR2 para CLOB ou NCLOB. [...]" (MCLAUGHLIN, 2009, p.311).
Em outras palavras, com esta função é possível retornar um pedaço de um texto armazenado dentro de um campo CLOB, por exemplo.
Função "dbms_lob.createtemporary"
Cria um BLOB, CLOB ou NCLOB temporário na memória. "[…] Os LOBs temporários são entidades de limite de tempo[...]" (MCLAUGHLIN, 2009, p.313), ou seja, somem da memória assim que o processo é finalizado (ou assim que são explicitamente "destruídos").
Função "dbms_lob.append"
É utilizado para adicionar conteúdos ao final de um LOB. (MCLAUGHLIN, 2009, p.304).
Função "dbms_lob.freetemporary"
É utilizado para liberar a memória consumida por um BLOB, CLOB ou NCLOB criado através da função "dbms_lob.createtemporary". Recomenda-se que esta função sempre deva ser utilizada ao trabalhar com LOBs temporários. (MCLAUGHLIN, 2009, p.313).
Implementação
Para o desenvolvimento deste exemplo fez-se uso do banco Oracle 11g Express Edition, por ser uma versão gratuita.
A título de exemplificação, foram criados 2 usuários. "user_emissor" e "user_externo". O usuário "user_emissor" representará o banco de dados que contém a origem das informações, ou seja, que contém a tabela com uma coluna do tipo de dado CLOB. Já o usuário "user_externo" representará um banco de dados externo que precisa fazer o acesso ao banco proprietário das informações ("user_emissor").
Para que a comunicação entre os usuário seja possível, é necessária a criação de pelo menos 2 objetos. Sendo um pacote que fará a serialização do CLOB num vetor de VARCHAR2 no usuário "user_emissor", e uma função que concatenará o vetor novamente em CLOB no usuário "user_externo". No exemplo também será criada uma view no usuário "user_externo". A implementação desta view não é obrigatória, mas facilitará a exibição dos dados.
Estrutura da tabela "exemplo_clob":
Dentro do esquema do usuário "user_emissor" existe a tabela "exemplo_clob".
 |
Diagrama Tabela "exemplo_clob".
|
Esta é a tabela que precisa ser acessada pelo usuário "user_externo".
Ela possui apenas duas colunas, sendo uma para a identificação de seus registros ("id_exemplo_clob"), e outra coluna ("texto") que armazena as informações que precisam ser lidas pelo usuário "usuario_externo".
Pacote "PKG_LOB"
Para que a integração entre os bancos de dados seja possível, é necessária a implementação de um pacote que contenha a definição de um objeto do tipo TABLE. É através deste pacote que o usuário "user_externo" irá ter acesso à estrutura de dados em que o CLOB foi serializado.
 |
Estrutura do pacote "pkg_lob". É através dela que o usuário "user_externo" terá acesso às informações do CLOB serializado.
|
A figura acima tem um exemplo da definição do pacote, e abaixo o detalhamento desta estrutura:
A linha 1 corresponde à instrução de criação do pacote
A linha 2 marca o início das definições do pacote. É a partir dela que todos os objetos e procedimento são definidos. Todos os objetos e procedimento públicos, ou seja, que podem ser acessados por outros objetos/usuário, devem estar descritos neste bloco.
Na linha 4 está a declaração de uma estrutura de dados do tipo TABLE OF VARCHAR2(4000). É a partir desta estrutura que os objetos de manipulação do CLOB serão montados. O tamanho do VARCHAR2 é de 4000, pois é o limite que uma instrução SELECT pode manipular.
Nas linhas 8 à 10 tem-se a definição da função que irá fazer a serialização do CLOB num vetor de VARCHAR2. Entre elas, na linha 9, está a declaração de um parâmetro de entrada que será utilizado para filtrar a informação desejada. Deve-se atentar à linha 10, pois ela define o retorno da função como sendo a estrutura de dados criada anteriomente.
Por fim, a linha 12 marca o final da declaração do pacote.
Corpo do pacote
 |
Estrutura do corpo do pacote "pkg_lob". Contém o algoritmo que faz a serialização do CLOB num vetor VARHCAR2.
|
O corpo do pacote é onde está contida toda a lógica (figura acima). Abaixo segue o detalhamento de sua estrutura:
A linha 1 corresponde à instrução de criação do corpo do pacote.
A linha 2 marca o início das declarações de todos os objetos e procedimentos acessíveis dentro do pacote. Nenhum objeto e/ou procedimento que esteja dentro desta declaração será público, exceto para os casos dos procedimentos estejam declarados na estrutura do pacote que permite o acesso externo.
Entre as linhas 6 e 38 está a função responsável por fazer a serialização do CLOB no array de VARCHAR2.
As linhas 6 a 8 criam a definição da função. Esta deve estar exatamente igual ao que foi definido na estrutura principal do pacote.
Entre as linhas 9 e 11 está a declaração da variável "v_array" do tipo TabArray, onde TabArray é o objeto TYPE criado na definição do pacote, e a declaração de uma constante ("c_max_length"), que armazena o valor máximo de cada bloco VARCHAR2 do vetor.
A linha 14 faz a inicialização do objeto vetor.
Na linha 17 está a declaração do loop que fará a serialização do CLOB em VARCHAR2.
Entre as linhas 18 e 24 estão as instruções SELECT que fazem a serialização do CLOB em VARCHAR2. Para que seja possível o entendimento desta instrução, precisa-se olhar do SELECT interno para o SELECT externo. O SELECT interno faz uso da função CEIL com a função LENGTH (linha 20). A função LENGTH faz a contagem da quantidade de caracteres contidos no CLOB. Este resultado é divido pela constante "c_max_length", que é o limite de caracteres definido para este exemplo. Ao resultado desta divisão é aplicada a função CEIL. Esta função faz com que um valor que contenha casas decimais seja arredondado para o próximo valor inteiro. Exemplo: CEIL(4,01) = 5. A este resultado da-se o alias (apelido) de "qtd_linhas"
Logo abaixo, na linha 21, o SELECT retorna a coluna "texto", que é a coluna que contém o(s) valor(es) CLOB(s) a ser(em) serializado(s).
Na linha 23 o parâmetro "pi_id_exemplo_clob" é utilizado para filtrar apenas um registro da tabela e fazer a serialização das informações deste registro.
Com base nas informações retornada pelo SELECT interno, o SELECT externo faz a serialização dos dados CLOB através da combinação da clausula CONNECT BY com a função "dbms_lob.substr". Ao utilizar a cláusula CONNECT BY com o resultado de "qtd_linhas" faz-se com que o SELECT retorne uma quantidade de linhas igual ao valor de "qtd_linhas", ou seja, se "qtd_linhas" for igual a 7, o SELECT retornará 7 linhas, por exemplo. O identificador de cada linha fica por conta da função "LEVEL", que retorna qual é a posição de cada linha na hierarquia.
O resultado de "LEVEL" possui 2 utilidades, definir o índice das posições do vetor (linha 19), e, junto da função "dbms_lob.subtr", realizar a divisão do CLOB em blocos de VARCHAR2 (linha 21).
As linhas 25 e 33 marcam, respectivamente, o início e o fim do bloco que carrega o vetor.
A linha 28 cria, através da função "EXTEND", uma nova posição para o vetor "v_array".
A linha 31 define o índice de "v_array" com o valor de "LEVEL" (ao qual foi dado alias de "i") e atribui na posição deste índice o bloco de VARCHAR2 gerado pelo SELECT.
Ao finalizar todas as iterações do loop, o vetor "v_array" é retornado através da instrução "RETURN" (linha 37).
A linha 38 marca o final da função, e a linha 44 marca o final da declaração do corpo do pacote.
Partimos agora para a função "get_exemplo_clob_texto". É através dela que a desserialização do CLOB é feita. Ela também é responsável por criar o vínculo entre os usuários "user_externo" e "user_emissor".
 |
Estrutura da função "get_exemplo_clob_texto". É responsável por fazer a desserialização do vetor e retornar um CLOB.
|
As linhas 1 e 3 marcam definição da função, que é igual à função que está dentro do pacote "pkg_lob" no usuário "user_emissor", exceto pelo tipo de dado a ser retornado, que, neste caso, é um CLOB.
Entre as linhas 5 e 7 estão declaradas 3 variáveis. Uma do tipo TabArray ("v_array") e duas do tipo CLOB ("v_temp" e "v_texto"). É importante perceber que a declaração da variável "v_array" é feita com o uso do DB link, fazendo referência ao objeto TabArray do pacote "pkg_lob", que pertence ao usuário "user_emissor". É aqui onde encontra-se todo o segredo por trás do algoritmo, pois a estrutura que é utilizada para criar o vetor passa a ser compartilhado por ambos os usuários.
Entre as linhas 12 e 14 está a chamada para a função "pkg_lob.get_exemplo_clob_texto" e armazena o valor que ela retorna dentro da variável "v_array".
Após isso, na linha 17, um objeto temporário do tipo CLOB é criado através da variável "v_temp" e a função "dbms_lob.createtemporary".
O bloco entre as linhas 20 e 22 é responsável por varrer todo o vetor, e concatenar os dados contidos dentro de cada um dos blocos deste vetor na variável "v_temp", através da função "dbms_lob.append".
Após este processamento, o resultado obtido em "v_temp" é atribuído à variável "v_texto" (linha 25), e, logo em seguida, a variável temporária "v_temp" é eliminada da memória com o uso da função "dbms_lob.freetemporary" (linha 28).
Por fim, na linha 31, a função devolve os dados que foram inicialmente recebidos de maneira serializada, como se fosse originalmente um dado do tipo CLOB.
A linha 33 marca o final de todo o bloco correspondente à função.
Visualização dos dados através da view "VIEW_EXEMPLO_CLOB"
A estrutura da view é feita de maneira que de a impressão ao usuário que ela nada mais é que um "espelho" da tabela "exemplo_clob". Assim como a função "get_exemplo_clob_texto", ela também é implementada no lado do usuário "user_externo".
 |
Estrutura da view "view_exemplo_clob". Criada para facilitar o acesso às informações da tabela "exemplo_clob" do usuário "user_emissor".
|
Abaixo segue o detalhamento de sua estrutura:
A primeira linha é onde defíne-se o nome da view.
A linha 2 marca o ponto à partir de onde será criado a instrução da consulta.
Entre as linhas 3 e 5 está a instrução da consulta utilizada para retornar os dados. Na linha 3 para o ínico da consulta através da cláusula "SELECT". Junto da instrução "SELECT" está a coluna "id_exemplo_clob" da tabela "exemplo_clob". A linha 4 faz uso da função "get_exemplo_clob_texto" e informa o "id_exemplo_clob" como parâmetro da função. Isso significa que será buscado o texto correspondente à identificação da linha que está sendo processada. Por fim, na linha 5 está identificada a fonte dos dados, ou seja, a tabela "exemplo_clob". Como esta tabela não pertence ao usuário "user_externo", faz-se necessário o uso do DB link para que as informações sejam acessdas.
A linha 6 marca o final do script de criação da view.
Comparando os resultados
A fim de mostrar a eficácia deste método, duas consultas exatamente iguais são executadas, uma para cada um dos usuários. Exceto que para o usuário "user_emissor" a consulta será em cima da tabela "exemplo_clob", enquanto no usuário "user_externo" a consulta é em cima da view "view_exemplo_clob". Porém isto não afeta o resultado, uma vez que a fonte de dados da view é a tabela "exempl_clob", ou seja, extamente a mesma fonte de dados utilizada para o usuário "user_emissor".
Execução da consulta no usuário "user_emissor":
 |
Consulta no usuário "user_emissor"
|
Execução da consulta no usuário "user_externo":
 |
Consulta no usuário "user_externo".
|
Como uma coluna CLOB pode armazenar um valor muito grande (e que talvez não seja possível visualizar por completo, como é o caso deste exemplo), a função "LENGHT" foi utilizada para realizar a comparação do tamanho do texto retornado em cada usuário. Como podemos observar, o resultado para ambos os usuários foi exetamente igual, "18346" caracteres. Isso mostra que toda a serialização e desserialização destes dados ocorreu como o esperado, uma vez que nenhuma informação foi perdida, ou acressida.
Referências
WATSON, John; RAMKLASS, Roopesh. OCA Oracle Database 11g: Fundamentos I SQL. Rio de Janeiro: Alta Books, 2008. 564 p.
MCLAUGHLIN, Michael. Oracle Database 11g: PL/SQL Programação. Rio de Janeiro: Alta Books, 2009. 830 p.
ORACLE. CREATE DATABASE LINK. 2005. Disponível em: <https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm>. Acesso em: 15 abr. 2017.
ORACLE. HIERARCHICAL QUERIES 2005. Disponível em: <https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm>. Acesso em: 18 mai. 2017.
ORACLE. HIERARCHICAL QUERIES PSEUDOCOLUMNS 2005. Disponível em: <https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#i1009261>. Acesso em: 18 mai. 2017.
ORACLE. DBMS_LOB 2005. Disponível em: <http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm>. Acesso em: 18 mai. 2017.
Postagens
