Home » SQL & PL/SQL » SQL & PL/SQL » Total Recors Count Calculation (10.2.0.4)
Total Recors Count Calculation [message #574686] Mon, 14 January 2013 22:41 Go to next message
tigsav
Messages: 44
Registered: April 2012
Member
Hello All,

We have a front end that is polling the database for some set of data.
That set of data is returned by opening a ref cursor and passing it back to the calling environment.
Now the problem they also want the count of total number of records that will be fetched by my select statement.
One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .Any way I can reduce this to 1 time or any other logic to get the count would be great.

Thanks
tigsav
Re: Total Recors Count Calculation [message #574687 is a reply to message #574686] Mon, 14 January 2013 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 23188
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Total Recors Count Calculation [message #574689 is a reply to message #574687] Tue, 15 January 2013 00:04 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Tigsav,

In this case,You have to use DBMS_SQL package for running your SQL Query.In this case you have to use:

A) dbms_sql.open_cursor
B) dbms_sql.parse(cursor_name,<select Query>
Here,rows_processed := DBMS_SQL.EXECUTE(cursor_name);
C) dbms_sql.close_cursor(cursor_name)


Then, you can retrieve total count in rows_processed local variable.

Please check and use this one in your code.

[Updated on: Tue, 15 January 2013 00:04]

Report message to a moderator

Re: Total Recors Count Calculation [message #574693 is a reply to message #574686] Tue, 15 January 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot get the number of rows returned by a cursor without fetching the rows.

Regards
Michel
Re: Total Recors Count Calculation [message #574697 is a reply to message #574693] Tue, 15 January 2013 02:31 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Michel,

Please try this and tell me where is the fetch statement we are using.But "tigsav' needs same approach :



CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);
/
CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE do_query_1 (
  placeholder vc_array,
  bindvars vc_array,
  sql_stmt VARCHAR2                    )
IS
  TYPE curtype IS REF CURSOR;
  src_cur      curtype;
  curid        NUMBER;
  bindnames    vc_array;
  empnos       numlist;
  depts        numlist;
  ret          NUMBER;
  isopen       BOOLEAN;
BEGIN
  -- Open SQL cursor number:
  curid := DBMS_SQL.OPEN_CURSOR;

  -- Parse SQL cursor number:
  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);

  bindnames := placeholder;

  -- Bind arguments:
  FOR i IN 1 .. bindnames.COUNT LOOP
    DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));
  END LOOP;

  -- Execute SQL cursor number:
  ret := DBMS_SQL.EXECUTE(curid);

  -- Switch from DBMS_SQL to native dynamic SQL:
  src_cur := DBMS_SQL.TO_REFCURSOR(curid);
  FETCH src_cur BULK COLLECT INTO empnos, depts;

  -- This would cause an error because curid was converted to a REF CURSOR:
  -- isopen := DBMS_SQL.IS_OPEN(curid);

  CLOSE src_cur;
END;
/


[Updated on: Tue, 15 January 2013 02:32]

Report message to a moderator

Re: Total Recors Count Calculation [message #574699 is a reply to message #574697] Tue, 15 January 2013 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
tell me where is the fetch statement we are using.

Quote:
FETCH src_cur BULK COLLECT INTO empnos, depts;


What you said in your previous post is wrong: the "rows_processed" returned by DBMS_SQL.EXECUTE are for DML not for SELECT.

Regards
Michel

[Updated on: Tue, 15 January 2013 02:36]

Report message to a moderator

Re: Total Recors Count Calculation [message #574938 is a reply to message #574686] Wed, 16 January 2013 22:57 Go to previous message
tigsav
Messages: 44
Registered: April 2012
Member
Hi All,
Thanks for the help. I would be fetching it to make things simple.

Thanks and Regards,
Tigsav
Previous Topic: need to find out db name for the indexes
Next Topic: Create Gap in Series
Goto Forum:
  


Current Time: Sat Dec 27 07:44:36 CST 2014

Total time taken to generate the page: 0.06105 seconds