How to access LONG RAW column content Greater than 32K using PL/SQL
(Doc ID 337753.1)
Last updated on AUGUST 04, 2018
Applies to:PL/SQL - Version: 126.96.36.199 to 10.2.0.1 - Release: 8.1.7 to 10.2
Oracle Server - Enterprise Edition - Version: 188.8.131.52 to 10.2.0.1 [Release: 8.1.7 to 10.2]
Information in this document applies to any platform.
A limitation of PL/SQL is the max size of a RAW variable of size 32760.
As a result of this limitation PL/SQL only allows to to query LONG RAW columns that can be stored in this variable, otherwise the error
ORA-06502: PL/SQL: numeric or value error
Piecewise fetching using DBMS_SQL.COLUMN_VALUE_LONG only seems to work for LONG (character) columns.
As a result this is a "clever" method to temporarily convert the content into a BLOB now allowing the full capability of the DBMS_LOB package or even some of the overloaded built-in functions to accomplish processing of the content.
Although this has not been tested on columns greater than 32K it should work.
This is not extremely efficient code but the alternative to this is having to write code using ProC, OCI, JDBC, ODBC etc.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document