Re: Number of records

> Hi Everybody,
 
I want to count the number of records that will retrieve against
EXECUTE_QUERY built in. There is a COUNT_QUERY built in , which give the
message how many records will return but I want to store this value
(number of records) in a variable. How can I do this, Is there any other
built in which can we use??

This is no other built-in, but you can catch the message and extract the 
count value from it with an ON-MESSAGE trigger like this:

DECLARE 
  v_errtype VARCHAR2(3) := MESSAGE_TYPE;
  v_errcode NUMBER := MESSAGE_CODE;
  v_errtxt VARCHAR2(80) := MESSAGE_TEXT;
  v_count INTEGER;
BEGIN
  IF v_errcode = 40355 THEN
    -- Count Query message looks like "Query will retrieve 999 records"
    -- Get the substring from pos 21 up to the next space.
    v_count := to_number(substr( v_errtxt, 21, instr( v_errtxt, ' ', 22)-
21 ));
  ELSE
    -- Show other messages as usual
    Message(v_errtype||'-'||to_char(v_errcode)||':  '||v_errtxt);
  END IF;
END;

Bear in mind that to get this count, COUNT_QUERY has to run the query, 
which may take some time for some queries.
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].