Home » Developer & Programmer » Forms » Execute Query
icon5.gif  Execute Query [message #382480] Thu, 22 January 2009 09:55 Go to next message
Kevin58
Messages: 79
Registered: January 2009
Member
Hi All,

I am trying to find the number of records returned from the
execute_query built in function.

I tried using count_query. However, that cancels the query and
shows the number of records available in the table unfiltered.

Any ideas much appreciated.
Thanks,

Kevin
Re: Execute Query [message #382511 is a reply to message #382480] Thu, 22 January 2009 15:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you use it?

For testing purposes, I have created a sample form based on Scott's EMP table. I have written the KEY-EXEQRY trigger:
count_query;
execute_query;
After the form has started, I have entered query mode, entered "10" into the DEPTNO item and pressed EXECUTE QUERY. It displayed a message saying "FRM-40355: Query will retrieve 3 records" and fetched all three employees (Clark, King and Miller at the moment in my database) who work in department 10.

As far as I'm concerned, COUNT_QUERY works as expected. So, once again: how did you use it so that it didn't work?
Re: Execute Query [message #382642 is a reply to message #382480] Fri, 23 January 2009 06:17 Go to previous messageGo to next message
Kevin58
Messages: 79
Registered: January 2009
Member
Hi, I'm using count_query after the execute_query.
It is used via a button's when-button-pressed trigger.

Here is my code:
do_key('execute_query');
count_query;
set_item_property('cancel_query', enabled, property_false);
set_item_property('enter_query', enabled, property_true);
last_record;
first_record;

The last and first records are to show the user how many records are available.

My intention with the count_query is to tell whether or not the query found any records. Then based on that result I would like to enable or disable the buttons.

Trying to imitate the default toolbar functionality using my own buttons.

When I execute the code above I get the response:
FRM-40355: Query will retrieve 91 records.

The form becomes blank like I didn't even put in the search criteria.

Should I not use the do_key() function?
Thanks,


Kevin
Re: Execute Query [message #382644 is a reply to message #382480] Fri, 23 January 2009 06:37 Go to previous messageGo to next message
Kevin58
Messages: 79
Registered: January 2009
Member
Oh. Confused I just re-read your post.
It appears that I needed to put the count_query ahead of the execute_query function.

It just seemed a little backwards to ask for a count before retrieving any results with execute_query.

How can I tell if the search has any results?
Is there a system or block status to check?
Thanks Smile,


Kevin
Re: Execute Query [message #382646 is a reply to message #382644] Fri, 23 January 2009 07:01 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
How can I tell if the search has any results?

Simply, by viewing the screen. If *something* appears after executing a query, the search was successful (i.e. returned the result). Also, if the screen is empty, nothing was found using this search criteria.

Why do you need Oracle to explicitly tell you the result?
Re: Execute Query [message #382648 is a reply to message #382480] Fri, 23 January 2009 07:11 Go to previous messageGo to next message
Kevin58
Messages: 79
Registered: January 2009
Member
I am attempting to imitate the functionality of the default toolbar buttons.

The reason I would like to do this programmatically is to enable or disable the query buttons I have on the form.
Thanks,


Kevin
Re: Execute Query [message #382707 is a reply to message #382648] Fri, 23 January 2009 12:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you might try to combine ON-COUNT and KEY-EXEQRY triggers (at least, that's how I managed to get the result):
-- ON-COUNT trigger

count_query;
:global.cnt := get_block_property('emp', query_hits);
-- KEY-EXEQRY trigger

count_query;

if :global.cnt < 10 then
   execute_query;
else
   message('Too many rows; query will not be performed');
   null;
end if;
Re: Execute Query [message #383006 is a reply to message #382707] Mon, 26 January 2009 23:44 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Search this forum for 'count_query'. Use the command in the 'pre-query' trigger as is recommended by the reference manual.

David
Previous Topic: Oracle Developer Suite 10g cannot be installed into existing Oracle Home
Next Topic: JSP and Oracle forms and Jsp page from orcle form (merged)
Goto Forum:
  


Current Time: Sat Dec 10 22:32:11 CST 2016

Total time taken to generate the page: 0.06904 seconds