[SOLVED] Error converting varchar to numeric (but there's no number)

Error converting varchar to numeric (but there's no number)

I have a table with several columns, like this:

CREATE TABLE CRM.INFO_ADICIONAL
(
  ID_INFO_ADICIONAL        NUMBER(10)           NOT NULL,
  NOMBRE                   VARCHAR2(100 BYTE)   NOT NULL,
  OBLIGATORIO              NUMBER(1)            NOT NULL,
  TIPO_DATO                VARCHAR2(2 BYTE)     NOT NULL,
  ACTIVO                   NUMBER(1)            NOT NULL,
  ID_TIPO_REQUERIMIENTO    NUMBER(10)           NOT NULL,
  ID_USUARIO_AUDIT         NUMBER(10)           NOT NULL,
  ORDEN                    NUMBER(3)            DEFAULT 1,
  RECHAZO_POR_NO           NUMBER(1),
  ID_TIPO_ARCHIVO_ADJUNTO  NUMBER(10),
  SOLICITAR_EN             VARCHAR2(30 BYTE),
  ID_CONSULTA              NUMBER(10),
  COMBO_ID                 VARCHAR2(40 BYTE),
  APLICAR_COMO_VENC        NUMBER(1),
  MODIFICABLE              NUMBER(1)            DEFAULT 0,
  ID_AREA_GESTION          NUMBER(10),
  ID_TAREA                 NUMBER(10)
)

The "COMBO_ID" column is the target. It is defined as VARCHAR, but when I'm trying to insert a row, TOAD displays

"ORA-06502: PL/SQL: error : error de conversión de carácter a número numérico o de valor"

Or a 'numeric conversion error', in english.

This table have some pre-existing data, and I even found some rows including values at COMBO_ID column, all of them being VARCHAR, i.e.:

NACION (Nation), SEXO (Sex), etc

I tried a few simple SELECT statements

SELECT
    ID_INFO_ADICIONAL,
    NOMBRE,
    OBLIGATORIO,
    TIPO_DATO,
    ACTIVO,
    ID_TIPO_REQUERIMIENTO,
    ID_USUARIO_AUDIT,
    ORDEN,
    RECHAZO_POR_NO,
    ID_TIPO_ARCHIVO_ADJUNTO,
    SOLICITAR_EN,
    COMBO_ID,
    APLICAR_COMO_VENC,
    ID_CONSULTA,
    MODIFICABLE,
    ID_AREA_GESTION,
    ID_TAREA
INTO
    pRegistro
FROM
    crm.info_adicional

where pRegistro is declared as

pRegistro INFO_ADICIONAL%ROWTYPE;

Again, I'm still getting this 'numeric conversion error'. But, wait, if I hardcode the SELECT value in COMBO_ID column with a NUMBER:

SELECT
    --other columns
    123456 COMBO_ID,
    --other columns
INTO
    pRegistro
FROM
    crm.info_adicional

It works, what the heck, it's defined as VARCHAR. If I do the same but harcoding a string, it fails to execute again

Already tried in my DEV environment, and it's working fine.

I'm not a pro in Oracle, but I feel pretty lost. Could it be that tables get "confused"? Any clues?

Solution

Okay, I already found the answer.

Quoting Oracle Documentation:

The %ROWTYPE attribute provides a record type that represents a row in a table or view. Columns in a row and corresponding fields in a record have the same names and datatypes.

So, basically, the SELECT statement needed to be in the same order as the table columns definition.

In my case, I had a few columns (including COMBO_ID) in a different order. Tried, re-ordering, and works like a charm.

Thank you all for the support.