CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
jpleouf
jpleouf

Reputation: 1

Oracle insert if not exists inside Procedure

I have a procedure, that supposed to insert a new record after checking the record is not exists. When i run it, Oracle says :

Informe de error - ORA-01008: no todas las variables han sido enlazadas ORA-06512: en "SALUD.INSERT_MASS_MULTIMORB_FINALIZA", linea 71 ORA-06512: en linea 1 01008. 0

I have this query :

  EXECUTE IMMEDIATE 'insert into salud.multimor_paciente_detalle(id_paciente,id_patologia,fecha,puntaje,vigente) select :val1,:val2,:val3,:val4,:val5 from dual where not exists (select * from salud.multimor_paciente_detalle where id_paciente=:val1 and id_patologia=:val2 and fecha=:val3 and puntaje=:val4 and vigente=:val5)'
  using registro.paciente_id,registro.patologia_id,registro.fecha,registro.valor_puntaje,'S';
  EXECUTE IMMEDIATE 'commit';

What is wrong ?

Thanks for your help

Note : this code works well :

  EXECUTE IMMEDIATE 'insert into salud.multimor_paciente_detalle(id_paciente,id_patologia,fecha,puntaje,vigente) values(:val1,:val2,:val3,:val4,:val5)'
  using registro.paciente_id,registro.patologia_id,registro.fecha,registro.valor_puntaje,'S';
  EXECUTE IMMEDIATE 'commit';

Upvotes: 0

Views: 96

Answers (1)

APC
APC

Reputation: 146239

What is wrong ?

The specific problem with your query is that you repeat the bind variables in the INSERT statement and the NOT EXISTS subquery. That means you have to repeat them in the USING clause, because stored procedures (unlike anonymous blocks) don't remember placeholder names in an EXECUTE IMMEDIATE statement. This is annoying but it is also the documented behaviour.

A more general problem is that the construct is wrong. Use a MERGE statement instead.

The most wrong thing is the use of dynamic SQL. There is nothing in your statement which necessitates dynamic SQL. As your question demonstrates, dynamic SQL is difficult to write correctly and hard to debug. It is also undesirable architecturally (because it is risky in production, it thwarts impact analysis, it is trickier to maintain). So please don't use it unless there is no other way to implement the requirement.

Upvotes: 2

Related Questions