record count

Posted on 2005-04-29
Last Modified: 2010-04-12
I am using execute_query to display records in block and i want to display the total of record displayed is there any bulid in command for my question.
Question by:rana_m
    LVL 14

    Expert Comment

    Select count(*) from your_table_name;
    LVL 8

    Expert Comment

    You can have a display item in your form.  After doing execute_query, then you have to code select count(1) into :item_name from table;
    LVL 4

    Expert Comment

    Try to use this command.....

    INSCNT := sql%rowcount;

    which will assign the number of records affected by ur operation to a variable.   You can use this variable to display the number of records affected.The sql%rowcount is a predefined implicit cursor variable frm where you can find the number of records affected by ur operation......



    Author Comment

    I have tried all the three answers. Thanks for sending but all didn't work.
    I don't want to display the number of records in the table.
    I want to count the total records display by execute_query for a certian condition. Not all the records displayed.
    I tired the thired answer but still it didn't work.
    LVL 13

    Accepted Solution

    use count_query built-in:'

    from documentation:

    In an On-Count trigger, performs the default Form Builder processing for identifying the number of rows that a query will retrieve for the current block, and clears the current block.  If there are changes to commit in the block, Form Builder prompts the end user to commit them during COUNT_QUERY processing.  Form Builder returns the following message as a result of a valid call to COUNT_QUERY:

    FRM-40355:  Query will retrieve <number> records.

    This built-in is included primarily for applications that will run against a non-ORACLE data source.


    Built-in Type   restricted procedure
    Enter Query Mode  yes



    Count_Query Examples

    Example 1


    ** Built-in:  COUNT_QUERY
    ** Example:   Display the number of records that will be retrieved
    **            by the current query.

    Example 2

    ** Built-in:  COUNT_QUERY
    ** Example:   Perform Form Builder count query hits processing.
    **            Decide whether to use this Built-in or a user
    **            exit based on a global flag setup at startup by
    **            the form, perhaps based on a parameter.
    ** Trigger:   On-Count
      ** Check the global flag we set during form startup
      IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
        ** User exit returns query hits count back into the
        ** CONTROL.HITS item.
        ** Deposit the number of query hits in the appropriate
        ** block property so Form Builder can display its normal
        ** status message.
      ** Otherwise, do the right thing.
      END IF;

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now