0
So guys, I’m having a problem inserting a data into my Oracle tables, entering the data through a PLSQL routine, and I’d like to know how to insert data into a respective table, but if one of the fields is empty, I want the NULL value not to be inserted, follow my routine below:
DECLARE
ID_OCORRENCIA NUMBER;
BEGIN
INSERT INTO OCORRENCIA (CLASSIFICACAO_CENIPA, DATA_UTC, UF, MUNICIPIO_OCORRENCIA, LATITUDE, LONGITUDE, NUMERO_FICHA, HISTORICO, HORA_UTC)
VALUES (:P2_CLASSIFICACAO_CENIPA, :P2_DATA_UTC, :P2_UF, :P2_MUNICIPIO_OCORRENCIA, :P2_LATITUDE, :P2_LONGITUDE, :P2_NUMERO_FICHA, :P2_HISTORICO, :P2_HORA_UTC)
RETURNING ID INTO ID_OCORRENCIA;
INSERT INTO TIPO_OCORRENCIA (ID_OCORRENCIA, TIPO) VALUES (ID_OCORRENCIA, :P2_TIPO_OCORRENCIA);
INSERT INTO TIPO_OCORRENCIA (ID_OCORRENCIA, TIPO) VALUES (ID_OCORRENCIA, :P2_TIPO_OCORRENCIA_1);
END
What happens here is that when entering a type of occurrence in the table TIPO_OCORRENCIA, it always inserted in the table the value I report and also the value NULL. There is a way for this not to happen, and home I wanted to insert only one dice, have only one dice in my table and not two.
When referring to ID_OCORRENCIA how you differentiate if you are referring to the table field or the local variable of your routine?
– anonimo
I think you’re making a mess. It’s not entering the NULL value. It, in fact, is not entering any value, when you see NULL it is because nothing has been entered. To avoid that field being null, you have two options: treat this in the code or create the field in the table with the NOT NULL clause.
– Reginaldo Rigo
True what you said, the field is not null because I enter the value of the foreign key in the table. But I wanted to know precisely how not to insert this record in the table, if it is only with the foreign key filled, record this the TYPE in the case.
– Otávio
Problem already solved, I used a conditional command "IF :P2_TIPO_OCORRENCIA_1 IS NOT NULL THEN INSERT INTO ...." problem solved, but thanks for the tips.
– Otávio