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