Corrupted pl/sql cursor variable

From: Oracle-L DBA <oramail_at_sbcglobal.net>
Date: Fri, 25 Feb 2005 11:08:46 -0600
Message-ID: <002201c51b5c$ab416ce0$694032a6@wcomnet.com>

Hello,
I have a procedure that's being called frequently by the app, once in a while it's cursor variable getting corrupted and has junk value. I have to recompile the procedure to fix the issue.

Here is the synopsis of the proc :

---

cursor pl_prts is select plp.*, ownr.ownr_nm, ownr.prv_sys_cd from plp, ownr where plp.crd_id = a_crd_id and plp.ownr_id = ownr.ownr_id;

// a_crd_id is an argument for the procedure.
...
...

for prts in pl_prts
loop
begin
select prv_ind into v_prv_ind from
pccm_prt where ctlg_id = v_crd_ctlg_id and prt_ord_num = prts.prt_ord_num;
exception when no_data_found then
v_prv_ind := 'Y';
end;
...
...

end loop;
...
...

---

prts.prt_ord_num has corrupted values. The following is from Trace file generated using 10046 event :

bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=24 offset=0
bfp=10219c4d0 bln=22 avl=04 flg=05
value=20585
bind 1: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=24 offset=0
bfp=1021a7080 bln=22 avl=03 flg=09

value=-(990000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

This happens once in a while like few times in a week. Not able to reproduce. Recompiling the procedure fixes the issue (for a while). The RDBMS version is 8.1.7.2.0 EE 64 bit on Sun Solaris 5.8. I have opened a TAR and waiting for them to respond, please let me know if this is a known bug or something wrong with my configuration :

NAME                               VALUE
--------------------------------------- ---------------------
open_cursors                    1000
cursor_space_for_time       FALSE
session_cached_cursors    50
cursor_sharing                   EXACT

Regards,

Bala.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 25 2005 - 12:11:50 CST