Can you do select cursor%ROWCOUNT from dual?

From: <tmccarthy9435_at_my-deja.com>
Date: Wed, 07 Feb 2001 04:26:54 GMT
Message-ID: <95qiq7$ich$1@nnrp1.deja.com>

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 Tue Feb 06 2001 - 22:26:54 CST