Using DBMS_LOB.SUBSTR on a BLOB results in ORA-06502

Question

When I try to run the dbms_lob.substr function on a BLOB field, I get the following error:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

ORA-06512: at line 1

My query:

select dbms_lob.substr(my_report, 10000, 1) 
from my_table where my_table.report_id = :myid

According to the dbms_lob.substr documentation, I should be able to use a value in the 2nd parameter up to 32767, and the size of the report is over 200,000 bytes, so it is within the range.

After playing with the number, I have found that the make value that I can use in the amount parameter (2nd parameter) to the substr function is 2000.

Does anyone know why?

Answer 1

The function is returning the result as the RAW datatype, and the RAW datatype has a maximum size of 2000 bytes.

References:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#SQLRF0021

http://dbaforums.org/oracle/index.php?showtopic=8445

Answer 2

The length limitation of 2000 octets applies to the sql engine only. In Pl/sql you may exploit the whole range of up to a length of 32767 (2^15-1).

As of 12c, the length limitation of 2000 has been lifted.

However, prior to 12c there is a length limitation in the sqlplus client that does not allow for column sizes above 4000 ( The value for 11g2 ).

The following code works for 11g2 and later

var myid number;
exec :myid := 1234; -- whatever

DECLARE
    l_r   RAW(32767);
BEGIN
    select dbms_lob.substr ( my_report, 2000, 1 ) head
      into l_r
      from my_table
     where my_table.report_id = :myid  
       ;

  l_r := UTL_RAW.COPIES ( l_r, 10 );
  dbms_output.put_line ( 'id ' || :myid || ', len(l_r) = ' || utl_raw.length(l_r));
END;
/
show errors 

... while this version requires 12c:

var myid number;
exec :myid := 1234; -- whatever

DECLARE
    l_r   RAW(32767);
BEGIN
    select dbms_lob.substr ( my_report, 32767, 1 ) head
      into l_r
      from my_table
     where my_table.report_id = :myid  
       ;

  dbms_output.put_line ( 'id ' || :myid || ', len(l_r) = ' || utl_raw.length(l_r));
END;
/
show errors