Functions Table no Oracle e Java

Aproveitando a necessidade de implementar conexão Java com o Oracle nos últimos dias, mostrarei aqui também como executar uma seleção de dados utilizando o recurso Function Table do Oracle.

Utilizarei na demonstração uma função que retornará uma lista de nomes de sistemas operacionais. Para tal, será necessário criar um tipo no Oracle que especifique o retorno de uma tabela de dados. Utilizarei uma tabela de CLOBs, como segue:

CREATE OR REPLACE TYPE "TYP_TB_CLOB" IS TABLE OF CLOB;

Posteriormente, criarei uma package para acomodar funções e procedimentos. Não é uma necessidade formal, porém, me auxilia no controle do código:

CREATE OR REPLACE PACKAGE "KAPP_PACKAGE" AS
    FUNCTION FGET_OPERATIONAL_SYSTEMS(P_NAME VARCHAR2) RETURN TYP_TB_CLOB;
END KAPP_PACKAGE;

Passo então a criação do corpo da package no qual conterá apenas uma função que receberá uma cadeia de caracteres qualquer e retornará uma lista de nomes dos sistemas operacionais cadastrados:

CREATE OR REPLACE PACKAGE BODY "KAPP_PACKAGE" AS
 
FUNCTION FGET_OPERATIONAL_SYSTEMS(P_NAME VARCHAR2) RETURN TYP_TB_CLOB
IS
    V_TB_CLOB TYP_TB_CLOB;
BEGIN
    V_TB_CLOB := TYP_TB_CLOB();
 
    V_TB_CLOB.EXTEND();
    V_TB_CLOB(V_TB_CLOB.COUNT) := 'Linux';
 
    V_TB_CLOB.EXTEND();
    V_TB_CLOB(V_TB_CLOB.COUNT) := 'Windows';
 
    RETURN V_TB_CLOB;
END FGET_OPERATIONAL_SYSTEMS;
 
END KAPP_PACKAGE;

Uma vez compilado tipo e package, podemos testar a implementação dos recursos no Oracle através da seguinte query:

SELECT * FROM TABLE(KAPP_PACKAGE.FGET_OPERATIONAL_SYSTEMS('MacOS'));

que tem que resultar em:

COLUMN_VALUE
--------------------------------------------------------------------------------
Linux
Windows

Passando então para a codificação Java faremos a conexão no banco de dados Oracle e formação da query que solicite a Function Table criada da seguinte forma:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
 
public class ImportXML
{
   public void Execute()
   {
      String serviceURL = "jdbc:oracle:thin:@<server>:1521:orcl";
      String username = "<username>";
      String password = "<password>";
      StringBuffer query = new StringBuffer();
 
      query.append("select * from table(<SCHEMA>.KAPP_PACKAGE.FGET_OPERATIONAL_SYSTEMS(?))");
 
      Properties properties = new Properties();
      properties.setProperty("InactivityTimeout", "0");
 
      try 
      {
         Class.forName("oracle.jdbc.OracleDriver");
         OracleDataSource ods = new OracleDataSource();
         ods.setURL(serviceURL);
         ods.setUser(username);
         ods.setPassword(password);
         ods.setConnectionCacheProperties(properties);
 
         Connection conn = ods.getConnection();
         CallableStatement call = conn.prepareCall(query.toString());
         call.setQueryTimeout(0);
         call.setObject(1, new String("MacOS"));
 
         ResultSet rs = call.executeQuery();
         while (rs.next()) 
         {
            String objectName = rs.getString(1);
            System.out.print(objectName);
         }
 
         rs.close();
         conn.close();
      } 
      catch (Exception e) 
      {
         e.printStackTrace();
      }
   }
 
   public static void main(String[] args) throws SQLException, Exception 
   {
      ImportXML oImportXML = new ImportXML();
      oImportXML.Execute();
   }
}

Com esse código será possível executar a function table corretamente. Em artigo próximo abordarei o uso de function table de objetos.