Passing procedure arguments to SELECT INTO where clause not working

Question

I have a compound trigger that checks a condition with a SELECT INTO. If the condition is met, I raise an exception and the values are not inserted. For this I am using a NO_DATA_FOUND exception, when everything is fine, meaning condition for raising my custom exception isn't met and the INSERT is executed.

What bothers me is that when I use a constant in my SELECT INTO where clause I get the NO_DATA_FOUND exception correctly. When I use the procedure argument with the same value as the constant, the NO_DATA_FOUND exception isn't getting raised.

Here's the procedure inside the trigger that makes the check:

PROCEDURE validate_system(
    v_abgleich_id   IN ctl_webadmin_abgleich.webadmin_abgleich_id%TYPE,
    v_valid_from    IN ctl_webadmin_abgleich.gueltig_von%TYPE,
    v_valid_through IN ctl_webadmin_abgleich.gueltig_bis%TYPE,
    v_source_system IN ctl_webadmin_abgleich.cldb_quellsystem_id%TYPE,
    v_target_system IN ctl_webadmin_abgleich.cldb_zielsystem_id%TYPE,
    v_table_id      IN ctl_webadmin_abgleich.cldb_webadmin_table_id%TYPE)
IS
  overlapping_abgleich EXCEPTION;
  src_target_same      EXCEPTION;
  from_date_null       EXCEPTION;
  from_date_gt         EXCEPTION;
  dummy                CHAR(1);
  v_ss NUMBER(10,0);
BEGIN
  v_ss := 4;
  dbms_output.put_line('v_ss' || v_ss);
  IF(validate_system.v_source_system = validate_system.v_target_system) THEN
    dbms_output.put_line('Raising src_target_same.');
    RAISE src_target_same;
  END IF;
  IF(validate_system.v_valid_from IS NULL) THEN
    dbms_output.put_line('Raising from_date_null.');
    RAISE from_date_null;
  END IF;
  IF(validate_system.v_valid_from > validate_system.v_valid_through) THEN
    dbms_output.put_line('Raising from_date_gt.');
    RAISE from_date_gt;
  END IF;    

  BEGIN

    SELECT 'X'
    INTO dummy
    FROM ctl_webadmin_abgleich ab
    WHERE ab.cldb_quellsystem_id = 4 AND ROWNUM = 1; -- constant raises NO_DATA_FOUND

-- WHERE ab.cldb_quellsystem_id = validate_system.v_source_system AND ROWNUM = 1; -- variable with the same value doesn't raise NO_DATA_FOUND exception.

    dbms_output.put_line('Conflicting recs foound: ' || v_ss);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('NO DATA FOUND:' || validate_system.v_source_system);
    NULL;
  END;

EXCEPTION
WHEN src_target_same THEN
  RAISE_APPLICATION_ERROR(-20001, 'Quellsystem darf nicht gleich dem Zielsystem sein!');
WHEN from_date_null THEN
  RAISE_APPLICATION_ERROR(-20002, 'VON Datum darf nicht NULL sein!');
WHEN from_date_gt THEN
  RAISE_APPLICATION_ERROR(-20003, 'VON Datum liegt vor Datum BIS!');
WHEN overlapping_abgleich THEN
  RAISE_APPLICATION_ERROR(-20004, 'Gültigkeitsbereiche mit schon existierenden Einträgen kollidieren!');
END validate_system;

In an empty table, If I pass a constant to the WHERE clause of the SELECT INTO I get NO_DATA_FOUND. But when I work with the variable -> validate_system.v_source_system, the exception isn't raised.

What am I doing wrong? This procedure is called from another procedure, that in turn is called in the AFTER STATEMENT block of the trigger.

The INSERT is the following:

Insert into CTL_WEBADMIN_ABGLEICH (webadmin_abgleich_id, gueltig_von, gueltig_bis, cldb_quellsystem_id, cldb_zielsystem_id, cldb_webadmin_table_id) 
values (12, to_date('20.01.20', 'DD.MM.RR'), to_date('20.05.20', 'DD.MM.RR'), 4, 6, 813);

And here's the whole trigger:

create or replace 
trigger abgleich_quellsystem_trg FOR INSERT OR
  UPDATE ON ctl_webadmin_abgleich COMPOUND TRIGGER type abgleich_type IS record ( abgleich_id ctl_webadmin_abgleich.webadmin_abgleich_id%TYPE, valid_from ctl_webadmin_abgleich.gueltig_von%TYPE, valid_through ctl_webadmin_abgleich.gueltig_bis%TYPE, source_system ctl_webadmin_abgleich.cldb_quellsystem_id%TYPE, target_system ctl_webadmin_abgleich.cldb_zielsystem_id%TYPE, table_id ctl_webadmin_abgleich.cldb_webadmin_table_id%TYPE );
