ORA-01461 can bind a LONG value only for insert into a LONG column-Occurs when querying

Question

When I try to query objects  I end up with following error   ORA-01461  can bind a LONG value only for insert into a LONG column   Could someone please help me on the cause and solution of the problem

User · Answer

It can also happen with varchar2 columns. This is pretty reproducible with PreparedStatements through JDBC by simply

creating a table with a column of varchar2 (20 or any arbitrary length) and
inserting into the above table with a row containing more than 20 characters

So as above said it can be wrong with types, or column width exceeded.

Also note that as varchar2 allows 4k chars max, the real limit will be 2k for double byte chars

Hope this helps

User · Answer

I encountered this error message when trying to insert String into an XMLTYPE column   Specifically using Java s PreparedStatement like this   ps setString  XML   document     where XML here is defined as XMLTYPE

User · Answer

I had the same problem with Entity Framework database first on all CLOB columns    As a workaround  I filled the text values with spaces to be at least 4000 in width in insert operations  did not come with any better solution

User · Answer

I encountered the same problem using Siebel REXPIMP  registry import  when using the latest Instant Client driver  To fix the issues  use the Siebel provided Data Direct driver instead  The DLL is SEOR823 DLL

User · Answer

I was facing the same issue and solve it by just replacing VARCHAR with CLOB  This link helped me out

User · Answer

In my particular case  I was trying to store a Base64 encoded file into a table BLOB field  using Mybatis   So in my xml I had    lt insert id  save     parameterType     DTO  gt       lt selectKey keyProperty  id  resultType  long  order  BEFORE  gt          SELECT SEQ nextVal FROM DUAL      lt  selectKey gt      insert into MYTABLE          ID                       PDF       values             id  jdbcType VARCHAR                          tcPdf  jdbcType BLOB          lt  insert gt    and in my DTO   String getPdf        return pdf      That makes to Mybatis threat as if were a String char sequence and try to store it as a Varchar  So my solution was the following   In my DTO   Byte   getPdf        return pdf getBytes        And worked   I hope this could help anybody

User · Answer

Adding another use case where I found this happening  I was using a ADF Fusion application and the column type being used was a varchar2 4000  which could not accommodate the text and hence this error

User · Answer

I have a solution for Java JPA eclipselink oracle when insert a long xml string   4000  into a XMLTYPE column at Insert XML with more than 4000 characters into a Oracle XMLTYPE column  For clarity  include the same contents here in case the link not working  You need to convert xml string for more than 4000 charcaters into SQLXML type first   Environment  jpa 2 1 0  eclipselink 2 5 2  oracle db 11gr2  SQL   CREATE TABLE  XMLTEST     ID  NUMBER 10 0  NOT NULL ENABLE      DESCRIPTION  VARCHAR2 50 CHAR  NOT NULL ENABLE      XML TXT   XMLTYPE  NOT NULL ENABLE     INSERT INTO XMLTEST  ID  DESCRIPTION  XML TXT  VALUES  101   XML DATA     lt data gt TEST lt  data gt     COMMIT   DROP TABLE  XMLTEST     Java Code  String sql    INSERT INTO XMLTEST  ID  DESCRIPTION  XML TXT  VALUES             String xmlDataStr     lt data gt test    lt  data gt       a long xml string with length  gt  4000 characters Connection con   getEntityManager   unwrap Connection class   SQLXML sqlXml   con createSQLXML    sqlXml setString xmlDataStr     Java code - use PreparedStatement  PreparedStatement pstmt   con prepareStatement sql   pstmt setLong 1  201   pstmt setLong 2   Long XML Data    pstmt setSQLXML 3  sqlXml   pstmt execute      Java code - use native query instead of PreparedStatement  Query query   getEntityManager   createNativeQuery sql   query setParameter 1  301   query setParameter 2   Long XML Data    query setParameter 3  sqlXml   query executeUpdate

User · Answer

