Re: Can you do select cursor%ROWCOUNT from dual?

It is certainly desirable not to open a cursor more than once. The second time it is opened something may have changed in the database.

One way to avoid opening the cursor more than once is to open the cursor and load all the data into one or more pl/sql tables or records. This provides a convenient name for a lot of data and one no longer needs the cursor. It can be closed. Then one can check to see if these pl/sql tables are empty. One can also move back and forth through the tables as well as return the pl/sql tables to a calling procedure.

The Concepts manual, chapter 15, defines a cursor as "a handle or name for a private SQL area -- an area in memory in which a parsed statement and other information for processing the statement are kept." This definition does not imply that a cursor is the result set of the query. One can, however, fetch the result set by executing the cursor as a named resource in a program and pass that result set (stored as variables or pl/sql tables) back and forth in the code.

Frank Hubeny

tmccarthy9435_at_my-deja.com wrote:

> I have some basic cursor questions. I've checked the FAQ and it helped
> some.
>
> Is there a way to test to see if the cursor has any values without
> having to do a fetch? like doing something like "select cursor%
> ROWCOUNT from dual. I tried that and it doesn't work, but maybe
> something similar. I've read some postings that imply this isn't
> possible, but I want to make sure. I'm ok with fetching into some
> variables. The goal is to return the cursor with all the values. As
> soon as I fetch I lose that row in the cursor. Only way I can get my
> logic to work is to test for rowcount and if there are values redo my
> cursor. Ugh. Sloppy, icky. I think in Sybase you can rewind your
> cursor. I forget though.
>
> The codes at the bottom. It's basically a wrapper function that will
> first call one function if it gets nothing try the next.
>
> Thanks in advance,
>
> Tricia
>
> (ssn_var IN client_pins.login%TYPE,
> pass_var IN client_pins.password%TYPE)
> return type.cursortype AS
> l_cursor type.cursortype;
>
> first_var EMPLOYEES.first_name%TYPE;
> last_var EMPLOYEES.last_name%TYPE;
> cli_var EMPLOYEE_PINS.cli_id%TYPE;
> tea_var EMPLOYEE_PINS.cli_tea_id%TYPE;
> reg_var EMPLOYEE_PINS.cli_reg_id%TYPE;
> ssn_new_var EMPLOYEE_PINS.ssn%TYPE;
> emp_var EMPLOYEE_PINS.emp_id%TYPE;
> role_var EMPLOYEE_PINS.role%TYPE;
> grp_var WEB_ROLE_GROUPS.grp%TYPE;
>
> coname_var CLIENTS.name%TYPE;
> login_var CLIENT_PINS.login%TYPE;
> password_var CLIENT_PINS.password%TYPE;
> num_cli_var NUMBER;
>
> begin
>
> -- Check the Client Pins
>
> l_cursor := web_clientlogin(ssn_var, pass_var);
>
> fetch l_cursor into coname_var, cli_var, tea_var, reg_var,
> login_var, role_var, grp_var;
>
> -- Nothing found in Client Pins
>
> if (l_cursor%ROWCOUNT = 0) then
>
> --Check the Employee Pins
>
> l_cursor := web_employeelogin(TO_NUMBER(TRANSLATE(ssn_var, '!
> abcdefghijklmnopqrstuvwxyz.', 0) ),
> TO_NUMBER(TRANSLATE(pass_var,'!
> abcdefghijklmnopqrstuvwxyz.', 0) ) );
>
> fetch l_cursor into first_var, last_var, cli_var, tea_var,
> reg_var, ssn_new_var, emp_var, role_var, grp_var;
>
> -- Nothing found in Employee Pins
>
> if (l_cursor%ROWCOUNT = 0) then
>
> DBMS_OUTPUT.PUT_LINE(' No employees found for ' ||
> ssn_var||','||pass_var );
> open l_cursor for select 0 from dual;
> return l_cursor;
>
> -- Found it in Employee Pins
> -- since I can't rewind the cursor execute it again - Ugh!
>
> else
>
> DBMS_OUTPUT.PUT_LINE('Employees for ' ||
> emp_var||','||last_var );
> l_cursor := web_employeelogin(TO_NUMBER(TRANSLATE
> (ssn_var, '!abcdefghijklmnopqrstuvwxyz.', 0) ),
> TO_NUMBER(TRANSLATE
> (pass_var,'!abcdefghijklmnopqrstuvwxyz.', 0) ) );
> return l_cursor;
>
> end if;
>
> -- Found it in Client Pins
> -- since I can't rewind the cursor execute it again - Ugh!
>
> else
>
> DBMS_OUTPUT.PUT_LINE('Clients for ' ||
> coname_var||','||login_var );
> l_cursor := web_clientlogin(ssn_var, pass_var);
> return l_cursor;
>
> end if;
>
> end;
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Feb 08 2001 - 01:00:24 CST