OraFAQ Forum: Forms » how to execute the last query fired on form when somene press CANCEL button

Home » Developer & Programmer » Forms » how to execute the last query fired on form when somene press CANCEL button

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to execute the last query fired on form when somene press CANCEL button [message #145983]

Tue, 08 November 2005 00:33

sanaafridi
Messages: 50
Registered: August 2005
Location: pakistan

Member

hi all

i am writing a code on form "CANCEL" button which mean to cancel the current user action .now if the last action was execute_query so when user click the cancel button so my logic should capture the last query fired on form through
":SYSTEM.LAST_QUERY" and then execute the last query through
EXECUTE_QUERY but my problem is that how i execute my last_query through execute_query.

can any one help?

i tried to look it form help but i didn't

regards

sana

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146135 is a reply to message #145983]

Tue, 08 November 2005 19:16

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

In the Pre-Query trigger get the system.last_query text, find the 'where' word in the string and use the rest of the string in a set_block_property ('blk',default_where) command.

Have a look at the sample code in the reference manual under SYSTEM.LAST_QUERY

David

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146155 is a reply to message #146135]

Tue, 08 November 2005 22:27

sanaafridi
Messages: 50
Registered: August 2005
Location: pakistan

Member

Hello sir,

sir, you exaclty pointed out what i am trying to do but i have some questions.

1) i have studied the set_block_property ('blk',default_where) but it only used for overiding the default WHERE block property which mean if my last_query contain 'WHERE' clause so then it set _block.... would be usefuel but my block do not conatin where clause in the last query so then how can i use "set_block_property ('blk',default_where)"
plzz guide

2) i try to find the reference mannual on net .i found one with "pl/sql user guide and Reference" but it do not conatin what u suggested

3)u can't catch the last_query in 'PRE-QUERY' trigger as at this stage the execute_query will not fire.

thanks for your tume sir

regards

sana

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146156 is a reply to message #146155]

Tue, 08 November 2005 22:49

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

1) Don't define the where clause in the block properties. Define a default_default_where in your pre-query. For example: varchar2(100) := 'where 1=1'

2) There are links to documentation in the 'sticky' threads at the beginning of this forum.

Go to http://www.oracle.com/technology/documentation/forms.html. Find 'Form Builder Reference'. Download it and search for 'SYSTEM.LAST_QUERY'.

3) If the last_query is NULL then set default_where to default_default_where. If it isn't NULL then do an INSTR to find the 'where' and use substr to get the 'where xxx' and put it into set_block_command. For example (very rough):

declare
   default_default_where   varchar2 (100) := 'where 1=1';
   where_where             number (10);
begin
   where_where  := instr (upper (:system.last_query), upper ('WHERE') );

   if where_where = 0 then
      set_block_property ('myblk', default_where, default_default_where);
   else
      set_block_property ('myblk', default_where,
                          substr (:system.last_query, where_where) );
   end if;
end;

David

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146385 is a reply to message #145983]

Thu, 10 November 2005 02:20

sanaafridi
Messages: 50
Registered: August 2005
Location: pakistan

Member

good day, sir

1) In the IF--part of your code if the last query don't have the 'WHERE' clause then how can i hard code the 'WHERE 1=1' or anything here as i can't predict it how the last query was, and if it don't have the where clause then how can i execute my last query.see the followinh exmp

Exmp.

i have created a single block of DEPT table and there are ADD and CANCEL and EXECUTE button.

now i press EXECUTE button so all records displayed then i prees ADD to add new record so create_record built_in executed behind the ADD button but then user change his mind and press CANCEL button now i have written the code on CANCEL button, in which i capture the last_query

now the last query don't have WHERE clause as it a simple 'seelct deptno,dname,loc from emp' so how can i execute
this last query on that block

regards
sana

Upd-mod: remove blank lines

[Updated on: Thu, 10 November 2005 21:54] by Moderator

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146515 is a reply to message #146385]

Thu, 10 November 2005 21:59

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

There is no difference between 'select deptno,dname,loc from emp' and 'select deptno,dname,loc from emp where 1=1'.

The reason for setting the default_where to 'where 1=1' is I have found that you can't 'NULL out' the default_where, you have to put something in it.

By the way, why don't you just teach your users to press the clear_record button and then press the execute_query button again.

David

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146516 is a reply to message #146515]

Thu, 10 November 2005 22:01

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

