Recentemente, um colega me enviou uma mensagem perguntando como ele poderia listar os arquivos de um diretório específico diretamente dentro do Oracle Database. Ele estava lidando com um cenário onde precisava acessar esses arquivos diretamente do banco de dados, mas não encontrou uma maneira nativa de fazer isso usando apenas PL/SQL. Diante disso, resolvi escrever este post para compartilhar uma solução prática que integra Java com PL/SQL para realizar essa tarefa.

Requisitos para Utilizar a Solução:

1. Permissões de Java no Oracle Database

O usuário que irá criar e executar as funções Java deve ter as permissões apropriadas. Como estamos criando os objetos no schema SYS, já temos as permissões necessárias, mas o usuário que irá executar a função precisará de privilégios de execução.

2. Servidor com Java Virtual Machine (JVM) Ativado:

O Oracle Database deve ter o recurso de Java Virtual Machine (JVM) ativado. A JVM está embutida na maioria das instalações do Oracle, mas em alguns casos, pode ser necessário ativá-la ou configurá-la corretamente.

SET LINESIZE 100
SET PAGESIZE 20
COLUMN COMP_ID FORMAT A10
COLUMN COMP_NAME FORMAT A35
COLUMN STATUS FORMAT A10

SELECT COMP_ID, COMP_NAME, STATUS
FROM DBA_REGISTRY
WHERE COMP_ID = 'JAVAVM';


COMP_ID  | COMP_NAME                      | STATUS
-------- | ------------------------------ | --------
JAVAVM   | JServer JAVA Virtual Machine   | VALID

Se a JVM não estiver instalada ou ativada, você precisará entrar em contato com o administrador do banco de dados para realizar a instalação.

3. Configuração do Ambiente:

Certifique-se de que o diretório do sistema de arquivos ao qual você deseja acessar seja acessível pelo Oracle Database e que o usuário do banco de dados tenha permissões adequadas para leitura dos arquivos nesse diretório.

4. Versão do Oracle Database:

Esta solução está disponível a partir do Oracle Database 9i, que introduziu suporte aprimorado para Java Stored Procedures. Certifique-se de que sua versão do Oracle Database seja 9i ou superior para garantir compatibilidade com a execução de código Java.

Considerações Importantes:

• Segurança: Executar código Java dentro do Oracle Database pode introduzir riscos de segurança se não for gerenciado corretamente. Garanta que apenas usuários autorizados tenham permissão para criar e executar código Java, e que o código seja revisado para evitar vulnerabilidades.
• Performance: A execução de código Java dentro do Oracle Database pode ter impacto na performance dependendo do volume de dados e da complexidade das operações. Teste a solução em um ambiente de desenvolvimento antes de implementá-la em produção.

Passo a Passo para Instalação

1. Criando a Classe Java no Schema SYS

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "ListaArquivos" AS
import java.io.*;
import java.sql.*;

public class ListaArquivos {
    public static String listarArquivos(String diretorio) throws SQLException {
        File pasta = new File(diretorio);
        File[] listaDeArquivos = pasta.listFiles();
        StringBuilder sb = new StringBuilder();

        for (int i = 0; i < listaDeArquivos.length; i++) {
            if (listaDeArquivos[i].isFile()) {
                sb.append(listaDeArquivos[i].getName())
                  .append("|")
                  .append(listaDeArquivos[i].length())
                  .append("\n");
            }
        }

        return sb.toString();
    }
};
/

--##  Java created.

Classe ListaArquivos: A classe Java que criamos é chamada ListaArquivos.
Método listarArquivos: Este método recebe o caminho do diretório (diretorio) como argumento e usa o objeto File para acessar os arquivos dentro desse diretório. Em seguida, ele percorre cada arquivo e armazena os nomes em um StringBuilder, separando cada nome de arquivo com uma nova linha (\n). Finalmente, retorna a string contendo todos os nomes dos arquivos.

2. Criando a Função PL/SQL no Schema SYS

Após criar a classe Java, precisamos de uma função PL/SQL que possa chamar este método Java e utilizar seu resultado. Vamos criar essa função no schema SYS:

CREATE OR REPLACE FUNCTION listar_arquivos_em_diretorio(p_diretorio IN VARCHAR2) 
RETURN VARCHAR2 AS 
   LANGUAGE JAVA 
   NAME 'ListaArquivos.listarArquivos(java.lang.String) return java.lang.String';
