Oracle database: ORA-01461: can bind a LONG value only for insert into a LONG column

Question

I used Long as my id in Oracle database. I see: 'ORA-01461: can bind a LONG value only for insert into a LONG column', when I try to save by Spring Data 'save' function.

I don't understand what the problem is. I've read that I should use CLOB instead of Long but I can't understand what CLOB is and how to use it. Or maybe something else is the problem. Fragments of my code:

Model:

@Entity
public class Picture {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PICT_SEQ")
  @SequenceGenerator(sequenceName = "picture_seq", allocationSize = 5, name = "PICT_SEQ")
  private Long id;

  private String name;

  private String author;

  @Column(name="CREATED_DATE")
  @Convert(converter = LocalDateAttributeConverter.class)
  private LocalDate createdDate;

  private String content;

  // getXXX, setXXX...
}

Repository:

public interface PictureRepository extends CrudRepository<Picture,Long>{

  public List<Picture> findAll();

  public void delete(Picture pic);

  @SuppressWarnings("unchecked")
  public Picture save(Picture pic);
}

Service layer:

@Service
public class PictureService {

  @Autowired
  private PictureRepository pictureRepo;

  public Picture savePicture(Picture p) {
      return pictureRepo.save(p);
  }

  //...
}

Controller fragment:

@RestController
public class PictureController {

  @Autowired
  private PictureService pictureService;

  @PostMapping(value="/send")
  public ResponseEntity<?> sendPictureToDatabase(@RequestBody Picture picture) throws IOException {

    pictureService.savePicture(picture); //here is the problem
    AjaxResponseBody result = new AjaxResponseBody();
    result.setMsg("success");
    result.setResult(pictureService.getAllPictures());
    return ResponseEntity.ok(result);
  }
}

davidxxx davidxxx · Accepted Answer · 2018-01-30T23:04:55

I used Long as my id in Oracle database.

You have not to.
The Oracle LONG Datatype is really not designed for to represent numeric types :

Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.

Instead, this id :

@Id
private Long id;

should be mapped to a NUMBER type in oracle.

You could specify NUMBER(19) to be able to represent the Java Long range values which the max value contains 19 digits (9223372036854775807).

Oracle database: ORA-01461: can bind a LONG value only for insert into a LONG column

1 Answers