Total records in block
Jap Boon Churn
I'm new to Oracle Developer and currently using Forms.
Can someone tell me how do I get the value for the number of records in
a data block including those that are not yet displayed until we scroll
down.
I have try to use this method :
last_record;
v_rowcount := Get_block_property('BlockName', Query_hits);
This method can be applied when I perform the above action using a
button and setting the mouse and keyboard navigate for the button to NO.
However, what I want is to be able to get the number of records in
current block after entering something in an item and move to another
item in the when_item_validate trigger. This is very important as I
cannot use the above method cos it needs to go to last record and this
will cause an error message.
Hope someone can answer by question as soon as possible.
Thanks a lot.
Samuel Leung
Jap Boon Churn wrote in message <37CA095D...@pc.jaring.my>...
Valeri Sorokine
and On-Message trigger (message_code is 40355) to get the number of records?
Hope this helps.
--
Valeri Sorokine
ProSoft, Russia, Moscow, Information Systems Division
Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640
E-mail: vsor...@dd.ru OR vsor...@prosoft.ru
http://www.dd.ru
Maurécio
if you're using Forms 5 or 6, you can define a summary of type "Count".
In the same block you want to count records (must have "Query All
Records" property set to true), you define a text item or display item
of data type number, with "Calculation Mode" property set to Summary ,
"Summary Function" set to Count, "Summarized Block" to this very block
and "Summarized Item" to a not null item in the block (any one will do).
Then, set the "Number of records displayed" to one and arrange it on
your canvas...
You don't need to worry about refreshing it, Forms will do it for you.
Welcome to Forms.
Hope to have been of any help.
Regards,
Maurício.
In article <37CA095D...@pc.jaring.my>,
Jap Boon Churn <bc...@pc.jaring.my> wrote:
> Hi,
>
> I'm new to Oracle Developer and currently using Forms.
>
> Can someone tell me how do I get the value for the number of records
in
> a data block including those that are not yet displayed until we
scroll
> down.
>
> I have try to use this method :
> last_record;
> v_rowcount := Get_block_property('BlockName', Query_hits);
>
> This method can be applied when I perform the above action using a
> button and setting the mouse and keyboard navigate for the button to
NO.
>
> However, what I want is to be able to get the number of records in
> current block after entering something in an item and move to another
> item in the when_item_validate trigger. This is very important as I
> cannot use the above method cos it needs to go to last record and this
> will cause an error message.
>
> Hope someone can answer by question as soon as possible.
>
> Thanks a lot.
>
>
--
Maurício B. Falleiros <m...@uniconsult.com.br>
Uniconsult Sistemas e Serviços
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Kirill Dylewski
block?
Valeri Sorokine
I don't think that a setting the "Query All Records" property to True is
a very fast solution...
The Forms counts all records easily and fast by Count_Query built-in.
Still offer the using Count_Query...
Hope this will help.
Kirill Dylewski wrote:
>
> How about query executing time if there are 5000...10000...20000 records in
> block?
--
Maurécio
bad way; dealing with on-message is not one favorite of mines, though.
If you have issued a count_query, then
"get_block_property('<block_name>', query_hits)" will give you the
number of record (in varechar2 format).
Regards,
Maurício.
In article <37CCED02...@dd.ru>,
--
Valeri Sorokine
>
> Yes, I surrender, the "thousands of record" make "query all records" a
> bad way; dealing with on-message is not one favorite of mines, though.
> If you have issued a count_query, then
> "get_block_property('<block_name>', query_hits)" will give you the
> number of record (in varechar2 format).
Yeah, you are quite right. We use something like this:
PROCEDURE On_Message_Trg IS
msgnum NUMBER := MESSAGE_CODE;
msgtxt VARCHAR2(80) := MESSAGE_TEXT;
msgtyp VARCHAR2(3) := MESSAGE_TYPE;
tmp_Cnt VARCHAR2(10);
tmp_Msg VARCHAR2(80);
...
BEGIN
IF msgnum = 40355 THEN
tmp_Cnt := Get_Block_Property(NAME_IN('SYSTEM.CURSOR_BLOCK'), QUERY_HITS);
IF tmp_Cnt != '0' THEN
... we make our message about number of records here ...
...
tmp_Msg := '...: '||tmp_Cnt;
...
Message(tmp_Msg);
END IF;
ELSE
Message(msgtyp||'-'||TO_CHAR(msgnum)||': '||msgtxt);
END IF;
EXCEPTION
...
END;
Hope this helps.
Best wishes,
Valeri
>
> Regards,
> MaurМcio.
>
> Valeri Sorokine <vsor...@dd.ru> wrote:
> > Yeah, especially in the block with complicated POST-QUERY trigger!
> >
> > I don't think that a setting the "Query All Records" property to True
> > is a very fast solution...
> >
> > The Forms counts all records easily and fast by Count_Query built-in.
> >
> > Still offer the using Count_Query...
> >
> > Hope this will help.
> >
> > Kirill Dylewski wrote:
> > >
> > > How about query executing time if there are 5000...10000...20000
> > > records in block?
<skip>
Maurécio
that's almost perfect, except that I'd rather not intercepting messages
in ON-MESSAGE trigger.
As long as the user has to press <count_query>, it's cleaner (I think)
to build a KEY-CQUERY (count-query key trigger) with:
declare qt_recs varchar2(10);
begin
count_query;
qt_recs := := Get_Block_Property(NAME_IN('SYSTEM.CURSOR_BLOCK'),
QUERY_HITS);
--
-- do messages here (even if zero records...user may want to know)
--
end;
Best regards,
Maurício.
In article <37CE1A42...@dd.ru>,
Valeri Sorokine <vsor...@dd.ru> wrote:
> > Maurício.
> >
> > Valeri Sorokine <vsor...@dd.ru> wrote:
> > > Yeah, especially in the block with complicated POST-QUERY trigger!
> > >
> > > I don't think that a setting the "Query All Records" property to
True
> > > is a very fast solution...
> > >
> > > The Forms counts all records easily and fast by Count_Query
built-in.
> > >
> > > Still offer the using Count_Query...
> > >
> > > Hope this will help.
> > >
> > > Kirill Dylewski wrote:
> > > >
> > > > How about query executing time if there are 5000...10000...20000
> > > > records in block?
>
> <skip>
>
> --
> Valeri Sorokine
> ProSoft, Russia, Moscow, Information Systems Division
> Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640
> E-mail: vsor...@dd.ru OR vsor...@prosoft.ru
> http://www.dd.ru
>
--
Valeri Sorokine
What are you going to do with "FRM-40355: Query will retrieve XXX records."?
You will have two messages, one yours about counted records and one 40355...
Anyway my point was about using the Count_Query built-in instead of setting
"Query All Records" property to True...
Yours truly,
Valeri
P.S. Our users don't press <count_query>, we use Count_Query built-in
in the KEY-EXEQRY trigger.
MaurИcio wrote:
>
> Hi,
>
> that's almost perfect, except that I'd rather not intercepting messages
> in ON-MESSAGE trigger.
> As long as the user has to press <count_query>, it's cleaner (I think)
> to build a KEY-CQUERY (count-query key trigger) with:
>
> declare qt_recs varchar2(10);
> begin
> count_query;
> qt_recs := := Get_Block_Property(NAME_IN('SYSTEM.CURSOR_BLOCK'),
> QUERY_HITS);
> --
> -- do messages here (even if zero records...user may want to know)
> --
> end;
>
> Best regards,
> MaurМcio.
>
> In article <37CE1A42...@dd.ru>,
> Valeri Sorokine <vsor...@dd.ru> wrote:
> > > MaurМcio.
Maurécio
yoou can hide messages of some level of severity by setting the variable
":system.message_level". Particularly that message is of level 5.
Another one of this level is "Transacion completed succesfully", for
example. All messages that can be trapped on ON-MESSAGE trigger are of
this level (error messages are of level 25 or greater, in general).
If you set the message level to 5, the formers will not be displayed:
begin
...
:system.message_level := 5;
count_query;
:system.message_level := 0;
qt_recs := get_block_property(...);
message("Your message about number of records");
...
end;
Just remember KEY-EXEQRY does not fire when you are in query-mode, by
default...
Hope these are useful.
Maurício.
In article <37D6799E...@dd.ru>,
Valeri Sorokine <vsor...@dd.ru> wrote:
> Hi, Maurécio,
>
> What are you going to do with "FRM-40355: Query will retrieve XXX
records."?
> You will have two messages, one yours about counted records and one
40355...
>
> Anyway my point was about using the Count_Query built-in instead of
setting
> "Query All Records" property to True...
>
> Yours truly,
> Valeri
>
> P.S. Our users don't press <count_query>, we use Count_Query built-in
> in the KEY-EXEQRY trigger.
>
> Maurécio wrote:
> >
> > Hi,
> >
> > that's almost perfect, except that I'd rather not intercepting
messages
> > in ON-MESSAGE trigger.
> > As long as the user has to press <count_query>, it's cleaner (I
think)
> > to build a KEY-CQUERY (count-query key trigger) with:
> >
> > declare qt_recs varchar2(10);
> > begin
> > count_query;
> > qt_recs := := Get_Block_Property(NAME_IN('SYSTEM.CURSOR_BLOCK'),
> > QUERY_HITS);
> > --
> > -- do messages here (even if zero records...user may want to know)
> > --
> > end;
> >
> > Best regards,
> > Maurício.
> >
> > In article <37CE1A42...@dd.ru>,
> > Valeri Sorokine <vsor...@dd.ru> wrote:
> > > > Maurício.
> > > >
> > > > Valeri Sorokine <vsor...@dd.ru> wrote:
> > > > >
> > > > > Yeah, especially in the block with complicated POST-QUERY
trigger!
> > > > >
> > > > > I don't think that a setting the "Query All Records" property
to
> > > > > True is a very fast solution...
> > > > >
> > > > > The Forms counts all records easily and fast by Count_Query
> > > > > built-in.
> > > > >
> > > > > Still offer the using Count_Query...
> > > > >
> > > > > Hope this will help.
> > > > >
> > > > > Kirill Dylewski wrote:
> > > > > >
> > > > > > How about query executing time if there are
5000...10000...20000
> > > > > > records in block?
> > >
>
> <skip>
>
> --
> Valeri Sorokine
> ProSoft, Russia, Moscow, Information Systems Division
> Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640
> E-mail: vsor...@dd.ru OR vsor...@prosoft.ru
> http://www.dd.ru
>
--
Valeri Sorokine
MaurИcio wrote:
>
> Hi Valery,
>
> yoou can hide messages of some level of severity by setting the variable
> ":system.message_level". Particularly that message is of level 5.
> Another one of this level is "Transacion completed succesfully", for
> example. All messages that can be trapped on ON-MESSAGE trigger are of
> this level (error messages are of level 25 or greater, in general).
> If you set the message level to 5, the formers will not be displayed:
>
> begin
> ...
> :system.message_level := 5;
> count_query;
> :system.message_level := 0;
> qt_recs := get_block_property(...);
> message("Your message about number of records");
> ...
> end;
>
> Just remember KEY-EXEQRY does not fire when you are in query-mode, by
> default...
I don't know why but KEY-EXEQRY always fires when I use Execute_Query :)
We need to show a message about the number of queried records after each
query in each block. It is why we use the method with ON-MESSAGE trigger.
In other case I will use your solution.
>
> Hope these are useful.
Sure all of those are useful! Thank you!
I think your solution is quite perfect now.
Good Luck!
Valeri
> MaurМcio.
>
> In article <37D6799E...@dd.ru>,
> Valeri Sorokine <vsor...@dd.ru> wrote:
> > Hi, MaurИcio,
> >
> > What are you going to do with "FRM-40355: Query will retrieve XXX
> > records."?
> > You will have two messages, one yours about counted records and one
> > 40355...
> >
> > Anyway my point was about using the Count_Query built-in instead of
> > setting "Query All Records" property to True...
> >
> > Yours truly,
> > Valeri
> >
> > P.S. Our users don't press <count_query>, we use Count_Query built-in
> > in the KEY-EXEQRY trigger.
> >
> > MaurИcio wrote:
> > >
> > > Hi,
> > >
> > > that's almost perfect, except that I'd rather not intercepting
> > > messages in ON-MESSAGE trigger.
> > > As long as the user has to press <count_query>, it's cleaner
> > > (I think) to build a KEY-CQUERY (count-query key trigger) with:
> > >
> > > declare qt_recs varchar2(10);
> > > begin
> > > count_query;
> > > qt_recs := := Get_Block_Property(NAME_IN('SYSTEM.CURSOR_BLOCK'),
> > > QUERY_HITS);
> > > --
> > > -- do messages here (even if zero records...user may want to know)
> > > --
> > > end;
> > >
> > > Best regards,
> > > MaurМcio.
> > >
> > > In article <37CE1A42...@dd.ru>,
> > > Valeri Sorokine <vsor...@dd.ru> wrote:
> > > > > MaurМcio.