oracle assigning integer value bigger than 3 digits to a bind variable gets ORA-06502 error-CodePudding

I need to get the generated id out from an oracle INSERT statement with RETURING INTO and subsequently access that value through zend framework paramContainer object.

If the id number is bigger than 3 digits I get this error:

ORA-06502: PL/SQL: errore : buffer della stringa di caratteri troppo piccolo di numero o valore

This is the zend-framework part:

$paramContainer = new ParameterContainer();
$paramContainer->offsetSet('cod_fis', $this->cf_dipendente, $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('matricola', $this->matr_dipendente, $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('dataPresentazione', $validatedData['dataPresentazione'], $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('obiettivo', $validatedData["obiettivo"], $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('retval', '0', $paramContainer::TYPE_STRING);
$paramContainer->offsetSetReference('id_progetto', 'retval');

This is the code returning the error:

DECLARE
  id_progetto NUMBER;
  retval      NUMBER;
BEGIN
  INSERT INTO LAVORO_TELE_PROGETTO (COD_FIS, MATRICOLA, STATO, STATO_INTERNO, DATA_PRESENTAZIONE, DATA_ULTIMA_MODIFICA)
  VALUES (:cod_fis, :matricola, 'A', 'C', TO_DATE(:dataPresentazione, 'YYYY-MM-DD HH24:MI:SS'),
          SYSDATE) RETURNING id INTO :id_progetto; /* id is 4 digits long */

  INSERT INTO LAVORO_TELE_OBIETTIVO (ID_PROGETTO, OBIETTIVO)
  VALUES (:id_progetto, :obiettivo);

  :retval := :id_progetto;


END;

While this code works:

DECLARE
  id_progetto NUMBER;
  retval      NUMBER;
BEGIN
  INSERT INTO LAVORO_TELE_PROGETTO (COD_FIS, MATRICOLA, STATO, STATO_INTERNO, DATA_PRESENTAZIONE, DATA_ULTIMA_MODIFICA)
  VALUES (:cod_fis, :matricola, 'A', 'C', TO_DATE(:dataPresentazione, 'YYYY-MM-DD HH24:MI:SS'),
          SYSDATE) RETURNING SUBSTR(id,1,3) INTO :id_progetto; /* here I'm truncating the number to 3 digits */

  INSERT INTO LAVORO_TELE_OBIETTIVO (ID_PROGETTO, OBIETTIVO)
  VALUES (:id_progetto, :obiettivo);

  :retval := :id_progetto;
END;

Thank you, Alex

CodePudding user response：

ORA-06502 can be result of you trying to execute a statement that resulted in an arithmetic, numeric, string, conversion, or constraint error.

You tried to assign a value to a numeric variable, but the value is larger than the variable can handle
- that's what you reported
You tried to assign a non-numeric value to a numeric variable and caused a conversion error
- that what smells wrong here

If you managed to make it work using substr, is it possible that value returned by the insert statement is actually a string (e.g. 123A) which can't fit into a NUMBER datatype variable?

Because, there's no problem in storing a 4-digits number into a non-constrained NUMBER datatype variable.

SQL> declare
  2    l_id_progetto number;
  3  begin
  4    l_id_progetto := 123456789;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

Furthermore, this is what you're doing (simplified):

DECLARE
  id_progetto NUMBER;
BEGIN
  INSERT INTO LAVORO_TELE_PROGETTO (...)
  RETURNING id INTO :id_progetto; 
                    ^
                    |
      what is this colon doing here?

Shouldn't it be

returning id into id_progetto

instead? Looks like you're inserting it into ... huh, something out of this PL/SQL procedure (e.g. an Oracle Forms field, Apex page item, whatever which actually is capable of holding only 3 characters, but - not the locally declared variable).

CodePudding user response：

You are mixing local PL/SQL variables defined in the DECLARE clause and globally defined bind variables prefixed with :. They are not the same variable even though they may have the same identifier.

What you probably want is:

DECLARE
  id_progetto NUMBER;  -- local variable
BEGIN
  INSERT INTO LAVORO_TELE_PROGETTO (
    COD_FIS,
    MATRICOLA,
    STATO,
    STATO_INTERNO,
    DATA_PRESENTAZIONE,
    DATA_ULTIMA_MODIFICA
  ) VALUES (
    :cod_fis, :matricola,
    'A',
    'C',
    TO_DATE(:dataPresentazione, 'YYYY-MM-DD HH24:MI:SS'),
    SYSDATE
  ) RETURNING id INTO id_progetto; -- into the local variable

  INSERT INTO LAVORO_TELE_OBIETTIVO (
    ID_PROGETTO, OBIETTIVO
  ) VALUES (
    id_progetto, :obiettivo -- read the local variable
  );

  :retval := id_progetto; -- out into the global bind variable
END;

Possibly using:

$paramContainer = new ParameterContainer();
$paramContainer->offsetSet('cod_fis', $this->cf_dipendente, $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('matricola', $this->matr_dipendente, $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('dataPresentazione', $validatedData['dataPresentazione'], $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('obiettivo', $validatedData["obiettivo"], $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('retval', '0', $paramContainer::TYPE_STRING);
$paramContainer->offsetSetReference('retval', 'retval');

or, do not use any local variables:

BEGIN
  INSERT INTO LAVORO_TELE_PROGETTO (
    COD_FIS,
    MATRICOLA,
    STATO,
    STATO_INTERNO,
    DATA_PRESENTAZIONE,
    DATA_ULTIMA_MODIFICA
  ) VALUES (
    :cod_fis, :matricola,
    'A',
    'C',
    TO_DATE(:dataPresentazione, 'YYYY-MM-DD HH24:MI:SS'),
    SYSDATE
  ) RETURNING id INTO :id_progetto; /* id is 4 digits long */

  INSERT INTO LAVORO_TELE_OBIETTIVO (
    ID_PROGETTO, OBIETTIVO
  ) VALUES (
    :id_progetto, :obiettivo
  );
END;

then:

$paramContainer = new ParameterContainer();
$paramContainer->offsetSet('cod_fis', $this->cf_dipendente, $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('matricola', $this->matr_dipendente, $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('dataPresentazione', $validatedData['dataPresentazione'], $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('obiettivo', $validatedData["obiettivo"], $paramContainer::TYPE_AUTO);
$paramContainer->offsetSet('id_progetto', '0', $paramContainer::TYPE_STRING);
$paramContainer->offsetSetReference('id_progetto', 'id_progetto');

You also should not pass dates as strings and should just use a DATE data type (in Oracle, a DATE always has a time component; which may mean that the equivalent in Zend is a timestamp data type rather than a date).

If you do pass it as an Oracle DATE then your code becomes:

BEGIN
  INSERT INTO LAVORO_TELE_PROGETTO (
    COD_FIS,
    MATRICOLA,
    STATO,
    STATO_INTERNO,
    DATA_PRESENTAZIONE,
    DATA_ULTIMA_MODIFICA
  ) VALUES (
    :cod_fis, :matricola,
    'A',
    'C',
    :dataPresentazione,
    SYSDATE
  ) RETURNING id INTO :id_progetto; /* id is 4 digits long */

  INSERT INTO LAVORO_TELE_OBIETTIVO (
    ID_PROGETTO, OBIETTIVO
  ) VALUES (
    :id_progetto, :obiettivo
  );
END;

and you would not want to use ::TYPE_AUTO in the Zend code but specify the actual type of the bind variable (if Zend supports ::TYPE_DATE or ::TYPE_TIMESTAMP, which is not obvious that it does).

For your Zend code, you want to make sure that whichever of id_progetto and/or retval you are using is declared as an OUT or IN/OUT parameter bind variable and not an IN parameter.

From this bug, it appears you need to use both offsetSet and offsetSetReference on the same bind parameter (but the Zend/Lamina documentation appears to be almost useless on giving descriptions of what these methods are supposed to do and does not give practical examples that obviously identify OUT or IN/OUT bind variables).