java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

4
votes

I am using a simple interface (in jsf 1.2 and rich faces 3.3.2, Oracle 11g R1) to let user select picture with rich:fileUpload and save in a table. As a test, i created following table.

CREATE TABLE TEST
(
 MIME_TYPE VARCHAR2 (1000),
 PHOTO BLOB,
 STUDENT_ID NUMBER NOT NULL
)

code snippet to save the picture to BLOB field is as follows.

//......From the uploadFile Listener
public void listener(UploadEvent event) throws Exception {
...      
item = event.getUploadItem();
...
StudentPhotoDAO dao = new StudentPhotoDAO();
dao.storePhoto(item.getData(),item.getContentType(),studentId);
...
}


//......From the PhotoDAO ..........................


public void storePhoto(byte data[],String mimeType, Long studentId){
{
 ...
  ByteArrayInputStream bis=new ByteArrayInputStream(data);
  String query = "update  TEST set PHOTO = ? ,MIME_TYPE = ?  where STUDENT_ID=?";
  pstmt = conn.prepareStatement(query);
  pstmt.setAsciiStream(1,(InputStream)bis,data.length);
  pstmt.setString(2,mimeType.toString());
  pstmt.setLong(3,studentId);
  pstmt.executeUpdate();
 }

I get following error:

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

Where is the error in the code please.

Thanks.

5
javajdbcoracle11gblob
It appears the SQL type NUMBER is not compatible with the Java type Long. – Thorbjørn Ravn Andersen
In Oracle, LONG is a textual type, not a numeric type. Most of these answers are looking in the wrong places. – HardlyKnowEm

5 Answers

1
votes

You specify the student_id as number, which seems to map to BigInteger. See e.g. this table.

Either you supply a BigInteger or you need to change the type of student_id.

1
votes

Look at Oracle LONG type description: "LONG is an Oracle data type for storing character data ...". So LONG is not number in Oracle. It's a text.

I think you got this error because of this: pstmt.setAsciiStream(1,(InputStream)bis,data.length);

Try use pstmt.setBinaryStream(int, InputStream, int) or pstmt.setBinaryStream(int, InputStream, long).

0
votes

You are calling

pstmt.setLong(3,studentId);

and you specified column as

STUDENT_ID NUMBER NOT NULL

and how docs says:

An attempt was made to insert a value from a LONG datatype into another datatype. This is not allowed.

So just make it like this:

STUDENT_ID INTEGER NOT NULL
pstmt.setInt(3, studentId);
0
votes

When binding large binary streams to a BLOB column in Oracle, use PreparedStatement.setBlob() rather than setAsciiStream() or setBinaryStream().

0
votes

I guess using ASCII stream is to blame.

pstmt.setAsciiStream(1, (InputStream) bis, data.length);

Try

pstmt.setBinaryStream(1, new ByteArrayInputStream(data));

(It looks like Oracle interprets ASCII stream with length parameters as LONG which cannot be more than 4000 bytes. But that's only an unconfirmed guess.)