Subquery Using Analytical Function Fails with ORA-6502 (Doc ID 852834.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 9.2.0.8 to 11.1.0.7
This problem can occur on any platform.

Symptoms

Using an analytical function such as LAST_VALUE, MIN, MAX in a subquery to select a CHAR variable defined (with same length as the table definition) fails with error :

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

CREATE TABLE abc (
cont_no char(8),
plan_id char(7));

INSERT INTO abc VALUES ('12345678', '7654321');  

DECLARE 
   c_Plan_ID abc.plan_id%type; 
BEGIN 
   SELECT plan_id
   INTO c_Plan_ID
   FROM (
        SELECT first_value(plan_id) OVER (ORDER BY cont_no DESC) AS plan_id
        FROM ABC
    );
END;
/

Cause

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