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;

/

Exadata Storage : Grid disks based on the OFFSETs.

The Cells storage servers in Exadata offer the capability to create grid disks based on their offsets, Create the new grid disks in the order BASED ON THE OFFSET, using the sizes shown from the command list griddisk attributes name,size,offset.  

Lets see how we can do it : 

An example :

CellCLI> create griddisk DATA_CD_07_<Host Name> celldisk=CD_07_<Host Name>,size=220G

CellCLI> create griddisk RECO_CD_07_<Host Name> celldisk=CD_07_<Host Name>,size=308.6875G

CellCLI> create griddisk SYSTEMDG_CD_07_<Host Name> celldisk=CD_07_<Host Name>,size=29.125G

Run the previous query on the new grid disks and make sure all the offsets are matching (in the third column):

CellCLI> list griddisk where celldisk=CD_07_<Host Name> attributes name,size,offset

         DATA_CD_07_<Host Name>                 220G                32M

         RECO_CD_07_<Host Name>                 308.6875G           220.046875G

         SYSTEMDG_CD_07_<Host Name>             29.125G             528.734375G

 At the ASM level, the old disk was likely dropped because the disk drop timer has expired.  Log into +ASM1 instance and add the new disk.  Set the rebalance power higher (11) to perform faster and specify the failgroup if the default (name of Storage Server) is not used.

sql> alter diskgroup data drop disk DATA_CD_07_<Host Name> force;

sql> alter diskgroup data add {failgroup <FAILGROUP>} disk 'o/*/DATA_CD_07_<Host Name>' rebalance power 11;

Do the same for RECO and SYSTEMDG from a different ASM instance (to balance the work) or wait until DATA finishes on +ASM1.

If after disk replacement old celldisk and griddidsk are in state proactive failure or any other that prevents them from being deleted, try the following:

celcli> ALTER CELL RESTART SERVICES cellsrv

celcli> alter lun {lun number} reenable force

for example :

celcli> alter lun 0_0 reenable force

After this, delete cell and griddisks that corresponds to the physical disk

(it may be even done automatically).

Next, create cell disk and griddisk.

Finally after griddisks are created they should be presented in ASM but marked as candidates, but will need to be enabled.