CODEWITHSUNDEEP
oracle-databaseplsql
Jeremy
Jeremy

Reputation: 65

PL/SQL: numeric or value error - ORA-06502

Im am keep getting ORA-06502: PL/SQL: numeric or value error: character string buffer too small. Here is what i do.

CREATE OR REPLACE TYPE t_new_var AS OBJECT (
    var_v_1             varchar2(6 char),   
    var_v_2             varchar2(4 char),   
    var_v_3             varchar2(4 char),   
  CONSTRUCTOR FUNCTION t_new_var(SELF IN OUT NOCOPY t_new_var) RETURN SELF AS RESULT
);
CREATE OR REPLACE TYPE t_old AS OBJECT (
    var_v_1                  number(3),  -- nullable   
    var_v_2                  number(8),   -- nullable      
    var_v_3                  number(2),   -- nullable   
  CONSTRUCTOR FUNCTION t_old(SELF IN OUT NOCOPY t_old) RETURN SELF AS RESULT
);

In a procedure:

SELECT 

  TO_CHAR(t_old.var_v_1) AS one,
  TO_CHAR(t_old.var_v_2) AS two,
  TO_CHAR(t_old.var_v_3) AS three
  INTO
    t_new_v.var_v_1,                 
    t_new_v.var_v_2,             
    t_new_v.var_v_3                   
  FROM DUAL;

What is the problem here ? I have tried, CAST DECODE as well NVL same error

Upvotes: 0

Views: 2387

Answers (1)

Bishan
Bishan

Reputation: 15702

You tried to assign a value to a varchar variable, but the value is larger than the variable can handle.

You are going to select

TO_CHAR(t_old.var_v_2) AS two - number(8)

into

var_v_2 - varchar2(4 char),

You can't select 8 characters into a variable, size of 4 characters.

Upvotes: 1

Related Questions