How can i implement my Function in my trigger?

Question

Hello everyone i am using an iban checker in PLSQL to check if the user filled in a right IBAN.

I have looked up the function on internet it compiles now i need to put in a trigger but it gives an error i have tried this

when the user enters an iban nummer on the APEX appication and the ibannummers is wrong the trigger needs to do it work

This is my function:

create or replace FUNCTION CheckIBANNUMMBER(
      pIBAN IN VARCHAR2
    ) RETURN VARCHAR IS
      lResult     INTEGER;
      ResulText   Varchar(250);
      IBAN        VARCHAR2(256);
      IBAN_Digits VARCHAR2(256);
      l_mod       NUMBER;
      lTmp        VARCHAR2(8);
      lSCnt       INTEGER := 5;
      i           INTEGER := 1;

---

      FUNCTION fn_GetIBANDigits RETURN VARCHAR2 AS
        lChar   VARCHAR2(1);
        lNumber INTEGER;
        lString VARCHAR2(255);
      BEGIN
        FOR i IN 1..LENGTH(IBAN) LOOP
          lChar := SUBSTR(IBAN, i, 1);
          BEGIN
            lNumber := ASCII(lChar);
            IF lNumber > 47 AND lNumber < 58 THEN
              -- It's number 0 ... 9
              lString := lString || TO_CHAR(lNumber - 48);
            ELSE
              lString := lString || TO_CHAR(lNumber - 55);
            END IF;
          END;
        END LOOP;
        RETURN lString;
      END fn_GetIBANDigits;

---

     BEGIN
      IBAN := SUBSTR(pIBAN, 5) || SUBSTR(pIBAN, 1, 4);

      IBAN_Digits := fn_GetIBANDigits;

      LOOP
        lTmp := SUBSTR(IBAN_Digits, i, lSCnt);
        EXIT WHEN lTmp IS NULL;

        IF l_mod IS NULL THEN
          l_mod := MOD( TO_NUMBER(lTmp), 97);
        ELSE
          l_mod := MOD(TO_NUMBER( TO_CHAR(l_mod) || lTmp), 97);
        END IF;

        i := i + lSCnt;
      END LOOP;

      IF l_mod = 1 THEN
        lResult := 1;
        ResulText := 'The given IBAN is correct';
      ELSE
        lResult := 0;
        ResulText := 'The given IBAN is incorrect';
      END IF;

      RETURN(ResulText);
    END CheckIBANNUMMBER;

and this is the trigger i made

create or replace trigger "T_CHECKIBAN"
BEFORE
insert or update on "PAYMENTS"
for each row
begin
if CHECKIBAN(new.IBAN) = 0 then raise_application_error(-20500,'Wrong IBANNUMMBER');
end;

Answer 1

Use a virtual column and a CHECK constraint then your validity checks are in the DDL statement for the table rather than being in another castle (i.e. a trigger):

create or replace FUNCTION CheckIBANNumber(
  pIBAN IN VARCHAR2
) RETURN NUMBER DETERMINISTIC
IS
  lResult     INTEGER;
  ResulText   Varchar(250);
  IBAN        VARCHAR2(256);
  IBAN_Digits VARCHAR2(256);
  l_mod       NUMBER;
  lTmp        VARCHAR2(8);
  lSCnt       INTEGER := 5;
  i           INTEGER := 1;

---

  FUNCTION fn_GetIBANDigits RETURN VARCHAR2 AS
    lChar   VARCHAR2(1);
    lNumber INTEGER;
    lString VARCHAR2(255);
  BEGIN
    FOR i IN 1..LENGTH(IBAN) LOOP
      lChar := SUBSTR(IBAN, i, 1);
      BEGIN
        lNumber := ASCII(lChar);
        IF lChar BETWEEN '0' AND '9' THEN
          -- It's number 0 ... 9
          lString := lString || lChar;
        ELSIF lChar BETWEEN 'A' AND 'Z' THEN 
          lString := lString || TO_CHAR(ASCII(lChar) - 55);
        END IF;
      END;
    END LOOP;
    RETURN lString;
  END fn_GetIBANDigits;

