Re: Cursors : When Does Oracle Show Error

Oracle is using the to_char on the deptno field for comparison on the where clause of the select. You are asking it to compare a varchar and a number (the IN group).

In article <71psfl$8q$1_at_nnrp1.dejanews.com>,   ajayanand_at_my-dejanews.com wrote:
> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 05 1998 - 14:55:35 CST