| when item validate trg [message #640467] |
Wed, 29 July 2015 02:08  |
 |
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
Hallo all,
I am trying to validate one field in a block. I worte a procedure and call in the respective Trigger in that item.
anz number;
begin
select count (*) into anz
from table1
where id = :d_block1.id;
if anz > 0 and :d_block1.id is not null then
message ('invalid ID !');
end if;
end;
in the block i do have, 5 items field, and one is non-database item which property set to database item = No and Required = No.
And the problem is, when i want to check validity in the respective field, (when user enter some other id in the ID field and press F11) it did not show any message and get error: "FRM-40301: Query caused no records to be retrieved, Re-enter."
Thats true, but i want to display my own message, if invalid ID is entered.
Any suggestion please? 
Regards,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: when item validate trg [message #640484 is a reply to message #640483] |
Wed, 29 July 2015 06:13  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Probably it does show the message but it's getting immediately overwritten by the FRM error.
You need to move the execute_query into the procedure so it's only run if the cursor finds a matching record.
|
|
|
|
|
|
| Re: when item validate trg [message #640487 is a reply to message #640486] |
Wed, 29 July 2015 06:54 |
|
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
Hallo,
I have changed some in my code, I have created on_message Trigger in form-Level to trap the error-40301, and it Shows the message, when i give the wrong ID and press F11, but when I give the right ID, and press F11, it Shows the same error as No Record Found and donT' execute query. :/ And my key_exeqry Trigger is as previous, did not Change any.
Any idea please?
Declare
msg_code Number := message_code;
msg Varchar2(200) := substr(' ' ||MESSAGE_TYPE||'-' || TO_CHAR(Msg_code) ||': '||Message_Text,1,200);
Begin
if msg_code = 40301 then
message('NO Records Found !');
else
message(msg, no_acknowledge);
end if;
End;
|
|
|
|
| Re: when item validate trg [message #640488 is a reply to message #640486] |
Wed, 29 July 2015 07:30 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sanodani wrote on Wed, 29 July 2015 12:24Hi CM,
Thanking you again..
Well, I have removed my procedure and written code direct in the key-exeqry, but it gives the same error still.
my current code at d_block:
declare
var number;
begin
select count (*) into var
from table1
where id = :d_block1.id;
if anz > 0 and :d_block1.id is not null then
message ('invalid ID !');
end if;
end;
execute_query;
Any idea please?
REgards,
That code runs execute_query regardless of whether the cursor found anything. If you know the ID is invalid you shouldn't be calling it.
|
|
|
|
|
|
|
|
| Re: when item validate trg [message #640493 is a reply to message #640492] |
Wed, 29 July 2015 08:25 |
|
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
Hallo CM,
I am sorry, i did not understand, what you mean :/ Can you please let me know what, you actually mean?
Well, I tried to put my execute query at first and include on-message Trigger at form-Level,
it Shows the message when id is wrong, but but also Show the message when id is correct and then after execute the query as per that id.
now, I made so:
Key-EXEQRY:
execute_query;
declare
var number;
begin
select count (*) into var
from table1
where id = :d_block1.id;
if anz > 0 and :d_block1.id is not null then
message ('invalid ID !');
end if;
end;
execute_query;
and ON-MESSAGE:
Declare
msg_code Number := message_code;
msg Varchar2(200) := substr(' ' ||MESSAGE_TYPE||'-' || TO_CHAR(Msg_code) ||': '||Message_Text,1,200);
Begin
if msg_code = 40301 then
message('NO Records Found !');
else
message(msg, no_acknowledge);
end if;
End;
Can you please tell me, where i have made mistake?
thanking you alot,
regards,
|
|
|
|
| Re: when item validate trg [message #640494 is a reply to message #640484] |
Wed, 29 July 2015 08:43 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Wed, 29 July 2015 12:13
You need to move the execute_query into the procedure so it's only run if the cursor finds a matching record.
That sentence would imply that the call to execute_query would need to be somewhere inside an IF/ELSE construct. You already have an IF construct that checks if there are matching records so I'm baffled as how you're struggling here.
Do I really need to tell you that you've placed the call to execute_query so that it'll always be run?
So move the call to execute_query as I suggested above and get rid of the on-message trigger - you don't need it.
|
|
|
|
|
|
| Re: when item validate trg [message #640541 is a reply to message #640532] |
Thu, 30 July 2015 09:10 |
 |
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
|
Senior Member |
|
|
I'm just confused as to why you would write a WVI trigger to validate the value entered when Oracle Forms performs the same basic validation when you execute your query. If all you wanted to do was change the message displayed when Forms didn't find a value, you could have done this in the On-Error trigger. Now, you are making 2 trips to the database for each valid entry instead of 1 trip to the database.
Just my thoughts...
Craig...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
