Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
creating error message in enter query mode
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
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
Best 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
-
Try to put your code in the pre-query trigger at level block.
Hope it helps you,
Fabrizio -
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
-
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.
-
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). -
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.