Return file as a blob from Oracle DB with java stored in DB-CodePudding

I want to read/write Blobs to the filesystem from inside the database without oracle directories, because I have a lot of directories and this makes it complicated.

I have java read/write rights on the main directory where we store the files. So security is no problem.

So I made a java function to read the file and encode it as a base64 string:

public static String getFile(String directory, String filename)
      {
        try {
            File file = new File(directory   "/"   filename);
            byte[] fileContent = Files.readAllBytes(file.toPath());
            return Base64.getEncoder().encodeToString(fileContent);
        } catch (IOException e) {
            throw new IllegalStateException("could not read file", e);
        }
      }

This works fine with the following pl/sql function:

FUNCTION get_file (p_dir in varchar2, p_file in varchar2) RETURN VARCHAR2
    AS LANGUAGE JAVA
    NAME 'FilesFromUnix.getFile (java.lang.String, java.lang.String) return java.lang.String';

But what I want to do is not return it as a VARCHAR2 and a String but use it as a Blob instead, but when I try to do this like with this code snippet:

public static byte [] getFile2(String directory, String filename)
      {
        try {
            File file = new File(directory   "/"   filename);
            return Files.readAllBytes(file.toPath());
        } catch (IOException e) {
            throw new IllegalStateException("could not read file", e);
        }
      }

I have a problem when trying to call it in a pl/sql function:

FUNCTION get_file2 (p_dir in varchar2, p_file in varchar2) RETURN BLOB
AS LANGUAGE JAVA
NAME 'FilesFromUnix.getFile (java.lang.String, java.lang.String) return java.lang.Byte';

a return value that is convertible to an Oracle BLOB expected, a Java java.lang.String received

I also tried with the following snippet:

public static Blob getFile2(String directory, String filename)
  throws SerialException, SQLException
      {
        try {
            File file = new File(directory   "/"   filename);
            return new SerialBlob(Files.readAllBytes(file.toPath()));
        } catch (IOException e) {
            throw new IllegalStateException("could not read file", e);
        } catch (SerialException s) {
            throw new IllegalStateException("could not read file", s);
        } catch (SQLException f) {
            throw new IllegalStateException("could not read file", f);
        }
      }

But couldn't get it also returned in PL/SQL as a BLOB.

Do one of you know a way how to return the file as a BLOB in the pl/sql part ?

My files are binary files.

And they could be large also, so I don't wan't restrictions on size.

CodePudding user response：

I have found the solution with java.sql.Blob, I probably made a mistake with my first try and mixed the types:

public static Blob getFile(String directory, String filename)
  throws SQLException, IOException
      {
            File file = new File(directory   "/"   filename);
            
            Connection conn = new OracleDriver().defaultConnection();
            Blob desBlob = conn.createBlob();
            desBlob.setBytes(1, Files.readAllBytes(file.toPath()));
            return desBlob;
      }

And the pl/sql procedure

FUNCTION get_file (p_dir in varchar2, p_file in varchar2) RETURN BLOB
AS LANGUAGE JAVA
NAME 'FilesFromUnix.getFile (java.lang.String, java.lang.String) return java.sql.Blob';

This works without deprecation.