Run Java app in Oracle 12c

[Krzysiek Keisyzrk]

Hi everyone, i try to write java class and deploy and run it in oracle database.
This is my method, nothing special, try to connect, simple select.

    public static int internatJDBC(int id) throws SQLException 
    {
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        String sqlSt = "select * from TESTTABLE where id="+id;
        
        Statement stmt = null;
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sqlSt);
        int pobraneID = -1;
        if (rs.next())
        {
             pobraneID = rs.getInt("ID");
        }
        return pobraneID;
    }

To upload class to Oracle i used Toad.
When upload finished, i run this sql command on database.

create or replace function internal( id_wart number) return number as language java name 'ParsowanieXSLX.internatJDBC(int) return int';

Everything is ok but, when i tried to used this function a get this error:

ORA-29541: klasa C##KRZYSIEK.ParsowanieXSLX nie mogła być rozstrzygnięta ( Class C##..... could not be resolver ) - i used polish version and this is translate.
29541. 00000 - "class %s.%s could not be resolved"
*Cause: An attempt was made to execute a method in a Java class
that had not been previously and cannot now be compiled
or resolved successfully.
*Action: Adjust the call or make the class resolvable.


Anyone knows something what could i do?. And i have second question. How can i add a java library to Oracle like ApachPOI.

[Zachary Pry]

Hello Krzysiek,

I would like to clarify few points:
1. Can you share full file - I would like to see class name.
2. Why you don't close ResultSet ?

Regarding creation of the function itself - you might want to use:
Using upper case improves readability plus 'internal' might be reserved word (I don't think this is the case for Oracle though) and it doesn't quite explain what function does.

CREATE OR REPLACE FUNCTION FIND_BY_ID(id_wart NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME 'ParsowanieXSLX.internatJDBC(int) return int';  

And i have second question. How can i add a java library to Oracle like ApachPOI.

I believe it can be done via:
loadjava –u <username> some_library.jar

[Krzysiek Keisyzrk]

Why i don't close RS? - it's my mistake, i resolve problem with this method it's work fine, but my main problem is how to add library to oracle ?, my second method ( test metod) use ApachPoi library - how can i add it to oracle ??.

This is the code

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import oracle.sql.BFILE;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ParsowanieXSLX
{
    public static String skopiujPlikXlsx(int id) throws SQLException, IOException
    {
            Connection connection = null;
            connection = DriverManager.getConnection("jdbc:default:connection:");
            String query = "select * from TESTTABLE where id=";
            query = query+id;
            Statement stmt = null;
            stmt = connection.createStatement();
                        ResultSet rs = stmt.executeQuery(query);
                        BFILE file = null;
                        if(rs.next())
                        {
                            Object fileObject = rs.getObject("PLIK");
                            file = (BFILE) fileObject;
                           // System.out.println(file.getName());
                        }
            file.openFile();
            XSSFWorkbook workbook = new XSSFWorkbook(file.binaryStreamValue());
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            XSSFWorkbook workbookOut = new XSSFWorkbook();
            XSSFSheet sheetOut = workbookOut.createSheet("Imiona");

            int columnNumber = -1;
            boolean stop = true;
            String imie = "";
            int rownum = 0;
            
            while (rowIterator.hasNext()) {
                XSSFRow row = (XSSFRow) rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    XSSFCell cell = (XSSFCell) cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        if (cell.getStringCellValue().equals("Imie")) {
                            columnNumber = cell.getColumnIndex();
                            break;
                        }
                    }
                    if (cell.getColumnIndex() == columnNumber) {
                        imie = cell.getStringCellValue();
                         System.out.println(imie);
                         return imie;
                    }
                } 
            }
        return "Exit";
    }


    public static int internatJDBC(int id) throws SQLException 
    {
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        String sqlSt = "select * from TESTTABLE where id="+id;
        
        Statement stmt = null;
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sqlSt);
        int pobraneID = -1;
        if (rs.next())
        {
             pobraneID = rs.getInt("ID");
        }
        rs.close();
        return pobraneID;
    }
}

I tried, build it to jar with all dependency load this file to oracle - in sql developer i see this library but it doesn't work. Also i tried to load library one by one but the effect is the same.