Re: ref cusror does not return rows

"Manoj" <manoj_20057_at_yahoo.com> a écrit dans le message de news: 1129175293.720235.36250_at_g47g2000cwa.googlegroups.com...
| Hello all,
|
| I have created following package using REF CURSOR. But the
| cursor does not return any rows. But if i do, fetch loop, it
| shows the proper values. What I am doing wrong ??
|
| CREATE OR REPLACE PACKAGE pckgNm
| IS
| TYPE t_cursor IS REF CURSOR;
| PROCEDURE sp_getdata (p_recid IN NUMBER, p_datarec IN OUT t_cursor);
|
| END pckgnm;
|
| CREATE OR REPLACE PACKAGE BODY pckgnm
| IS
|
| PROCEDURE sp_getdata (p_recid IN NUMBER, p_datarec IN OUT t_cursor)
| IS
| chkrec CHAR;
| chkcnt NUMBER;
| v_field1 varchar2(10);
| v_rec t_cursor;
| BEGIN
|
| OPEN v_rec
| FOR
| SELECT field1
| FROM view_object where recidfield = p_recid;
|
| chkcnt := v_rec%ROWCOUNT; -- shows zero
|
| IF v_rec%FOUND
| THEN
| p_datarec := v_rec;
| chkrec := 'Y';
| ELSE
| chkrec := 'N'; -- control comes here if still having
| records
|
| END IF;
| p_datarec := v_datarec; -- passes null
|
| LOOP
| FETCH v_rec INTO v_field1;
|
| -- this loop runs if having the records and v_field1 shows
| proper values
|
| EXIT WHEN v_rec%NOTFOUND;
| -- process row
|
| END LOOP;
|
| CLOSE v_rec;
|
| END;
|
| end pckgnm;
|
|
| thanks,
|
| Manoj.
|

From PL/SQL User's Guide and Reference, Chapter 13 "PL/SQL Language Elements", Section "Cursor Attributes", Paragraph "%ROWCOUNT Attribute":

<quote>
This is a cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the latest fetch returned a row. </quote>

Regards
Michel Cadot Received on Thu Oct 13 2005 - 00:18:45 CDT