What could cause ORA-00910: specified length too long for its datatype?

Question

While running rdsadmin.rds_file_util.read_text_file, I got error "ORA-00910: specified length too long for its datatype". After extensive investigation, I found out the cause.

The system is Oracle Database 19c. The data type is VARCHAR2. Parameter max_string_size is "STANDARD".

Answer 1

These scenarios could lead to ORA-00910

Create table

The most obvious possible cause of ORA-00910 is during table creation.

    create table t (
        v varchar2(4001)
    );

    v varchar2(4001)
               *
ERROR at line 2:
ORA-00910: specified length too long for its datatype

Pipe row with > 4000 characters in a field

It is also possible to get ORA-00910 while executing a pipelined table function. In the following code, if l_text.text length exceeds 4000 characters, ORA-00910 will appear.

    create or replace package test_str_pkg
    is
    
        type text_t is  record (
            text    varchar2(32767)
        );
    
        type text_list_t is table of text_t;
    
        function test_str
        return text_list_t
        pipelined;
    
    end test_str_pkg;
    /
    
    create or replace package body test_str_pkg
    is
        function test_str
        return text_list_t
        pipelined
        is
            l_text    text_t;
        begin
            l_text.text := rpad('a', 4001, 'a');
            pipe row(l_text);
        end test_str;
    
    end test_str_pkg;
    /
    
    select * from table(
        test_str_pkg.test_str
    );

Package created.
  
Package body created.

    test_str_pkg.test_str
    *
ERROR at line 2:
ORA-00910: specified length too long for its datatype

This simplified example is similar to the logic in RDS read_text_file.

A workaround is:

    select text
    from table(
        rdsadmin.rds_file_util.read_text_file(
            :dir,
            :filename
        )
    )
    where length(text) <= 4000

Related errors

Please distinguish ORA-00910 from other, similarly-worded errors.

PLS-00215: String length constraints must be in range (1 .. 32767)

    declare
        l_str varchar2(32768);
    begin
        null;
    end;
    /

    l_str varchar2(32768);        -- PLS-00215: String length constraints must be in range (1 .. 32767)
                   *
ERROR at line 2:
ORA-06550: line 2, column 20:
PLS-00215: String length constraints must be in range (1 .. 32767)

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    declare
        l_str varchar2(32767);
    begin
        l_str := rpad('a', 32768, 'a');
    end;
    /

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4