Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Hungary
    Posts
    5

    Red face Unanswered: Message for user - help

    Hi!

    I'm in trouble with the "raise_application_error" message: I made a data validator trigger to give some feedback for users about their mistakes, so the trigger check the length of the data and if it isn't correct I want to promt the user, but when an exception coming, the row (in code below) with "raise_application_error (-20000, 'ROSSZ ADAT:' || tetel);" doesn't work, no message appear in Oracle Forms. It's possible to show a table trigger generated message on Oracle Froms? Can someone help me again?

    Thanks and regards: Tamas

    The trigger code is:

    CREATE OR REPLACE TRIGGER VALIDAL
    BEFORE
    INSERT OR UPDATE OF CENZURASZAM
    ON HP_HIANYPOTLAS
    FOR EACH ROW
    DECLARE
    rosszadat EXCEPTION;
    tetel VARCHAR2(15);
    BEGIN

    IF LENGTH(:NEW.cenzuraszam) != 12
    THEN
    tetel:='CENZÚRASZÁM';
    RAISE rosszadat;
    END IF;

    EXCEPTION
    WHEN rosszadat
    THEN
    raise_application_error (-20101, 'ROSSZ ADAT:' || tetel);
    ROLLBACK;
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Message for user - help

    Yes, you can trap and show the error in Forms. One way is to code an ON-ERROR trigger which traps the error and displays DBMS_ERROR_TEXT - look at the documentation for DBMS_ERROR_TEXT for an example.

    Actually, your example should not be done using a trigger, as it can be done more efficiently by a check constraint:

    ALTER TABLE HP_HIANYPOTLAS ADD CONSTRAINT chk_cenzuraszam
    CHECK (LENGTH(:NEW.cenzuraszam) = 12);

    Your form would see the DBMS_ERROR_TEXT:
    ORA-02290: check constraint (xxx.chk_cenzuraszam) violated

    It could then easily parse this and produce a more user-friendly message.

    As it stands, your trigger has a ROLLBACK that is redundant and will never happen anyway (since RAISE_APPLICATION_ERROR aborts the process), and could be simplified to:

    CREATE OR REPLACE TRIGGER VALIDAL
    BEFORE
    INSERT OR UPDATE OF CENZURASZAM
    ON HP_HIANYPOTLAS
    FOR EACH ROW
    BEGIN
    IF LENGTH(:NEW.cenzuraszam) != 12
    THEN
    raise_application_error (-20101, 'ROSSZ ADAT:' || 'CENZÚRASZÁM');
    END IF;
    END;

    (But as I said before, a CHECK constraint is better).

  3. #3
    Join Date
    Jan 2003
    Location
    Hungary
    Posts
    5

    Thumbs up

    Thank you the answer Andrew, another gap filled in my mind.

    I'll use the constraints too, and understand the using of DBMS_ERROR_TEXT.

    Is some significant difference in cycletime when I use 7 constraint or 1 trigger with 7 if-then to validate fields on form?

    Thanks again!
    Tamas

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by morvait
    Is some significant difference in cycletime when I use 7 constraint or 1 trigger with 7 if-then to validate fields on form?
    Tamas
    I haven't got any figures to back this up, but I believe any differences would be insignificant. However, constraints are generally more efficient than triggers - and remember: a table with 15 NOT NULL columns already has 15 check constraints: i.e. they are not an issue.
    The general rule is: always use a constraint rather than a trigger, if possible.

  5. #5
    Join Date
    Jan 2003
    Location
    Hungary
    Posts
    5
    Thank you Andrew i'll keep it in mind!

    Tamas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •