Re: Forms4.5: How can I show the record position in the block?
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;
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)||' recordsreturned';
Hope this is what you were looking for.
-Eric Received on Tue May 19 1998 - 18:27:44 CEST