Corrupted pl/sql cursor variable
- From: "Oracle-L DBA" <oramail@xxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 25 Feb 2005 11:08:46 -0600
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
