Hello.
I have an issue I am not able to solve... it's driving me crazy...
I am receiving randomly and inconsistently, but with an alarming frequency, some "ORA-01001: invalid cursor" errors.
It does not reproduce in sqlplus, it only happens from jdbc calls from the application server, and repeating the same call usually works (but sometimes fails couple of times before succeeding).
Version of oracle is 10.2.0.3
the message is:
ORA-01001: invalid cursor ORA-06512: at
"APP.STRING_SEPARATE", line 8 ORA-06512: at
"APP.PKG_FIND_VAL", line 744 ORA-06512: at
"APP.PKG_FIND_VAL", line 838 ORA-06512: at line 1
The code looks like:
743> while counter >= l_pos loop
744> l_ret := string_separate(l_string, l_pos, ',');
745> if l_value not like '%' || l_ret || '%' then
746> raise val_exc;
747> end if;
748> l_pos := l_pos + 1;
749> end loop;
and STRING_SEPARATE function is
create or replace FUNCTION string_separate(
p_val IN VARCHAR2,
p_sel IN NUMBER,
p_sep IN VARCHAR2)
RETURN VARCHAR2 IS
p_retval VARCHAR2(4000);
BEGIN
SELECT SUBSTR(p_val,
decode(p_sel, 1, 1, instr(p_val, p_sep, 1, p_sel -1) + 1),
instr(p_val, p_sep, 1, p_sel) -1 -decode(p_sel, 1, 0, instr(p_val, p_sep, 1, p_sel -1))
)
INTO p_retval
FROM dual;
RETURN LTRIM(RTRIM(p_retval));
END string_separate;
From what I understand, line 744 (the invocation of the user defined function) is receiving an error (ORA-01001: invalid cursor) from the "select..into..from dual" in the user defined function.
How can this happen?
Or, which point am I missing?
Any help welcome,
Andrea