ORA-01461 When Working with Varchar2 > 4k Inside PL/SQLl Block (Doc ID 1096471.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version 8.0.6.0 and later
Information in this document applies to any platform.
***Checked for relevance on 14-Aug-2013***

Symptoms

When working with a varchar2 variable > 4k in PL/SQL, an error ORA-01461 is occurring when inserting into a table using the variable.

drop table test;
create table test (memo varchar2(4000));

set serveroutput on
DECLARE
  sResultset varchar2(30000);
BEGIN
  sResultset := rpad('*', 4001, '*');
  dbms_output.put_line(length(substr(sResultset,1,4000)));
  insert into test(memo) values (substr(sResultset,1,4000));
END;
/


ERROR OUTPUT:

4000
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 7



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms