Consumindo classes JAVA dentro de um Banco de Dados Oracle

Por Fabio Prado
Publicado en maio 2013

No artigo apresentaremos um recurso muito interessante e pouco conhecido no Oracle Database, que permite consumir classes desenvolvidas em JAVA dentro de um Banco de Dados (BD) Oracle, para processar ou interagir com dados dentro do próprio BD.

As principais vantagens e desvantagens em consumir classes Java dentro de um BD são:

VANTAGENS:

- Possibilidade de reutilizar programas testados e estáveis, desenvolvidos em classes Java, ao invés de desenvolver algo novo em PL/SQL;

- Ganhar tempo utilizando um programa que já está pronto ao invés de desenvolver um novo;

- Possibilidade de expandir recursos de programação, pois Java possui muito mais recursos que PL/SQL para interagir, por exemplo, com o SO e programas externos.

DESVANTAGENS:

- A performance é muito menor em relação a um programa PL/SQL puro;

- Aumenta-se a dificuldade de gerenciamento do BD, pois neste caso o DBA ou responsável pelo BD precisará gerenciar objetos externos, que normalmente ele não está acostumado a lidar, e isso poderá provocar problemas quando houver migração de BD para outras máquinas ou ambientes em que a classe Java não foi instalada;

- Será necessário gerenciar uma área de memória adicional do BD, chamada Java Pool.

Para demonstrar este recurso, utilizaremos como exemplo o bytecode (Idade.class) de uma classe desenvolvida em Java chamada Idade, que possui uma função para calcular idade, chamada getIdade, que possui as seguintes características:

- Aceita como entrada um valor alfanumérico correspondente à data de nascimento de uma pessoa;

- Retorna um valor numérico correspondente à idade atual da pessoa.

-----------------------------------------------------------------------------------------
ROTEIRO PASSO-A-PASSO P/ CRIAR E TESTAR A FUNÇÃO JAVA
-----------------------------------------------------------------------------------------

Pré-requisitos:

a) No host do servidor de BD, configure a variável ORACLE_SID com o nome da instância de BD em que você irá carregar e testar a classe Java para executar o Passo 1.

Passo 1- Carregando a classe Java no BD:

Execute o utilitário loadjava, localizado na pasta $ORACLE_HOME/bin, informando nome de usuário e senha de BD de um schema onde a classe será carregada + nome do arquivo bytecode da classe Java, como no exemplo abaixo:

loadjava.bat -user user/password Idade.class

Obs.: Para seguir o exemplo deste arquivo, baixe o arquivo Idade.class a partir do link https://skydrive.live.com/redir?resid=A2D6B428543B3787!444

Passo 2- Verificando se a classe foi carregada com sucesso:

Conectando-se no SQL Plus, SQL Developer ou qualquer outra ferramenta similar, execute a query abaixo para verificar se o objeto foi criado com sucesso:

SELECT 	OBJECT_NAME, OBJECT_TYPE, CREATED
from	ALL_OBJECTS
where	upper(OBJECT_NAME) = upper('Idade');

Se a query acima retornar uma linha, significa que o objeto foi criado com sucesso. Se a query não retornar linha(s), repita o passo anterior e/ou identifique o que pode ter falhado no procedimento de execução do utilitário loadjava.

Passo 3- Criando uma função PL/SQL p/ consumir a função da classe Java:

Conectando-se no SQL Plus, SQL Developer ou qualquer outra ferramenta similar, execute o código abaixo para criar uma função PL/SQL que irá consumir a função Java para retornar uma Idade, que será calculada após fornecermos uma string (parâmetro de entrada) correspondente a uma data qualquer:

create or replace FUNCTION RetornarIdade_Java(st IN VARCHAR2) 
RETURN NUMBER AS 
LANGUAGE JAVA NAME 'Idade.getIdade(java.lang.String) returnint';

Passo 4- Executando a função:

Para executar a função criada no passo anterior, iremos utilizar dados da famosa tabela HR.EMPLOYEES como parâmetro de entrada, para retornar o nome e tempo de trabalho (ao invés de idade) de cada empregado:

 SELECT 	E. FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
		RETORNARIDADE_JAVA(TO_CHAR(HIRE_DATE,'DD/MM/YYYY')) as worktime
FROM	hr.employees e;

Obs.: Aofinal do passo 4, se tudo correu bem, pudemos verificar que a gente conseguiu consumir uma função da classe Java dentro do BD. Iniciaremos agora um teste de performance para comparar o desempenho de um cálculo de idade da função Java e um cálculo de idade de uma função nova, desenvolvida somente com código PL/SQL.