type abgleich_recs_type
IS
  TABLE OF abgleich_type INDEX BY pls_integer;
  abgleich_rec abgleich_recs_type;
PROCEDURE lock_system(
    v_abgleich_id   IN ctl_webadmin_abgleich.webadmin_abgleich_id%TYPE,
    v_valid_from    IN ctl_webadmin_abgleich.gueltig_von%TYPE,
    v_valid_through IN ctl_webadmin_abgleich.gueltig_bis%TYPE,
    v_source_system IN ctl_webadmin_abgleich.cldb_quellsystem_id%TYPE,
    v_target_system IN ctl_webadmin_abgleich.cldb_zielsystem_id%TYPE,
    v_table_id      IN ctl_webadmin_abgleich.cldb_webadmin_table_id%TYPE)
IS
  abgleich_id ctl_webadmin_abgleich.webadmin_abgleich_id%TYPE;
  valid_from ctl_webadmin_abgleich.gueltig_von%TYPE;
  valid_through ctl_webadmin_abgleich.gueltig_bis%TYPE;
  source_system ctl_webadmin_abgleich.cldb_quellsystem_id%TYPE;
  target_system ctl_webadmin_abgleich.cldb_zielsystem_id%TYPE;
  table_id ctl_webadmin_abgleich.cldb_webadmin_table_id%TYPE;
BEGIN
  dbms_output.put_line('Locking...' || lock_system.v_abgleich_id);
  SELECT ab.webadmin_abgleich_id,
    ab.gueltig_von,
    ab.gueltig_bis,
    ab.cldb_quellsystem_id,
    ab.cldb_zielsystem_id,
    ab.cldb_webadmin_table_id
  INTO abgleich_id,
    valid_from,
    valid_through,
    source_system,
    target_system,
    table_id
  FROM ctl_webadmin_abgleich ab
  WHERE ab.webadmin_abgleich_id = lock_system.v_abgleich_id FOR UPDATE;
END lock_system;


PROCEDURE validate_system(
    v_abgleich_id   IN ctl_webadmin_abgleich.webadmin_abgleich_id%TYPE,
    v_valid_from    IN ctl_webadmin_abgleich.gueltig_von%TYPE,
    v_valid_through IN ctl_webadmin_abgleich.gueltig_bis%TYPE,
    v_source_system IN ctl_webadmin_abgleich.cldb_quellsystem_id%TYPE,
    v_target_system IN ctl_webadmin_abgleich.cldb_zielsystem_id%TYPE,
    v_table_id      IN ctl_webadmin_abgleich.cldb_webadmin_table_id%TYPE)
IS
  overlapping_abgleich EXCEPTION;
  src_target_same      EXCEPTION;
  from_date_null       EXCEPTION;
  from_date_gt         EXCEPTION;
  dummy                CHAR(1);
BEGIN
  IF(validate_system.v_source_system = validate_system.v_target_system) THEN
    dbms_output.put_line('Raising src_target_same.');
    RAISE src_target_same;
  END IF;
  IF(validate_system.v_valid_from IS NULL) THEN
    dbms_output.put_line('Raising from_date_null.');
    RAISE from_date_null;
  END IF;
  IF(validate_system.v_valid_from > validate_system.v_valid_through) THEN
    dbms_output.put_line('Raising from_date_gt.');
    RAISE from_date_gt;
  END IF;    

  BEGIN

    SELECT 'X'
    INTO dummy
    FROM ctl_webadmin_abgleich ab
    WHERE ab.cldb_quellsystem_id = 5 AND ROWNUM = 1;

    dbms_output.put_line('Conflicting recs foound: ' || validate_system.v_source_system);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('NO DATA FOUND:' || validate_system.v_source_system);
    NULL;
  END;

EXCEPTION
WHEN src_target_same THEN
  RAISE_APPLICATION_ERROR(-20001, 'Quellsystem darf nicht gleich dem Zielsystem sein!');
WHEN from_date_null THEN
  RAISE_APPLICATION_ERROR(-20002, 'VON Datum darf nicht NULL sein!');
WHEN from_date_gt THEN
  RAISE_APPLICATION_ERROR(-20003, 'VON Datum liegt vor Datum BIS!');
