Tuesday 11 October 2022

Causes of ORA-6502 Errors (PL/SQL: numeric or value error)

 

1) A common reason for this error can be when there is a mismatch between the data type of the variable and the value that is being assigned to it. For Example: Assigning character value to a numeric data type or assigning more value to a variable than it can hold. 


2) However its interesting that this issue can be caused by the mismatch between the client side NLS_LANG and NLS_CHARACTERSET of the database.


So the thing is when there is a mismatch between client side character set NLS_LANG and the database character set NLS_CHARACTERSET this can occur. When working with UNICODE database (UTF8/AL32UTF8), make sure that:


The NLS_LANG is not set at the client side. When NLS_LANG is not set, it will use database character set.


If NLS_LANG is set, then make sure PL/SQL bind variables is declared with enough buffer size so that the data conversion does not fail.


Some common code related issues are shown below:


SET SERVEROUTPUT ON


DECLARE

  V_VAR1 VARCHAR2(10);

  V_VAR2 NUMBER;

BEGIN

  V_VAR2 := 'MIKE';        /* VALID ORA-6502 */

  V_VAR1 := 'ABCDEFGHIKL'; /* VALID ORA-6502 */

END;

/


SET SERVEROUTPUT ON


DECLARE

  V_VAR1 VARCHAR2(3);

BEGIN

  SELECT 'ABCD' INTO V_VAR1 FROM DUAL;

END;

/


CREATE OR REPLACE FUNCTION

TEST_6502_ERROR

RETURN VARCHAR2

AS

BEGIN

   RETURN 'ASDF';

END;

/


SET SERVEROUTPUT ON


DECLARE

  V_GET_VALUE VARCHAR2(3);

BEGIN

  V_GET_VALUE := TEST_6502_ERROR;

END;

/

No comments:

Post a Comment