In the Execute_Query trigger, you could store the current query in either a global variable or in a variable declared in a package specification or use a pair of package functions to store it and retrieve it.

David

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146555 is a reply to message #145983]

Fri, 11 November 2005 01:00

sanaafridi
Messages: 50
Registered: August 2005
Location: pakistan

Member

good day, sir

thank you very much sir,i got it now what u suggested

but u said that catch the last query in EXECUTE_QUERY which is quite valid but then how can i execute that query through EXEWCUTE-QUERY built in.

i look at the form hlep but don't found specific info.

regards sir

sana

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146556 is a reply to message #145983]

Fri, 11 November 2005 01:04

sanaafridi
Messages: 50
Registered: August 2005
Location: pakistan

Member

beg your parden , sir

it is EXECUTE_QUERY

regards

sana

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #146748 is a reply to message #146555]

Sun, 13 November 2005 16:40

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

My apologies, when I say the execute-query trigger, the real name is 'Key-EXEQRY'. Sloppy naming on my part.

In your Key-EXEQRY trigger, store the current query and then issue the execute_query command.

David

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580382 is a reply to message #146516]

Sat, 23 March 2013 13:13

D_ORA
Messages: 32
Registered: November 2012
Location: UK

Member

What is the process to store the Current Query in Global variable at KEY-EXEQRY Trigger..Can you please explain it...Thanks for your urgent reply.

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580451 is a reply to message #580382]

Sun, 24 March 2013 14:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

:global.last_query := :system.last_query;

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580454 is a reply to message #580451]

Sun, 24 March 2013 16:18

D_ORA
Messages: 32
Registered: November 2012
Location: UK

Member

FIRST OF ALL BUNDLE OF THANKS Littlefoot ..FOR YOUR SHARP REPLY..

SIR ,
I HAVE TWO BLOCKS B1 AND B2 BOTH I KEPT WITH WHERE CLAUSE CONDITION AT FORM LEVEL AS WELL.
BUT WHEN I AM RUNING THE FORM ,I AM GETTING THE FRM-41009 FUNCTION KEY NOT ALLOWED PRESS F8 FOR KEYS LIST ..I ALREADY TRAPPED THE FRM-41009 ERROR BY ON-ERROR KEY TRIGGER...INFACT MY FORM IS OPENING IN QUERY-MODE AND I AM ENTERING THE JOB_CODE AND PRESSING THE EXECUTE BUTTON TO RUN THE QUERY..IT IS DISPLAYING THE DATA PROPERY ..BUT WHEN I AM PRESSING TWO TIMES F11 TO BRINING THE LAST EXECUTED JOB_CODE EVEN IT IS DISPLAYING THE LAST EXECUTED JOB_CODE IN THE JOB_CODE_FIELD AT THAT TIME THE FORM AGAIN GOING TO QUERY_MODE,BUT WHEN I AM GOING TO EXECUTE AGAIN THE LAST JOB_CODE IT IS NOT FETCHING THE DATA.AND FORM STILL IN THE QUERY_MODE AND PRESSING THE EXECUTE BUTTON NO AFFECTS...
ANY HELP IN THIS REGARD WILL BE APPRECIATED.

THANKS IN ADVANCE FOR YOUR SHARP REPLY.

[Updated on: Sun, 24 March 2013 16:20]

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580470 is a reply to message #580454]

Mon, 25 March 2013 02:26

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

BOTH I KEPT WITH WHERE CLAUSE CONDITION AT FORM LEVEL AS WELL

What does that mean? Do you, perhaps, set WHERE clause dynamically (i.e. use SET_BLOCK_PROPERTY with ONETIME_WHERE or DEFAULT_WHERE)?

Quote:

BUT WHEN I AM GOING TO EXECUTE AGAIN THE LAST JOB_CODE IT IS NOT FETCHING THE DATA.AND FORM STILL IN THE QUERY_MODE AND PRESSING THE EXECUTE BUTTON NO AFFECTS

If my previous question (use of SET_BLOCK_PROPERTY) means "yes, you do that" - why do you do it? How come default Forms querying process doesn't do its job properly? Try to remove code you wrote (you don't have to delete it - just comment it out) and run the form again; it *should* be OK.

Another option is to run a form in debug mode; it will guide you through all program units, line by line, so - you'll probably find out what's going on.

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580492 is a reply to message #580470]

Mon, 25 March 2013 07:34

D_ORA
Messages: 32
Registered: November 2012
Location: UK

Member

