creating error message in enter query mode

622131

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

611407

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).

Fabrizio Delli Priscoli

Try to put your code in the pre-query trigger at level block.
Hope it helps you,
Fabrizio

611407

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

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

Cuauhtemoc Amox

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.

611407

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

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.