DBMS_LOB: Operações com LOBs (Large Objects)
Os LOBs (Large Objects) são usados para armazenar grandes quantidades de dados, como imagens, áudios, vídeos e documentos de texto grande. O Oracle fornece o pacote DBMS_LOB para manipular LOBs. Vamos explorar algumas das operações comuns com LOBs.
Iniciando com LOBs
Existem basicamente dois tipos de LOBs: BLOB (para dados binários) e CLOB (para caracteres). Para armazenar LOBs, primeiro definimos colunas do tipo LOB:
CREATE TABLE my_table (
id NUMBER,
my_clob CLOB,
my_blob BLOB
);
Inserindo dados em LOBs
Você pode inserir dados em LOBs como em outros tipos de colunas:
DECLARE
l_clob CLOB := 'Este é um exemplo de CLOB';
l_blob BLOB;
BEGIN
-- Para BLOBs, vamos considerar que l_blob já contém algum dado binário.
INSERT INTO my_table (id, my_clob, my_blob) VALUES (1, l_clob, l_blob);
END;
/
Lendo dados de LOBs
Para ler dados de LOBs, você pode simplesmente selecionar a coluna. No entanto, para propósitos de demonstração, podemos usar DBMS_LOB.SUBSTR para obter uma substring do CLOB:
SELECT id, DBMS_LOB.SUBSTR(my_clob, 20, 1) as sample_text FROM my_table WHERE id = 1;
Atualizando LOBs
Para atualizar um LOB, você pode usar a função DBMS_LOB.WRITE:
DECLARE
l_clob CLOB;
BEGIN
SELECT my_clob INTO l_clob FROM my_table WHERE id = 1 FOR UPDATE;
DBMS_LOB.WRITE(l_clob, 11, 1, ' novo texto');
END;
/
Operações com DBMS_LOB
- Append: Anexa um LOB ao final de outro.
DECLARE
l_clob CLOB;
BEGIN
SELECT my_clob INTO l_clob FROM my_table WHERE id = 1 FOR UPDATE;
DBMS_LOB.APPEND(l_clob, ' Anexado');
END;
/- Length: Retorna o tamanho do LOB.
DECLARE
l_length NUMBER;
l_clob CLOB;
BEGIN
SELECT my_clob INTO l_clob FROM my_table WHERE id = 1;
l_length := DBMS_LOB.GETLENGTH(l_clob);
DBMS_OUTPUT.PUT_LINE('Tamanho do CLOB: ' || l_length);
END;
/- Etc: Existem várias outras funções e procedimentos como
ERASE,INSTR,TRIM, entre outros.
Exemplos práticos
Vamos criar um exemplo prático mais elaborado, onde uma empresa fictícia deseja armazenar contratos em PDF (formato binário) e sua descrição textual.
Pré-requisito
Tenha certeza de que o diretório contendo os PDFs que você deseja carregar esteja acessível pelo Oracle e que um diretório de banco de dados tenha sido definido para ele.
Por exemplo, se você tiver um diretório /meus_pdfs, você precisaria executar:
CREATE DIRECTORY pdf_dir AS '/meus_pdfs';Lembre-se de conceder as permissões adequadas:
GRANT READ, WRITE ON DIRECTORY pdf_dir TO seu_usuário;Criação da Tabela
Primeiro, criamos uma tabela para armazenar os contratos:
CREATE TABLE contratos (
contrato_id NUMBER PRIMARY KEY,
descricao CLOB,
arquivo BLOB
);Inserindo dados – Carregando um arquivo PDF em um BLOB
DECLARE
l_bfile BFILE;
l_blob BLOB;
BEGIN
-- Inicializando o BLOB
INSERT INTO contratos (contrato_id, descricao, arquivo)
VALUES (1, 'Descrição do contrato', EMPTY_BLOB())
RETURNING arquivo INTO l_blob;
-- Abrindo o arquivo do sistema de arquivos
l_bfile := BFILENAME('PDF_DIR', 'nome_do_arquivo.pdf');
-- Abre os LOBs
DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(l_blob, DBMS_LOB.LOB_READWRITE);
-- Carregando o BFILE no BLOB
DBMS_LOB.LOADFROMFILE(l_blob, l_bfile, DBMS_LOB.GETLENGTH(l_bfile));
-- Fechando os LOBs
DBMS_LOB.CLOSE(l_bfile);
DBMS_LOB.CLOSE(l_blob);
-- Atualizando a tabela
UPDATE contratos
SET arquivo = l_blob
WHERE contrato_id = 1;
COMMIT;
END;
/
1 linha inserida.
Explicação
- BFILE: É um tipo de dado que representa um LOB armazenado fora do banco de dados, geralmente no sistema de arquivos.
- BFILENAME: Esta função retorna um locator para um BFILE. O primeiro argumento é o nome do diretório de banco de dados, e o segundo é o nome do arquivo.
- DBMS_LOB.LOADFROMFILE: Esta função carrega conteúdo de um BFILE para um BLOB (ou CLOB/NCLOB).
Após executar o procedimento acima, o arquivo PDF nome_do_arquivo.pdf será carregado no BLOB na tabela contratos para o contrato_id especificado.
Lembre-se: Sempre garanta que os tamanhos dos arquivos e os tamanhos permitidos para os BLOBs sejam compatíveis e que você tenha espaço suficiente no tablespace. Além disso, dependendo do tamanho do arquivo, você pode considerar opções adicionais para otimizar o desempenho.
Atualizando a Descrição do Contrato
DECLARE
l_nova_descricao CLOB := ' (Atualizado em 20/08/2023)';
BEGIN
UPDATE contratos
SET descricao = descricao || l_nova_descricao
WHERE contrato_id = 1;
END;
/
-- Verificando a atualização:
SELECT descricao FROM contratos WHERE contrato_id = 1;
Contrato de venda referente ao imóvel X. (Atualizado em 20/08/2023)
Consultando o Tamanho do Arquivo do Contrato
DECLARE
l_tamanho NUMBER;
BEGIN
SELECT DBMS_LOB.GETLENGTH(arquivo)
INTO l_tamanho
FROM contratos WHERE contrato_id = 1;
DBMS_OUTPUT.PUT_LINE('Tamanho do arquivo: ' || l_tamanho || ' bytes');
END;
/
Tamanho do arquivo: 1024 bytes <o resultado será de acordo com o PDF que você utilizou>
Extraindo Parte da Descrição
Suponha que desejamos extrair os primeiros 20 caracteres da descrição para um resumo:
SELECT contrato_id, DBMS_LOB.SUBSTR(descricao, 20, 1) AS resumo
FROM contratos WHERE contrato_id = 1;
Deletando um Contrato
DELETE FROM contratos WHERE contrato_id = 1;
1 linha deletada.
Conclusão
O pacote DBMS_LOB fornece uma variedade de funções úteis para manipular dados LOB. Neste exemplo, demonstramos algumas operações básicas para trabalhar com CLOBs e BLOBs em um cenário prático. Em uma aplicação real, a interação com BLOBs envolveria também a leitura e escrita de arquivos binários do sistema de arquivos. Porém, para fins de simplicidade, o exemplo acima é baseado apenas em texto.
Lembre-se, para operações complexas com LOBs, é crucial compreender as nuances de desempenho, especialmente ao lidar com objetos muito grandes.
Abs
Referências
- Oracle® Database PL/SQL Packages and Types Reference. Disponível em: https://docs.oracle.com/en/database/
- Oracle® Database SQL Language Reference. Disponível em: https://docs.oracle.com/en/database/