Inserting byte[] array as blob in Oracle Database getting ORA-01460: unimplemented or unreasonable conversion requested

Question

I have a java stored procedure that I am trying to insert a byte[] array into an oracle blob field in a table.

I create a prepared statement as follows but it will randomly fail when I execute the prepared statement. I have narrowed down that the issue is coming from the pstmt.setBytes(4,content). The error I get is:

ORA-01460: unimplemented or unreasonable conversion requested.

private static void insertFile(Connection connOracle, int zipFileId, byte[] data, String filepath, String filename ) throws SQLException {

try {
    String QUERY = "INSERT INTO files(file_id, zip_file_id, filename, file_path, content) VALUES(SEQ_FILE_ID.nextval,?,?,?,?)";

    PreparedStatement pstmt = connOracle.prepareStatement(QUERY);

    pstmt.setInt(1,zipFileId);
    pstmt.setString(2, filename);
    pstmt.setString(3, filepath);
    pstmt.setBytes(4, data);

    System.out.println("INSERTING file_id " + filepath + ", " + filename + " INTO DATABASE");

    pstmt.execute();
    pstmt.close();
}
catch (SQLException e) {
    throw new SQLException(e.getMessage());  
}

no, if I comment out the 4th param then everything works great. — medium
– medium, Commented Oct 17, 2011 at 13:30
Deleted my answer now that the question has been corrected... is data non-null? — Jon Skeet
– Jon Skeet, Commented Oct 17, 2011 at 13:31
I meant: Does it happen when data is null or an empty string? — Klas Lindbäck
– Klas Lindbäck, Commented Oct 17, 2011 at 13:32
no it will not happen if data is null, it only happens when there is content in the byte[] array — medium
– medium, Commented Oct 17, 2011 at 13:54

Mohit · Accepted Answer · 2022-08-02 08:53:35Z

5

If I recall correctly the Oracle JDBC drivers (at least in the older ones - you didn't tell us which version you are using) don't support setBytes() (or getBytes()).

In my experience, using setBinaryStream() is much more reliable and stable:

InputStream in = new ByteArrayInputStream(data);
pstmt.setBinaryStream(4, in, data.length);

edited Aug 2, 2022 at 8:53

Mohit

1,2152 gold badges12 silver badges25 bronze badges

answered Aug 26, 2013 at 10:08

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Goodwine Over a year ago

what would be different on this from using Blob?

user330315 Over a year ago

@Goodwine: because in my experience getBlob() and setBlob() are either not implemented or don't work properly.

M.J. · Accepted Answer · 2011-10-17 17:45:14Z

2

try with the below code, this should work for you :-

Blob blobValue = new SerialBlob(data);
pstmt.setBlob(4, blobValue);

answered Oct 17, 2011 at 17:45

M.J.

16.7k28 gold badges79 silver badges98 bronze badges

Collectives™ on Stack Overflow

Inserting byte[] array as blob in Oracle Database getting ORA-01460: unimplemented or unreasonable conversion requested

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related