rowcount in Oracle

Question

I want to use the function SQL%ROWCOUNT as a way to tell me if a record is in a table or not. The code that I have is the following:

DECLARE
v_emp employee%ROWTYPE; 
CURSOR c_emp IS
SELECT * FROM employee WHERE name='chuck';
BEGIN
OPEN c_emp;

    FETCH c_emp INTO v_emp;
    IF SQL%ROWCOUNT=1 THEN
             DBMS_OUTPUT.PUT_LINE('found');
            ELSE
                     DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
        END IF;
END;

But it does not print anything at all, eventhough that record with that name exists in the database

Thanks

Answer 1

Normally, you'd do something like

DECLARE
  l_count PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO l_count
    FROM employee
   WHERE name = 'chuck'
     AND rownum = 1;

  IF( l_count = 1 )
  THEN
    dbms_output.put_line( 'found' );
  ELSE
    dbms_output.put_line( 'not found' );
  END IF;
END;

If you really want to use an explicit cursor, you would need to check the <<cursor_name>>%rowcount, not sql%rowcount to determine how many rows had been fetched. If you're going to use an explicit cursor, you also need to take care to close the cursor. Since you didn't post your table definition or the data you're using, I'll use the EMP table in the SCOTT schema as an example

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    v_emp emp%ROWTYPE;
  3    CURSOR c_emp IS
  4      SELECT * FROM emp WHERE ename='SMITH';
  5  BEGIN
  6    OPEN c_emp;
  7    FETCH c_emp INTO v_emp;
  8    IF c_emp%ROWCOUNT=1 THEN
  9      DBMS_OUTPUT.PUT_LINE('found');
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
 12    END IF;
 13    CLOSE c_emp;
 14* END;
SQL> /
found

PL/SQL procedure successfully completed.

And note that no matter what approach you use, if you want the output from DBMS_OUTPUT to be displayed, you need to enable output in whatever tool you are using. If you are using SQL*Plus, that would mean running

SQL> set serveroutput on;

before executing the anonymous PL/SQL block.