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

Question

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

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

ORA-06512: on line 1

My request:

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 have to use the value in the second parameter up to 32767, and the report size is more than 200,000 bytes, so it is within the range.

After playing with the number, I found that the make value that I can use in the quantity parameter (second parameter) for the substr function is 2000.

Does anyone know why?

Answer 1

The function returns the result as a RAW data type, and the RAW data type has a maximum size of 2000 bytes.

Literature:

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

A 2000 octet length limit applies only to the sql engine. In Pl / sql, you can use the entire range up to 32767 (2 ^ 15-1).

Starting from 12c, the 2000 length limit was removed.

However, up to 12c there is a length limit in the sqlplus client that does not allow column sizes above 4000 (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 

... although 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