7

I have one instance of Oracle Database 10.2.0.4. I have java-application based on spring framework 3.0.5, hibernate 3.5.6. I have a table with BLOB column (blobContent BLOB) and an entity with java.sql.Blob field (mapped in hibernate XML-mapping as <property name="blobContent" type="blob"/>). I use Oracle10gDialect.

I get ORA-01461 periodically trying to insert row in this table. I tried several Oracle JDBC drivers (10.2.0.4, 10.2.0.5 and 11.2.0.3) with no any success. My googling and searching metalink database for this problem failed. I decompiled Oracle JDBC-driver and checked code starting at method oracle.jdbc.driver.OraclePreparedStatement.setBinaryStream(int, InputStream, long) thru oracle.jdbc.driver.DBConversion till oracle.jdbc.driver.OracleConversionInputStream.

Everything looks OK as far as this code can be considered OK :)

Please advise how to trace this problem... may be at Oracle side (?)... may be looking for some implicit casting... May be I missed some known Oracle metalink article regarding this problem?

svaor
  • 2,205
  • 2
  • 19
  • 41
  • Are you trying to insert a LOB through an `INSERT` statement? – Rachcha Mar 14 '13 at 17:52
  • Is there a reason for not using a byte array for mapping the blob content? – Sami Korhonen Mar 14 '13 at 21:43
  • @Rachcha Yes, I'm trying to insert BLOB. But some occasional insert statements fails with ORA-01461. It looks like Oracle or Oracle JDBC driver decides to process parameter value as Oracle LONG type at some moments. I can not grasp why and in which cases it does this conversion. Due to some circumstances it's hard for me to reproduce this problem in test environment. That is why I asked my question to experts here. – svaor Mar 15 '13 at 04:22
  • 1
    @SamiKorhonen, yes, of course, there is an important reason: blob content size can be small, big or even huge. New jdbc API add method setBinaryStream(int, InputStream, long) to support content size more than 2Gb. Parsing this method code I discover that there are three branch of processing streams: with size more than 1g, more than some physical connection parameter and other cases for small blobs when byte array is used. The memory consumption is very critical and important. – svaor Mar 15 '13 at 04:36
  • Maybe this answer can help you: http://stackoverflow.com/questions/9156019/ora-01461-can-bind-a-long-value-only-for-insert-into-a-long-column-occurs-when – Henrique Ordine Apr 07 '13 at 14:13
  • Otherwise, could you post a Stacktrace? – Henrique Ordine Apr 07 '13 at 14:14
  • Show us the code, how do you insert values into your blob field. – Andremoniy Apr 09 '13 at 05:26
  • I apologize for the lack of my response. Indeed my question was not about errors in my code. That is why I did not attach SSCCE code. The problem is not reproducible, it's realy rare and random. So my question was about Oracle-side diagnostic techniques or known issues regarding described problem. I have found Oracle bug 9018715. I'll check it and quote its text here as answer candidate later. – svaor Apr 11 '13 at 08:34
  • See the quoted text in [this](http://stackoverflow.com/q/14926134/1037210) question, it is about CLOB though. The drive version `11.1.0.7.0` contained by the `ojdbc6.jar` file (not `ojdbc14.jar`) solved the problem, (though it is a different problem domain). – Lion Apr 11 '13 at 11:22
  • same problem ORA-01461, maybe it will help someone http://stackoverflow.com/questions/22321663/oracle-hibernate-ora-01461-clob – Eugen Mar 12 '14 at 14:44

1 Answers1

4

I consider following oracle bug as a main cause of my errors. After upgrading database I'll check problem again and accept my own answer. I hope it will be useful for someone else.

By the way our platform is also AIX/64x.

Oracle Bug 9018715 "INTERMITTENT ORA-1461 IN UPDATES AND INSERTS WITH NO LONG COLUMN TYPES"

Type B - Defect

Fixed in Product Version 11.1.0.7.0

Severity 2 - Severe Loss of Service

Product Version 10.2.0.4.0

Status 93 - Closed, Not Verified by Filer

Platform 212 - IBM AIX on POWER Systems (64-bit)

Created Oct 14, 2009

Platform Version 5.3

Updated Dec 27, 201

Base Bug N/A

Database Version 10.2.0.4.0

Affects Platforms Generic

Product Source Oracle

PROBLEM:

ORA-1461: can bind a LONG value only for insert into a LONG column is received frequently in client applications.

The error is seen in insert and update statements, and for different tables. The statements are of this type: Insert into columns (, , ,..) values (:1, :2, :3, ...:n); or update set = :1, = :2, = :3,.. = :n where =:m; And none of the columns are of type lONG.

DIAGNOSTIC ANALYSIS:

The error started to appear after having upgraded rdbms from 10.2.0.3 to 10.2.0.4. It is occuring frequently, and on different tables. Once the error has occured, reexecuting the same sql often succeeds.

Database characterset : WE8ISO8859P15. Windows client. NLS settings: NLS_LANG=American_America.WE8ISO8859P15.

The applications are connecting via Jdbc thin driver version 10.2.0.1. A test with jdbc driver version 10.1.0.5 also fails.

svaor
  • 2,205
  • 2
  • 19
  • 41