Sir I set the Global variable in WNFI trigger

:GLOBAL.default_where_b1:= 'where jbc_comp_code = :b0.c_comp_code';

And I have set the where clause at Block level as well which I am taking the default where clause with some data condition ... 'where jbc_comp_code = :b0.c_comp_code'
I have PRE-QUERY Trigger where I dynamically set the where clause of Block B1 as well for the purpose of comparing the last_query and default query..

DECLARE
where_where number (10);
default_default_where_b1 varchar2 (4000) := 'where jbc_comp_code = :b0.c_comp_code';
BEGIN

where_where := instr (upper (:system.last_query), upper ('WHERE') );
if default_default_where_b1 = substr (:system.last_query, where_where) then
set_block_property ('B1', default_where, default_default_where_b1);
else

set_block_property ('B1', default_where, substr (:system.last_query, where_where) );

END IF;

In the KEY-EXEQRY trigger I set
:GLOBAL.default_where_b1:=:system.last_query;
execute_query;

But still the last executed job_code is not fetching the data it is showing in the job_code field ..but execute query is not returning any data.

Thanks a lot for your sharp reply.

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580498 is a reply to message #580492]

Mon, 25 March 2013 08:22

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

So at the end of the pre-query trigger display the result of get_block_property ('B1', default_where) in a message and check that it's what you think it is.

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580501 is a reply to message #580498]

Mon, 25 March 2013 08:39

D_ORA
Messages: 32
Registered: November 2012
Location: UK

Member

Hi Sir,
GET_BLOCK_PROPERTY('B1',default_where)
is showing the last_query ...but not executing it..

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580502 is a reply to message #580498]

Mon, 25 March 2013 08:46

D_ORA
Messages: 32
Registered: November 2012
Location: UK

Member

I have the following code in KEY-ENTQRY TRIGGER

IF (:B10.C_CALL_BY = '5') THEN
message('Calling from Block B5');
RAISE FORM_TRIGGER_FAILURE ;
ELSE

clear_block(no_validate);
go_block('B2');
clear_block(no_validate);
go_block('B1');
clear_block(no_validate);

ENTER_QUERY;

Next_block;

EXECUTE_QUERY;

Previous_block;

END IF;

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580560 is a reply to message #580502]

Mon, 25 March 2013 16:45

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This code won't do anything you put after ENTER_QUERY; Form simply waits for you to enter search criteria and execute query (manually - you need to push the button or press the key).

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580564 is a reply to message #580560]

Mon, 25 March 2013 17:40

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

And if it did run the code after enter-query you would get an error since you can't change block in enter-query mode.
Which I suspect is part of the problem.
You can't go into enter-query in one block and then try and do execute query in another block - that makes no sense.
So why are you trying to do that?

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580566 is a reply to message #580564]

Mon, 25 March 2013 19:24

D_ORA
Messages: 32
Registered: November 2012
Location: UK

Member

Sir Infact I have set my only two fields for enter query mode when I am Pressing F11 my Job _Code and Invoice no are coming in the enter_query mode again by Pressing it goes to show the last executed job code or invoice no which I executed last time...When I am not using the ENTER_QUERY in KEY-ENTQRY then it working fine..infact I need the execute_query over there because I am transferring the control for NEXT_BLOCK over there I need that Execute_Query..
I appreciated your useful advices.

Report message to a moderator

Re: how to execute the last query fired on form when somene press CANCEL button [message #580608 is a reply to message #580566]

Tue, 26 March 2013 02:31

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

When I am not using the ENTER_QUERY in KEY-ENTQRY then it working fine

So - don't use it!

Anyway, on a second thought: I'd say that you don't need that trigger at all. Here's why: if you created a master-detail form (that IS your form, right?) using Data Block Wizard, Forms would create all necessary triggers and procedures for you. Once you run such a form, it would work perfectly well.

There's no need to code "enter query" because Forms knows how to do that. It also clears a block for you (therefore, no need for CLEAR_BLOCK at all).

I'm not sure what you meant by raising an error if :B10.C_CALL_BY = '5'.

Therefore, perhaps you should follow the wizard and make your form work. Let Forms do as much job as possible. Then, if necessary, make the form fancy. You'll probably spoil what Forms did, but that's OK; hopefully, you'll learn something from it.

Report message to a moderator

Previous Topic:	How to call a canvas from menu
Next Topic:	call reports from forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 09 16:00:55 CDT 2025