-----------------------------------------------------------------------------------------
ROTEIRO PARA EFETUAR OS TESTES DE PERFORMANCE
ENTREFUNÇÃO JAVA x FUNÇÃO PL/SQL
-----------------------------------------------------------------------------------------

Passo 1- Criando uma função PL/SQL p/ calcular a idade (sem código Java):

Conectando-se no SQL Plus, SQL Developer ou qualquer outra ferramenta similar, execute o código abaixo para criar uma função PL/SQL (sem consumir função de classe Java) que irá retornar uma Idade, calculada após fornecermos uma string (parâmetro de entrada) correspondente a uma data qualquer:

CREATE OR REPLACE FUNCTION RetornarIdade_PLSQL(ST IN VARCHAR2) RETURN NUMBER AS 
   STR_DATA DATE;
   STR_ANO NUMBER;
   STR_MES NUMBER;
   STR_DIA NUMBER;  
   SYS_DATA DATE;
   SYS_ANO NUMBER;
   SYS_MES NUMBER;
   SYS_DIA NUMBER;

IDADE NUMBER;
   DIFERENCA_MES NUMBER;
DIFERENCA_DIA NUMBER;   
BEGIN
   STR_DATA := TO_DATE(st, 'DD/MM/YYYY');
   STR_ANO := TO_NUMBER(TO_CHAR(STR_DATA,'YYYY'));
   STR_MES := TO_NUMBER(TO_CHAR(STR_DATA,'MM'));
   STR_DIA := TO_NUMBER(TO_CHAR(STR_DATA,'DD'));   
   SYS_DATA := SYSDATE;
   SYS_ANO := TO_NUMBER(TO_CHAR(SYS_DATA,'YYYY'));
   SYS_MES := TO_NUMBER(TO_CHAR(SYS_DATA,'MM'));
   SYS_DIA := TO_NUMBER(TO_CHAR(SYS_DATA,'DD'));   
DIFERENCA_MES := SYS_MES - STR_MES;
DIFERENCA_DIA := SYS_DIA - STR_DIA;
IDADE := SYS_ANO - STR_ANO;

   IF (DIFERENCA_MES < 0 OR (DIFERENCA_MES = 0 AND DIFERENCA_DIA < 0)) THEN
IDADE := IDADE - 1;
   END IF;
  RETURN IDADE;
END;

Passo 2- Testando as funções JAVA x PL/SQL:

Conectando-se novamente no SQL Plus, SQL Developer ou qualquer outra ferramenta similar, execute o código abaixo para testarmos a performance das funções Java (RetornarIdade_Java) e PL/SQL (RetornarIdade_PLSQL), já criadas ao longo do artigo:

SET SERVEROUTPUT ON
DECLARE
  IDADE       NUMBER;
  L_START     NUMBER;
BEGIN
  L_START := DBMS_UTILITY.GET_TIME;
  FOR Y IN (SELECT HIRE_DATE FROM HR.EMPLOYEES) LOOP
    SELECT RETORNARIDADE_Java(TO_CHAR(Y.HIRE_DATE ,'DD/MM/YYYY')) INTO IDADE FROM DUAL;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Tempo paraexecução da função JAVA: ' || 
ROUND((DBMS_UTILITY.GET_TIME - L_START)/100,2) || 's');
  L_START := DBMS_UTILITY.GET_TIME;
  FOR X IN (SELECT HIRE_DATE FROM HR.EMPLOYEES) LOOP
    SELECT RETORNARIDADE_plsql(TO_CHAR(X.HIRE_DATE ,'DD/MM/YYYY')) INTO IDADE FROM DUAL;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Tempo paraexecução da função PL/SQL: ' 
|| ROUND((DBMS_UTILITY.GET_TIME - L_START)/100,2) || 's');
END;

Resultado:

Tempo de execução da função JAVA: 0,09s
Tempo de execução da função PL/SQL:0,01s

CONCLUSÃO:

Consumir classes Java dentro do BD para executar tarefas complexas na interação com os dados pode ser uma boa alternativa para não ter que desenvolver algo novo em PL/SQL ou até mesmo para conseguir resolver um problema que seria impossível de ser resolvido somente com código PL/SQL. Porém, devemos ter muito cuidado ao utilizar este recurso. Além do código em Java consumido dentro do BD ser muito lento em relação ao código PL/SQL puro, o gerenciamento das classes Java (externas ao BD) pode se tornar uma tarefa difícil para os DBAs.


Postado por Fabio Prado: DBA, Professor e Autor do blog fabioprado.net