Force Oracle error on fetch

Question

I am trying to debug a strange behavior in my application. In order to do so, I need to reproduce a scenario where an SQL SELECT query will throw an error, but only while actually fetching from the cursor, not while executing the query itself. Can this be done? Any error will do, but ORA-01722: invalid number seems like the obvious one to try.

I created a table with the follwing:

KEYCOL INTEGER PRIMARY KEY
OTHERCOL VARCHAR2(100)

I then created a few hundred rows with unique values for the primary key and the value l for the othercol. I then ran a SELECT * query, picked a row somewhere in the middle, and updated it to the string abcd. I ran the query SELECT KEYCOL, TO_NUMBER(OTHERCOL) FROM SOMETABLE hoping to get some rows of good data an then an error later. But I keep getting ORA-01722: invalid number on the execute step itself.

I have gotten this behavior programmatically using ADO (with server-side cursor) and JDBC, as well as from PL/SQL Developer. How can I get the result I'm looking for? thanks

Edit - meant to add, when using ADO, I am only calling Command.Execute. I am not creating or opening a Recordset.

Answer 1

This anonymous block of pl/SQL reproduces the error on the third iteration of the cursor:

BEGIN
   FOR c1_rec IN (WITH example AS
                       (SELECT '1' TEST_DATA
                          FROM DUAL
                        UNION
                        SELECT '2' TEST_DATA
                          FROM DUAL
                        UNION
                        SELECT '3A' TEST_DATA
                          FROM DUAL)
                  SELECT TO_NUMBER (TEST_DATA) TEST_NUM
                    FROM example)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Called ' || c1_rec.TEST_NUM);
   END LOOP;
END;

Output:

Called 1
Called 2
Error at line 2
ORA-01722: invalid number
ORA-06512: at line 2

Answer 2

It could be it is selecting a very large batch (eg 1000). It could be that, when you did the update, for some reason that row is being picked up very early.

Not sure whether it is any help, but you can select from a pipelined table function which may give you more precise control over whether and when to return the error.

create or replace function ret_err return sys.dbms_debug_vc2coll pipelined is
begin
   for i in 1..200 loop
      pipe row ('test');
   end loop;
   raise_application_error (-20001,'Error here');
   return;
end;
/

select * from table(ret_err);