Re: Forms4.5: How can I show the record position in the block?

From: Eric Lyders-Gustafson <ericlg_at_homemail.com>
Date: Tue, 19 May 1998 11:27:44 -0500
Message-ID: <3561B2FF.C22AAACF_at_homemail.com>


Valery A. Sorokin wrote:

> Dear Oracle's Gurus,
>
> How can I show the record position in the Forms block like this: '3/134'?
>
> The problem is :
>
> I want to show on the screen the number of records in the current block
> with current 'WHERE' property. After all query or navigation operations.
>
> 'COUNT_QUERY' is the restricted built-in, so I can't use it anywhere.
>
> Any solutions will be appreciated.
>
> Thanks in advance.
> --
> Valery A. Sorokin

  It takes a little coding but is not too difficult.

I've done it before by using the built-in package 'DBMS_SQL'.

First, create a function that returns a number when given a text string, like this:

craete or replace FUNCTION DYNAMIC_SQL_NUM (SQL_TEXT VARCHAR2)RETURN NUMBER IS

  CURSOR_HANDLE INTEGER;
  SELECT_NUM    INTEGER;
  EXECUTE_SQL   INTEGER;

BEGIN
  CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;   DBMS_SQL.PARSE(CURSOR_HANDLE, SQL_TEXT, DBMS_SQL.V7);   DBMS_SQL.DEFINE_COLUMN(CURSOR_HANDLE,1, SELECT_NUM);   EXECUTE_SQL := DBMS_SQL.EXECUTE(CURSOR_HANDLE);   IF DBMS_SQL.FETCH_ROWS(CURSOR_HANDLE) = 0 THEN     RETURN(-1);
  ELSE
    DBMS_SQL.COLUMN_VALUE(CURSOR_HANDLE,1, SELECT_NUM);   END IF;
  DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);
  RETURN(SELECT_NUM);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    DBMS_OUTPUT.PUT_LINE(SQL_TEXT);
    RETURN(-1);
END; Second, create a post-select trigger for each block that you want to have the record count from. The post-select trigger will look something like this:

DECLARE

  v_where_pos  NUMBER;
  v_order_pos  NUMBER;
  v_where_txt  VARCHAR2(10000) := :System.Last_Query;
  v_select_txt varchar2(2000);

BEGIN
  • See if we can find the word 'ORDER BY' and 'WHERE' in the text of the Last Query v_where_pos := INSTR(v_where_txt,'WHERE'); v_order_pos := INSTR(v_where_txt,'ORDER BY');
  • If we found it (at a positive index into the string), we extract the
  • 'WHERE' text and stop at the 'ORDER BY' clause. if v_order_pos <= 0 then if v_where_pos > 0 THEN v_where_txt := substr(v_where_txt, v_where_pos); else v_where_txt := null; end if; else if v_where_pos > 0 THEN v_where_txt := SUBSTR(v_where_txt, v_where_pos, v_order_pos); else v_where_txt := null; end if; END IF;
  v_select_txt := 'select count(*) from <table_name> '||v_where_txt;   :dummy.query_count := dynamic_sql_num(v_select_txt);   message(v_select_txt); -- used for debugging only EXCEPTION
  WHEN OTHERS THEN
    :dummy.query_count := -2;
END; Third, create a display item in a 'dummy' block that will be used to hold the number of records returned from the query. In the above trigger I used 'dummy.query_count' as my block name and display item. (You could create multiple display items in the same 'dummy' block if you want a separate display item for each block you're going to query from.)

To show '3 out of 345 rows returned' call the dynamic_sql_num function twice, once with the where clause and once without it. You can then either have 2 display items, one for the where clause coount and one for the total count or concatenate the counts together and put the text into one display text item like this:

:dummy.count_text := to_char(v_where_count)||' out of '||

                                   to_char(v_total_count)||' records
returned';

Hope this is what you were looking for.

-Eric Received on Tue May 19 1998 - 18:27:44 CEST

Original text of this message