Forced Oracle error on checkout
I am trying to debug strange behavior in my application. To do this, I need to reproduce a scenario in which the SQL SELECT query will throw an error, but only when actually fetching from the cursor, not when executing the query itself. It can be done? Any error will be there, but ORA-01722: invalid number
it seems obvious to try.
I created a table with a description:
KEYCOL INTEGER PRIMARY KEY
OTHERCOL VARCHAR2(100)
Then I created several hundred rows with unique values for the primary key and a value l
for othercol
. Then I ran a SELECT * query, selected a row somewhere in the middle, and updated it to a row abcd
. I ran the query SELECT KEYCOL, TO_NUMBER(OTHERCOL) FROM SOMETABLE
hoping to get some lines of good data and then an error later. But I keep getting ORA-01722: invalid number
on the very runtime.
I got this behavior programmatically using ADO (with server-side cursor) and JDBC and also from PL / SQL Developer. How can I get the result I am looking for? thanks
Edit - to add, when using ADO I only call Command.Execute
. I am not creating or opening a recordset.
a source to share
This can be a selection of a very large batch (eg 1000). Maybe when you did an update, for some reason this line is picked up very early.
Not sure if this is any help, but you can choose from a pipelined table function which might give you finer control over whether to return and when.
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);
a source to share
This anonymous pl / SQL block 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
a source to share