A collegue of me and I found out the following   When we use the Microsoft  NET Oracle driver to connect to an oracle Database  System Data OracleClient OracleConnection   And we are trying to insert a string with a length between 2000 and 4000 characters into an CLOB or NCLOB field using a database-parameter  oraCommand CommandText    INSERT INTO MY TABLE  NCLOB COLUMN  VALUES   PARAMETER1       Add string-parameters with different lengths    oraCommand Parameters Add  PARAMETER1   new string      1900       ok oraCommand Parameters Add  PARAMETER1   new string      2500        Exception   oraCommand Parameters Add  PARAMETER1   new string      4100       ok oraCommand ExecuteNonQuery       any string with a length under 2000 characters will not throw this exception any string with a length of more than 4000 characters will not throw this exception only strings with a length between 2000 and 4000 characters will throw this exception   We opened a ticket at microsoft for this bug many years ago  but it has still not been fixed

User · Answer

Kiran s answer is definetely the answer for my case   In code part I split string to 4000 char strings and try to put them in to db   Explodes with this error   The cause of the error is using utf chars  those counts 2 bytes each  Even I truncate to 4000 chars in code sth  like String Take 4000    oracle considers 4001 when string contains      or any other non-eng non ascii to be precise  which are represented with two or bytes in utf8  characters

User · Answer

Applications using JDBC 10 1 has got a bug  Doc ID 370438 1  and can throw the same ORA-01461 exception while working with UTF8 character set database even though inserted characters are less than the maximum size of the column   Recommended Solution  - Use 10gR2 JDBC drivers or higher in such case   HTH

User · Answer

Ok  well  since you didn t show any code  I ll make a few assumptions here   Based on the ORA-1461 error  it seems that you ve specified a LONG datatype in a select statement   And you re trying to bind it to an output variable   Is that right   The error is pretty straight forward   You can only bind a LONG value for insert into LONG column   Not sure what else to say   The error is fairly self-explanatory   In general  it s a good idea to move away from LONG datatype to a CLOB   CLOBs are much better supported  and LONG datatypes really are only there for backward compatibility   Here s a list of LONG datatype restrictions  Hope that helps

User · Answer

This error occurs when one attempts to use a varchar variable longer than 4000 bytes in an SQL statement  PL SQL allows varchars up to 32767 bytes  but the limit for database tables and SQL language is 4000  You can t use PL SQL variables that SQL doesn t recognize in SQL statements  an exception  as the message explains  is a direct insert into a long-type column   create table test  v varchar2 10   c clob     declare   shortStr varchar2 10      0123456789     longStr1 varchar2 10000     shortStr    longStr2 varchar2 10000   begin   for i in 1    10000   loop     longStr2    longStr2     X     end loop     -- The following results in ORA-01461   insert into test v  c  values longStr2  longStr2      -- This is OK  the actual length matters  not the declared one   insert into test v  c  values longStr1  longStr1      -- This works  too  a direct insert into a clob column    insert into test v  c  values shortStr  longStr2      -- ORA-01461 again  You can t use longStr2 in an SQL function    insert into test v  c  values shortStr  substr longStr2  1  4000    end

User · Answer

I had the same problem using PHP and prepared statements on a VARCHAR2 column  My string didn t exceeed the VARCHAR2 size  The problem was that I used -1 as maxlength for binding  but the variable content changed later   In example    sMyVariable        rParsedQuery   oci parse  rLink   INSERT INTO MyTable  MyVarChar2Column  VALUES   MYPLACEHOLDER     oci bind by name  rParsedQuery    MYPLACEHOLDER    sMyVariable  -1  SQLT CHR     sMyVariable    a   oci execute  rParsedQuery  OCI DEFAULT    sMyVariable    b   oci execute  rParsedQuery  OCI DEFAULT     If you replace the -1 with the max column width  i  e  254  then this code works  With -1 oci bind by param uses the current length of the variable content  in my case 0  as maximum length for this column  This results in ORA-01461 when executing

User · Answer

This ORA-01461  does not occur only while inserting into a Long column  This error can  occur when binding a long string for insert into a VARCHAR2 column and most commonly occurs when there is a multi byte means single char can take more than one byte space in oracle  character conversion issue   If the database is UTF-8 then  because of the fact that each character can take up to 3 bytes  conversion of 3 applied to check and so actually limited to use 1333 characters to insert into varchar2 4000    Another solution would be change the datatype from varchar2 4000  to CLOB

[oracle] ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying

Examples related to oracle

Examples related to oracle11g