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

Question

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.

Solution 1

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).

Solution 2

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.

Comments

[learner]

Thanks for reply. i did convert student_id to BigInteger. but how to map it to preparedStatement argument. since there is no pstmt.setBigInteger method...

[learner]

Used this but same error BigInteger bi = BigInteger.valueOf(studentId.longValue()); String query = "Update TEST SET PHOTO = ? , MIME_TYPE = ? where STUDENT_ID=" + bi + "";

[Mark Rotteveel]

Try to set it using setObject() instead

[learner]

cant changed the column from NUMBER. (its some compulsion which i can tell you but then i ll ve to *ill you ;) ).

[learner]

sir can you put in some code how to use setObject() (i m a beginner here)

[ScrappyDev]

The INTEGER oracle datatype is equivalent to NUMBER(28)

[Lukas Eder]

Oracle's LONG type is a character data type. The problem here is related to the first bind value, not the third...

[Ajay Sharma]

pstmt.setBinaryStream(int, InputStream, int) throws the same exception when data is more than 4000 bytes

[svaor]

@AjaySharma, sscce proofs plz. I can not reproduce it in my tests.