ORA-01722: invalid number

Asked

Viewed 1,044 times

-1

Help me with this error please, I wanted to understand why it is happening when trying to recover the ID of the enrollment field, when selecting the enrollment field in my select list in my form. Below is the SQL that I use to list the matricules

SELECT AERONAVE.MATRICULA AS MATRICULA, AERONAVE.ID AS ID FROM AERONAVE AERONAVE

Follow the PL/ SQL to fill in this foreign key when selecting the matricula field I want:

DECLARE
ID_OCORRENCIA NUMBER;

BEGIN

INSERT INTO OCORRENCIA (CLASSIFICACAO_CENIPA, DATA_UTC, UF, MUNICIPIO_OCORRENCIA)
VALUES (:P2_CLASSIFICACAO_CENIPA, :P2_DATA_UTC, :P2_UF, :P2_MUNICIPIO_OCORRENCIA)
RETURNING ID INTO ID_OCORRENCIA;

INSERT INTO AERONAVE_OCORRENCIA (ID_OCORRENCIA, ID_AERONAVE, OPERACAO)
VALUES (ID_OCORRENCIA, :P2_ID_AERONAVE, :P2_OPERACAO)

I want to enter the ID value in the ID_AERONAVE field.

sql oracle pl-sql

  • And what value is being passed to the query?

  • What are the data types for each column?

  • A varchar2 is being passed to the above query, and I wanted the return of the corresponding ID. To insert it in the OCCURRENCE table in the ID_AERONAVE field of type number. I think this is not being done correctly because of the error, but I used it in another form and it worked.

  • For your variable give a name other than the name of the table field. There may be an misinterpretation.

  • Good practice even in anonymous block and name variables as VS_<NAME> for char , VN_<name> for numerics etc

1 answer

0

A practical solution is to use the following command after Insert:

SELECT ID
INTO ID_OCORRENCIA
FROM OCORRENCIA 
WHERE CLASSIFICACAO_CENIPA = :P2_CLASSIFICACAO_CENIPA
      DATA_UTC = :P2_DATA_UTC,
      UF = :P2_UF,
      MUNICIPIO_OCORRENCIA = :P2_MUNICIPIO_OCORRENCIA;

Another solution would be to use the sub-select below to collect the new Inserted ID

At the time of Insert:

(SELECT MAX(ID_OCORRENCIA)+1 FROM OCORRENCIA)

Or after the Insert:

(SELECT MAX(ID_OCORRENCIA) FROM OCORRENCIA)

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.