/

--## Function created.

Função listar_arquivos_em_diretorio: Esta função PL/SQL chama o método listarArquivos da classe Java ListaArquivos. Ela passa o diretório como argumento e retorna uma VARCHAR2 contendo a lista de arquivos separados por novas linhas.

3. Concedendo Permissões a um Usuário Específico

Agora que a função está criada no schema SYS, precisamos conceder ao usuário específico o privilégio de executá-la. Isso garante que apenas usuários autorizados possam utilizar essa funcionalidade.

GRANT EXECUTE ON SYS.listar_arquivos_em_diretorio TO [target_user];

4. Executando a Função e Exibindo a Lista de Arquivos no PL/SQL

Agora que a função foi criada e as permissões foram concedidas ao usuário, é hora de utilizá-la para listar os arquivos de um diretório específico. O usuário autorizado pode executar a função dentro de um bloco PL/SQL e imprimir o nome de cada arquivo listado.

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
   v_lista_arquivos   VARCHAR2(32767);
   v_linha            VARCHAR2(32767);
   v_posicao          PLS_INTEGER := 1;
   v_proxima_pos      PLS_INTEGER;
   v_nome_arquivo     VARCHAR2(255);
   v_tamanho_arquivo  VARCHAR2(255);
BEGIN
   v_lista_arquivos := SYS.listar_arquivos_em_diretorio('/tmp');

   DBMS_OUTPUT.PUT_LINE(RPAD('Nome do Arquivo', 60) || ' | ' || 'Tamanho (bytes)');
   DBMS_OUTPUT.PUT_LINE(RPAD('-', 60, '-') || ' | ' || RPAD('-', 15, '-'));

   LOOP
      v_proxima_pos := INSTR(v_lista_arquivos, CHR(10), v_posicao);
      EXIT WHEN v_proxima_pos = 0;
      
      v_linha := SUBSTR(v_lista_arquivos, v_posicao, v_proxima_pos - v_posicao);
      
      v_nome_arquivo := SUBSTR(v_linha, 1, INSTR(v_linha, '|') - 1);
      v_tamanho_arquivo := SUBSTR(v_linha, INSTR(v_linha, '|') + 1);

      DBMS_OUTPUT.PUT_LINE(RPAD(v_nome_arquivo, 60) || ' | ' || RPAD(v_tamanho_arquivo, 15));
      
      v_posicao := v_proxima_pos + 1;
   END LOOP;
END;
/

O que este bloco faz:

1. Chamada da Função: A função listar_arquivos_em_diretorio é chamada com o caminho do diretório de interesse (neste exemplo, /tmp). A lista de arquivos resultante é armazenada na variável v_lista_arquivos.
2. Percorrendo e Imprimindo os Arquivos: A lista de arquivos, que é retornada como uma string contendo nomes e tamanhos separados por quebras de linha (\n), é percorrida em um loop. Durante o loop, cada nome de arquivo e seu respectivo tamanho são extraídos e impressos usando DBMS_OUTPUT.PUT_LINE.
3. Exibição da Saída: Com o DBMS_OUTPUT ativado, os nomes e tamanhos dos arquivos são exibidos de forma organizada na saída do SQL*Plus ou SQL Developer, facilitando a visualização e análise dos resultados.

Exemplo de Saída:

Essa abordagem mostra como podemos combinar Java com PL/SQL no Oracle para realizar tarefas que, normalmente, não seriam possíveis apenas com PL/SQL. Essa solução é especialmente útil para listar arquivos em diretórios específicos dentro do ambiente Oracle, oferecendo mais flexibilidade nas operações do banco de dados.

Depois de seguir os passos e atender aos requisitos, você poderá listar arquivos nos diretórios do Oracle Database de forma eficiente e organizada, expandindo as possibilidades do seu ambiente de trabalho.

Ao integrar Java com PL/SQL e acessar o sistema de arquivos, é importante garantir que apenas usuários autorizados tenham acesso a essas funções. Revisar o código para evitar vulnerabilidades também é essencial. A segurança deve sempre ser uma prioridade, especialmente quando estamos lidando com dados sensíveis e acessando o sistema operacional através do banco de dados Oracle.

🚜🚓 #20240901 #DBASobrinho #GuinaNãoTinhaDó #BóBó #LogoTemFérias #PaulistaUmaZona #ComidaDaMuié #AnnaTaGrude #GenteDoida 🚓🚜