CODEWITHSUNDEEP
oracle-databaseloopsexceptionplsql
Gamidron
Gamidron

Reputation: 133

plsql complete loop before if statement triggers?

So, for my current project I want to check if the given IP address is a valid IP address. I do this by checking a table of valid IP ranges.

CREATE OR REPLACE TRIGGER reactie_check
BEFORE INSERT OR UPDATE ON reactie FOR EACH ROW   
  DECLARE
    ipadres        NUMBER(20);
    fromipnr       NUMBER(20);
    toipnr         NUMBER(20);

    CURSOR fromtoip IS
      SELECT fromip, toip
        FROM dutchip;

    niet_nederlands EXCEPTION;
    not_nederlands VARCHAR2(1);   
  BEGIN

    ipadres := IPTOINT(:new.ipadres);
    dbms_output.put_line(ipadres);

    FOR cc IN fromtoip
    LOOP
      fromipnr := IPTOINT(cc.fromip);
      toipnr := IPTOINT(cc.toip);

      IF ipadres BETWEEN fromipnr AND toipnr THEN
        dbms_output.put_line('TRUE');
        EXIT;
      ELSE
        not_nederlands := 'Y';         
      END IF;
    END LOOP;

    IF not_nederlands = 'Y' THEN
      RAISE niet_nederlands;
    END IF;

    EXCEPTION WHEN niet_nederlands THEN
    raise_application_error(-20000, 'IP address ' || :new.ipadres || ' is not dutch.');       
  END;
/

My problem is that the correct IP range for the inserted IP address is somehwere in the middle of the DUTCHIP table. So when it checks if the IP address is in the first IP range, it will put not_nederlands on 'Y', instead of continuing to look if the IP address is in any range. How can I fix this?

Upvotes: 0

Views: 116

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Instead of iterating through a cursor, perhaps the database could do the work:

CREATE OR REPLACE TRIGGER REACTIE_CHECK
  BEFORE INSERT OR UPDATE
  ON REACTIE
  FOR EACH ROW
DECLARE
  nCount          NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO nCount
    FROM DUTCHIP i
    WHERE IPTOINT(:new.IPADRES) BETWEEN IPTOINT(i.FROMIP)
                                    AND IPTOINT(i.TOIP);

  IF nCount = 0 THEN
    raise_application_error(-20000, 'IP address ' || :new.IPADRES ||
                                    ' is not dutch.');
  END IF;
END REACTIE_CHECK;

Share and enjoy.

Upvotes: 2

Related Questions