Pro*C: How many rows selected?

Pro*C: How many rows selected?

Post by Wolfgang Schna » Wed, 04 Oct 1995 04:00:00



A Pro*C question:
Is there a possibility to get the number of rows selected after an
OPEN CURSOR statement *before* doing a FETCH?
(after the FETCH I can see the fetched rows in sqlca.sqlerrd[2]).

or do I have to use "SELECT count(*)" first, which means actually doing
the same call twice?

I'm using Pro*C 1.5, ORACLE 7.1

        Thanx, Wolfgang.

--
         V           Dr. Wolfgang Schnabl              
+-----------------+  Alcatel Austria AG / Dept. CGP    (tel) +43-1-27722-3059
|  A L C A T E L  |  Scheydgasse 41                    (fax) +43-1-27722-171

 
 
 

Pro*C: How many rows selected?

Post by Saad Ahm » Wed, 04 Oct 1995 04:00:00



> A Pro*C question:
> Is there a possibility to get the number of rows selected after an
> OPEN CURSOR statement *before* doing a FETCH?
> (after the FETCH I can see the fetched rows in sqlca.sqlerrd[2]).

  This would not tell you the total rows fetched after the
  first fetch.

Quote:> or do I have to use "SELECT count(*)" first, which means actually doing
> the same call twice?

  i think so

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *

**************************************************************

 
 
 

Pro*C: How many rows selected?

Post by gsa.. » Thu, 05 Oct 1995 04:00:00



>A Pro*C question:
>Is there a possibility to get the number of rows selected after an
>OPEN CURSOR statement *before* doing a FETCH?
>(after the FETCH I can see the fetched rows in sqlca.sqlerrd[2]).

>or do I have to use "SELECT count(*)" first, which means actually doing
>the same call twice?

>I'm using Pro*C 1.5, ORACLE 7.1

Hi,
I doubt it,
and after all, even after the fetch, you get only what you've fetched so far, not all
that may be fetched.
hth
 
 
 

Pro*C: How many rows selected?

Post by Henri M.B. van den Bul » Thu, 05 Oct 1995 04:00:00



>A Pro*C question:
>Is there a possibility to get the number of rows selected after an
>OPEN CURSOR statement *before* doing a FETCH?
>(after the FETCH I can see the fetched rows in sqlca.sqlerrd[2]).

After a open statement sqlca.sqlerrd[2] is zeroed and incremented after a FETCH.

sqlca.sqlerrd[2] only contains the number of rows for EXECUTE,INSERT, UPDATE DELETE and
SELECT INTO statements.

Quote:

>or do I have to use "SELECT count(*)" first, which means actually doing
>the same call twice?

>I'm using Pro*C 1.5, ORACLE 7.1

That's just what I do. But I'm looking into methods of getting the number of rows after the
OPEN. It seems to me that Pro*C must store this somewhere to indicate it internal.

---------------------------------------------
 Henri van den Bulk
 MGI Tulsa
 A Landmark Graphics Corp.
 5506 South Lewis Avenue
 Tulsa, OK  74105  USA
 tel. +1-918-749-7498, fax. +1-918-749-5556

        Art has to be forgotten, beauty has to be realised.
                Mondriaan

 
 
 

Pro*C: How many rows selected?

Post by Eric Kee » Fri, 06 Oct 1995 04:00:00


This is not a strange request. Some database API's support functions
like sqlnrr(cursor,rowcount) - which report the number of rows in a
result set.

--
Be wary of strong drink.  
It can make you shoot at tax collectors and miss.
-- Lazarus Long, "Time Enough for Love"

 
 
 

Pro*C: How many rows selected?

Post by Rob Alla » Fri, 06 Oct 1995 04:00:00


After the select, check the following variable:

sqlca.sqlerrd[2]

This is in the manual.
--

Ontario Hydro                   |  Tel. (416) 592 4195
Toronto, Ontario, Canada        |  Fax  (416) 592 4966

 
 
 

Pro*C: How many rows selected?

Post by Jessica Co » Thu, 12 Oct 1995 04:00:00


I would say it common to SELECT count(*) before you open the CURSOR
but this is typically done to no if it's even worth opening.  You could
put a counter in and increment after each FETCH.

Jessica Cook


: > A Pro*C question:
: > Is there a possibility to get the number of rows selected after an
: > OPEN CURSOR statement *before* doing a FETCH?
: > (after the FETCH I can see the fetched rows in sqlca.sqlerrd[2]).
:   This would not tell you the total rows fetched after the
:   first fetch.

: > or do I have to use "SELECT count(*)" first, which means actually doing
: > the same call twice?
:   i think so

: --
: **************************************************************
: *                          Saad Ahmad                        *
: *                          McHugh Freeman & Associates, Inc. *
: *                          Ph:  (414) 798-8606 Ext. 457      *
: *                          Fax: (414) 798-8619               *

: **************************************************************

 
 
 

1. returning a multi row select within a single row select

has anyone ever created a query that you have a table which links into
a one to many that has a one to many relationship with a lookup table
and you want to return a single row with the data from the first table
and the data from the one to many.
example below of what i'm looking for, i would do it programatically
but i'm using a generic peice of asp code that picks up sql from a
directory depending on what a user has selected so it can only only be
run once.
 cheers
LukeH

table a

key_a   col_1   col_2   col_3   col_4

1       a       a       a       a
2       bb      bb      bb      bb
3       ccc     ccc     ccc     ccc

table b                 table c

key_a   key_c            key_c  desc

1       1                1       x
1       2                2       y
1       3                3       z
2       1                2       2
3       3

data to be returned from query

key_a   col_1   col_2   col_3 column name here depends on whats to be
returned  as key_a will be used in the where clause. So only one row
will be returned but need to be able to handle all cases below.

1       a       a       a       x       y        z
2       bb      bb      bb      x       y
3       ccc     ccc     ccc     z

2. Laserjet 6L Problems

3. Row order differs from select to select?

4. UCX and NT WINS

5. Any practical examples of share, row share, row excl, share row excl ?

6. Please recommend Palm compatible printer w/IR...

7. Setting maximum rows to be selected

8. WinMgmt error

9. Select * from table where rows not locked

10. Getting the first row from a select

11. Setting maximum rows to be selected

12. select and update by row number

13. Question: select that just detects a matching row