How to access LONG RAW column content Greater than 32K using PL/SQL
Last updated on NOVEMBER 18, 2010
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms