Home » SQL & PL/SQL » SQL & PL/SQL » SQL%ROWCOUNT don't give the true value !!! (Oracle 10g , windows XP SP2)
icon4.gif  SQL%ROWCOUNT don't give the true value !!! [message #342577] Fri, 22 August 2008 20:01 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

DECLARE
   TYPE emp_table_type IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;

   my_emp_table   emp_table_type;
   mmax           NUMBER;
   mmin           NUMBER;
BEGIN
   SELECT MAX (employee_id)
     INTO mmax
     FROM employees;

   SELECT MIN (employee_id)
     INTO mmin
     FROM employees;

   FOR i IN mmin .. mmax
   LOOP
      SELECT *
        INTO my_emp_table (i)
        FROM employees
       WHERE employee_id = i;
   END LOOP;

   FOR i IN my_emp_table.FIRST .. my_emp_table.LAST
   LOOP
      DBMS_OUTPUT.put_line (   my_emp_table (i).last_name
                            || '  '
                            || my_emp_table (i).salary
                           );
   END LOOP;

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;
/


in the end of the previous code the (SQL%ROWCOUNT) don't give the whole number of the Rows , suppose print 107 but it print 1 ........ How could I fix that ?
Re: SQL%ROWCOUNT don't give the true value !!! [message #342578 is a reply to message #342577] Fri, 22 August 2008 20:06 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
1 is the correct return value, SQL%ROWCOUNT will return the rowcount of the most recent SQL statement which in this case is 1.
Re: SQL%ROWCOUNT don't give the true value !!! [message #342593 is a reply to message #342577] Sat, 23 August 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2     TYPE emp_table_type IS TABLE OF employees%ROWTYPE
  3        INDEX BY PLS_INTEGER;
  4  
  5     my_emp_table   emp_table_type;
  6     mmax           NUMBER;
  7     mmin           NUMBER;
  8  BEGIN
  9  
 10     SELECT *
 11     BULK COLLECT INTO my_emp_table
 12     FROM employees
 13     ORDER BY employee_id;
 14  
 15     FOR i IN my_emp_table.FIRST .. my_emp_table.LAST
 16     LOOP
 17        DBMS_OUTPUT.put_line (   my_emp_table (i).last_name
 18                              || '  '
 19                              || my_emp_table (i).salary
 20                             );
 21     END LOOP;
 22  
 23     DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
 24  END;
 25  /
King  24000
[105 output line removed]
Gietz  8300
107

PL/SQL procedure successfully completed.

or but less performant:
SQL> DECLARE
  2     TYPE emp_table_type IS TABLE OF employees%ROWTYPE
  3        INDEX BY PLS_INTEGER;
  4  
  5     my_emp_table   emp_table_type;
  6     mmax           NUMBER;
  7     mmin           NUMBER;
  8  BEGIN
  9     SELECT MAX (employee_id)
 10       INTO mmax
 11       FROM employees;
 12  
 13     SELECT MIN (employee_id)
 14       INTO mmin
 15       FROM employees;
 16  
 17     FOR i IN mmin .. mmax
 18     LOOP
 19        SELECT *
 20          INTO my_emp_table (i)
 21          FROM employees
 22         WHERE employee_id = i;
 23     END LOOP;
 24  
 25     FOR i IN my_emp_table.FIRST .. my_emp_table.LAST
 26     LOOP
 27        DBMS_OUTPUT.put_line (   my_emp_table (i).last_name
 28                              || '  '
 29                              || my_emp_table (i).salary
 30                             );
 31     END LOOP;
 32  
 33     DBMS_OUTPUT.put_line (my_emp_table.COUNT);
 34  END;
 35  /
King  24000
[105 output line removed]
Gietz  8300
107

PL/SQL procedure successfully completed.

Regards
Michel
Re: SQL%ROWCOUNT don't give the true value !!! [message #342603 is a reply to message #342577] Sat, 23 August 2008 01:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hany, you do realize that your code will only work if there are no gaps in the emp-id's ?
Re: SQL%ROWCOUNT don't give the true value !!! [message #342669 is a reply to message #342577] Sat, 23 August 2008 18:16 Go to previous messageGo to next message
rodolpho
Messages: 6
Registered: August 2008
Location: Rio de Janeiro, Brazil
Junior Member
Hany,

The "SQL object" is a implicit cursor, managing by Oracle (open and close) and her real function is store properties from last execute statement in you PL/SQl block. Any property by your pseudocolumn (ROWCOUNT, FOUND, NOFOUND,and so on...) will only aways store informations from last execute statement.
Re: SQL%ROWCOUNT don't give the true value !!! [message #342745 is a reply to message #342577] Sun, 24 August 2008 17:32 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There is also an obscure bug in some versions of pl/sql regarding use of sql%rowcount in combination with certain other pl/sql constructs. The fix/hack I have used in the past when necessary is below. This rewrite also demonstrates the reason for your initial confusion more clearly.

DECLARE
--
-- use a variable for rowcounts after sql statmets and fetch into it after each sql execution
-- this is a hack in response to a bug in some versions of pl/sql
--
   sql_rowcount_v number;

   TYPE emp_table_type IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;

   my_emp_table   emp_table_type;
   mmax           NUMBER;
   mmin           NUMBER;
BEGIN
   SELECT MAX (employee_id)
     INTO mmax
     FROM employees;
--
   sql_rowcount_v := sql%rowcount;

   SELECT MIN (employee_id)
     INTO mmin
     FROM employees;
--
   sql_rowcount_v := sql%rowcount;

   FOR i IN mmin .. mmax
   LOOP
      SELECT *
        INTO my_emp_table (i)
        FROM employees
       WHERE employee_id = i;
--
      sql_rowcount_v := sql%rowcount;
   END LOOP;

   FOR i IN my_emp_table.FIRST .. my_emp_table.LAST
   LOOP
      DBMS_OUTPUT.put_line (   my_emp_table (i).last_name
                            || '  '
                            || my_emp_table (i).salary
                           );
   END LOOP;
--
-- this of course was the point of your original mistake
--
   DBMS_OUTPUT.put_line (sql_rowcount_v);
END;
/


Good luck, Kevin
Previous Topic: Question on RefCursor result count
Next Topic: Materialized views
Goto Forum:
  


Current Time: Thu Dec 08 12:31:00 CST 2016

Total time taken to generate the page: 0.05132 seconds