how can i display recordcount on a form

[bookouri]

I want to put a text box on my form that displays a total number of records from a select. for example:
select count(*) from emp where status='A'
select count(*) from emp where status='I'

I know it should be simple, I tried a new_form_instance event to assign the value of the select to a text box, but I havnt been able to get it to work.

thanks for any suggestions

 

[lewisp]

This kind of function in a form tends to hit performance, especially if your query will return a lot of records. However, you can do it one of two ways.

One way is to perform an EXECUTE_QUERY(ALL_RECORDS); then go to LAST_RECORD; and read the :SYSTEM.CURSOR_RECORD value.

Or you could perform a SELECT COUNT(*) in a PRE-QUERY, but you will have to use the where clause from the :SYSTEM.LAST_QUERY to make sure you get the right record count.

 

[bookouri]

thanks, luckily the data set is very small in this case, only a couple hundred rows.

I was trying to do it something like this:
declare
temp varchar2;
begin
select count(status) into temp from emp where status='A';
:text_box_on_form := temp;
end

but I get an exception error. Why doesnt something like this work? it seems like it should, but I dont do much with Oracle forms.

thanks again..

 

[lewisp]

Try

select count(status)
into :block.field
from emp
where status='A';

Do you get the error when you compile or when you run? VARCHAR2 needs to have a size defined, eg VARCHAR2(10). In any case, count(*) returns a number but this will be implicitly converted when you copy it to the VARCHAR2.

 

[bookouri]

thanks, problem solved.. worked like a charm.