WHEN overlapping_abgleich THEN
  RAISE_APPLICATION_ERROR(-20004, 'Gültigkeitsbereiche mit schon existierenden Einträgen kollidieren!');
END validate_system;



PROCEDURE validate_systems
IS
  sys_idx pls_integer;
BEGIN
  dbms_output.put_line('Start validation...');
  sys_idx := abgleich_rec.first;
  LOOP
    EXIT
  WHEN sys_idx IS NULL;
    validate_system(abgleich_rec(sys_idx).abgleich_id, abgleich_rec(sys_idx).valid_from, abgleich_rec(sys_idx).valid_through, abgleich_rec(sys_idx).source_system, abgleich_rec(sys_idx).target_system, abgleich_rec(sys_idx).table_id);
    sys_idx := abgleich_rec.next(sys_idx);
  END LOOP;
END validate_systems;
BEFORE EACH ROW
IS
BEGIN
  abgleich_rec(:NEW.webadmin_abgleich_id).abgleich_id   := :NEW.webadmin_abgleich_id;
  abgleich_rec(:NEW.webadmin_abgleich_id).valid_from    := :NEW.gueltig_von;
  abgleich_rec(:NEW.webadmin_abgleich_id).valid_through := :NEW.gueltig_bis;
  abgleich_rec(:NEW.webadmin_abgleich_id).source_system := :NEW.cldb_quellsystem_id;
  abgleich_rec(:NEW.webadmin_abgleich_id).target_system := :NEW.cldb_zielsystem_id;
  abgleich_rec(:NEW.webadmin_abgleich_id).table_id      := :NEW.cldb_webadmin_table_id;
END BEFORE EACH ROW;

AFTER STATEMENT
IS
BEGIN
  dbms_output.ENABLE (buffer_size => NULL);
  dbms_output.put_line('Start validation...');
  validate_systems;
  dbms_output.put_line('Validation successful.');
END AFTER STATEMENT;
END abgleich_quellsystem_trg;

And the DDL for the table:

--------------------------------------------------------
--  DDL for Table CTL_WEBADMIN_ABGLEICH
--------------------------------------------------------

  CREATE TABLE "CLDBDEF"."CTL_WEBADMIN_ABGLEICH" 
   (    "WEBADMIN_ABGLEICH_ID" NUMBER(10,0), 
    "USERID_INS" NUMBER(10,0), 
    "TIMESTAMP_INS" TIMESTAMP (6), 
    "TIMESTAMP_UPD" TIMESTAMP (6), 
    "USERID_UPD" NUMBER(10,0), 
    "GUELTIG_VON" DATE, 
    "GUELTIG_BIS" DATE, 
    "CLDB_QUELLSYSTEM_ID" NUMBER(10,0), 
    "CLDB_WEBADMIN_TABLE_ID" NUMBER(10,0), 
    "CLDB_ZIELSYSTEM_ID" NUMBER(10,0)
   );
--------------------------------------------------------
--  DDL for Index SYS_C0027983
--------------------------------------------------------

  CREATE UNIQUE INDEX "CLDBDEF"."SYS_C0027983" ON "CLDBDEF"."CTL_WEBADMIN_ABGLEICH" ("WEBADMIN_ABGLEICH_ID") ;
--------------------------------------------------------
--  Constraints for Table CTL_WEBADMIN_ABGLEICH
--------------------------------------------------------

  ALTER TABLE "CLDBDEF"."CTL_WEBADMIN_ABGLEICH" ADD PRIMARY KEY ("WEBADMIN_ABGLEICH_ID");
  ALTER TABLE "CLDBDEF"."CTL_WEBADMIN_ABGLEICH" MODIFY ("WEBADMIN_ABGLEICH_ID" NOT NULL ENABLE);

Thank you all for the help! al

UPDATE: OK, I've added

select count(*) into recnr from ctl_webadmin_abgleich ab;
dbms_output.put_line('Rec nr: ' || recnr);

right after the SELECT INTO, and apparently the INSERT was executed and the count(*) returns 1. How's this possible? Can someone explain this? Thanks!

Answer 1

As Boneist noticed the record you are attempting to insert is already present in the table by the time you are checking for conflicts. Change your select statement to exclude the record id of the record you are inserting and it should operate as expected:

SELECT 'X'
INTO dummy
FROM ctl_webadmin_abgleich ab
WHERE ab.cldb_quellsystem_id = v_source_system 
AND ab.WEBADMIN_ABGLEICH_ID != v_abgleich_id -- Add this condition
AND ROWNUM = 1;