Forum Stats

  • 3,770,702 Users
  • 2,253,155 Discussions
  • 7,875,552 Comments

Discussions

creating error message in enter query mode

622131
622131 Member Posts: 40
edited Sep 2, 2008 2:49AM in Forms
Hello experts,
I want to create an error message in a enter query mode.Suppose i have a field dob and the data filled
in the database in this fasion 01-jan-2008.

Suppose in the enter query mode some one enter the value 01/01/2008.It is not a suitable input for the system .For this error i want to create a error message.

I have written this code

if :system.mode = 'ENTER-QUERY' then
if error_type='FRM' and error_code=40358 then
message('please enter date as 01-jan-2008');
end if;
end if;

The trigger i have used is KEY-EXEQRY.but it is not showing any error when ever i give a input like 01/01/2008.


can you please suggest what's wrong in the coding.

Regards
Anutosh
Tagged:

Best Answer

  • 611407
    611407 Member Posts: 418
    edited Sep 1, 2008 11:27AM Accepted Answer
    Yes, on-error trigger.
    DECLARE
      err_code NUMBER := ERROR_CODE;
      err_type VARCHAR2(3) := ERROR_TYPE;
      msg VARCHAR2(2000) := ERROR_TEXT;
    BEGIN
      IF err_code = 40358 AND err_type = 'FRM' THEN
        message('please enter date as 01-jan-2008');
      ELSE
        message(err_type||'-'||to_char(err_code)||': '||msg);
      END IF;
    
      RAISE FORM_TRIGGER_FAILURE;
    END;
    Makes sure the "Fire in Enter-Query Mode" option is set to Yes (Property Pallet).

Answers

  • Fabrizio Delli Priscoli
    Fabrizio Delli Priscoli Member Posts: 1,161 Silver Badge
    Try to put your code in the pre-query trigger at level block.
    Hope it helps you,
    Fabrizio
  • 611407
    611407 Member Posts: 418
    edited Sep 1, 2008 9:14AM
    If the table column is a date and the form item is a date then the format of the date does not matter - it will always be compared as a date. You can specify the date format by using the Format Mask property so the item will report an error if the user enters an incorrect format. You can set the item as a date by choosing that from the Data Type property in the Property Pallet.

    Date is always the same whatever the format you use:
    SQL> SELECT Dump(To_Date('01092008', 'DDMMRRRR')) a, Dump(To_Date('2008-Sep-01', 'YYYY-Mon-DD')) b
      2  FROM dual
      3  /
    
    A                               B
    ------------------------------- -------------------------------
    Typ=13 Len=8: 216,7,9,1,0,0,0,0 Typ=13 Len=8: 216,7,9,1,0,0,0,0
  • 622131
    622131 Member Posts: 40
    Hello u3,
    Thank you for your ideas but i want to generate an error message if user entered in this fashion.
    Can you please tell me whether the code and the associated trigger is right for the application.

    if :system.mode = 'ENTER-QUERY' then
    if error_type='FRM' and error_code=40358 then
    message('please enter date as 01-jan-2008');
    end if;
    end if;

    trigger:key-EXEQRY


    Hi Fabrizio
    I have tried your method but unfortunately it did not worked.

    Regards
    Anutosh
  • You have to code it in the ON-ERROR trigger. Check for :TRIGGER variables to identify the event and item generating the error. Beware that You still have to display error for other cases, use an else to show the standar error_type, code and messate.
    Cuauhtemoc Amox
  • 611407
    611407 Member Posts: 418
    edited Sep 1, 2008 11:27AM Accepted Answer
    Yes, on-error trigger.
    DECLARE
      err_code NUMBER := ERROR_CODE;
      err_type VARCHAR2(3) := ERROR_TYPE;
      msg VARCHAR2(2000) := ERROR_TEXT;
    BEGIN
      IF err_code = 40358 AND err_type = 'FRM' THEN
        message('please enter date as 01-jan-2008');
      ELSE
        message(err_type||'-'||to_char(err_code)||': '||msg);
      END IF;
    
      RAISE FORM_TRIGGER_FAILURE;
    END;
    Makes sure the "Fire in Enter-Query Mode" option is set to Yes (Property Pallet).
  • 622131
    622131 Member Posts: 40
    Thanks u3 and am0x,

    Thanks for your answer.Now i want to implement the same thing for another scnerio.
    Suppose i have entered a certain date but there is no related data in the database for .I am concedering it as a error and want to show a message.

    In the trigger ON-ERROR i have written this code.


    DECLARE
    err_code NUMBER := ERROR_CODE;
    err_type VARCHAR2(3) := ERROR_TYPE;
    msg VARCHAR2(2000) := ERROR_TEXT;
    BEGIN
    IF err_code = 40358 AND err_type = 'FRM' THEN
    message('please enter date as 01-jan-2008');
    END IF;

    IF err_code = 40301 AND err_type = 'FRM' THEN
    message('there is no related data in the database ,please enter the value again');

    END IF;

    RAISE FORM_TRIGGER_FAILURE;
    END;

    But it is not showing any message if there is no related data in the database can you please help.
This discussion has been closed.