OraFAQ Forum: Forms » Execute Query

Home » Developer & Programmer » Forms » Execute Query

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

Execute Query [message #382480]

Thu, 22 January 2009 09:55

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

Report message to a moderator

Re: Execute Query [message #382511 is a reply to message #382480]

Thu, 22 January 2009 15:31

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Execute Query [message #382642 is a reply to message #382480]

Fri, 23 January 2009 06:17

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

Report message to a moderator

Re: Execute Query [message #382644 is a reply to message #382480]

Fri, 23 January 2009 06:37

Kevin58
Messages: 79
Registered: January 2009

Member

Oh.

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

Report message to a moderator

Re: Execute Query [message #382646 is a reply to message #382644]

Fri, 23 January 2009 07:01

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Execute Query [message #382648 is a reply to message #382480]

Fri, 23 January 2009 07:11

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

Report message to a moderator

Re: Execute Query [message #382707 is a reply to message #382648]

Fri, 23 January 2009 12:48

Littlefoot
Messages: 21823
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;

Report message to a moderator

Re: Execute Query [message #383006 is a reply to message #382707]

Mon, 26 January 2009 23:44

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 18:11:52 CST 2025