---

BEGIN
  IBAN := SUBSTR(pIBAN, 5) || SUBSTR(pIBAN, 1, 4);

  IBAN_Digits := fn_GetIBANDigits;

  LOOP
    lTmp := SUBSTR(IBAN_Digits, i, lSCnt);
    EXIT WHEN lTmp IS NULL;

    IF l_mod IS NULL THEN
      l_mod := MOD( TO_NUMBER(lTmp), 97);
    ELSE
      l_mod := MOD(TO_NUMBER( TO_CHAR(l_mod) || lTmp), 97);
    END IF;

    i := i + lSCnt;
  END LOOP;

  IF l_mod = 1 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END CheckIBANNumber;
/

CREATE TABLE test_data (
  Country      VARCHAR2(20),
  IBAN         VARCHAR2(50),
  IBANValidity NUMBER(1) GENERATED ALWAYS AS ( CheckIBANNumber( iban ) ),
  CONSTRAINT IsValidIBAN CHECK ( IBANValidity = 1 )
);

Then (with test data from wikipedia):

INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Belgium', 'BE71 0961 2345 6769' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'France', 'FR76 3000 6000 0112 3456 7890 189' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Germany', 'DE91 1000 0000 0123 4567 89' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Greece', 'GR96 0810 0010 0000 0123 4567 890' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Romania', 'RO09 BCYP 0000 0012 3456 7890' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Saudi Arabia', 'SA44 2000 0001 2345 6789 1234' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Spain', 'ES79 2100 0813 6101 2345 6789' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'Switzerland', 'CH56 0483 5012 3456 7800 9' );
INSERT INTO test_data ( Country, IBAN ) VALUES ( 'United Kingdom', 'GB98 MIDL 0700 9312 3456 78' );

succeeds, and:

 SELECT * FROM test_data;

outputs:

COUNTRY        | IBAN                              | IBANVALIDITY
:------------- | :-------------------------------- | -----------:
Belgium        | BE71 0961 2345 6769               |            1
France         | FR76 3000 6000 0112 3456 7890 189 |            1
Germany        | DE91 1000 0000 0123 4567 89       |            1
Greece         | GR96 0810 0010 0000 0123 4567 890 |            1
Romania        | RO09 BCYP 0000 0012 3456 7890     |            1
Saudi Arabia   | SA44 2000 0001 2345 6789 1234     |            1
Spain          | ES79 2100 0813 6101 2345 6789     |            1
Switzerland    | CH56 0483 5012 3456 7800 9        |            1
United Kingdom | GB98 MIDL 0700 9312 3456 78       |            1

db<>fiddle here

Answer 2

Your code, edited:

CREATE OR REPLACE TRIGGER T_CHECKIBAN
    BEFORE INSERT OR UPDATE
    ON PAYMENTS
    FOR EACH ROW
BEGIN
    IF CheckIBANNUMMBER(:new.IBAN) = 0 /* new --> :new, CHECKIBAN -- > CheckIBANNUMMBER*/
    THEN
        raise_application_error(-20500, 'Wrong IBANNUMMBER');
    END IF; /* added END IF */
END;

I removed double quotes, because you only need them if you want to use lower case characters.

Also notice that your function gives ResulText, while it seems that it should return lResult instead; and even, you are using a varchar2 return value to handle a number.

If you only want this function to check whether the IBAN is correct or not, you should edit your function as follows (ResulText is unuseful here):

create or replace FUNCTION CheckIBANNUMMBER(
      pIBAN IN VARCHAR2
    ) RETURN number IS /* return a number */
    ...
      RETURN(lResult); /* return 0/1 */
    END CheckIBANNUMMBER;