How to access LONG RAW column content Greater than 32K using PL/SQL (Doc ID 337753.1)

Last updated on NOVEMBER 18, 2010

Applies to:

PL/SQL - Version: 8.1.7.0 to 10.2.0.1 - Release: 8.1.7 to 10.2
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 10.2.0.1   [Release: 8.1.7 to 10.2]
Information in this document applies to any platform.

Goal

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

is raised.

 

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.

Solution

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