Cursors : When Does Oracle Show Error

From: <ajayanand_at_my-dejanews.com>
Date: Wed, 04 Nov 1998 15:39:33 GMT
Message-ID: <71psfl$8q$1@nnrp1.dejanews.com>

Hi!

Emp table has deptno field with varchar datatype.

CREATE OR REPLACE PACKAGE BODY emp_test AS

   erec emp%ROWTYPE;
   CURSOR emp_cur IS

      (SELECT * FROM emp WHERE
        deptno NOT IN (92, 94, 95)

   );

PROCEDURE emp_loop IS
BEGIN
   OPEN emp_cur;
   LOOP

      BEGIN
         FETCH emp_cur INTO erec;
         EXIT WHEN emp_cur%NOTFOUND;
      EXCEPTION
         WHEN INVALID_NUMBER THEN
            dbms_output.put_line ('Error ' || SUBSTR (SQLERRM, 1, 80));
         WHEN OTHERS THEN
            RAISE;
      END;

   END LOOP;
   close emp_cur;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line ('Error ' || SUBSTR (SQLERRM, 1, 80)); END emp_loop;
END emp_test;
/
exec emp_test.emp_loop

When I run this I get an 'Invalid Number' error. This is caused due to a deptno value which is 9D. I think Oracle is doing a to_number(deptno) and then a != select * from emp where to_number(deptno) in (92,94,95) and it is failing since it is not able to do a to_number(9D).

I am able to get results from fetch till the record with deptno = 9D is reached.

Since deptno is varchar, I should ideally put the values 92,94,95 in quotes. I tried that and things worked fine.

My doubt is :

When I do not use quotes, Why does this error occur in fetch and not when the cursor is opened ? Oracle identifies the record set in open cursor and to identify the record set it should have applied the to_number then.

Thanks.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Nov 04 1998 - 09:39:33 CST