Just curious to see if anyone has seen anything like this - this is not a production issue, just something that I find interesting (a challenge if you will).
I have been testing Oracle 11.1.0.6 and 11.1.0.7 with an ERP package since January and have encountered an interesting issue where the ERP package throws an error "ORA-02005: implicit (-1)
length not valid for this bind or define datatype" error, when selecting the BLOB column from any table containing a BLOB - this same ERP package executes without problem with Oracle
10.2.0.2/10.2.0.3/10.2.0.4. The table definition is as follows:
PART_ID NOT NULL VARCHAR2(30)
TYPE NOT NULL CHAR(1)
BITS BLOB
BITS_LENGTH NOT NULL NUMBER(38)
The previous version of the ERP package had the same table defined as follows, and the previous version of the ERP package had no problem with Oracle 11.1.0.6:
PART_ID NOT NULL VARCHAR2(30)
TYPE NOT NULL CHAR(1)
BITS LONG RAW
BITS_LENGTH NOT NULL NUMBER(38)
One of the SQL statements which is tossing the error:
SELECT BITS FROM PART_MFG_BINARY where TYPE = :1 and PART_ID = :2
A portion of a 10046 trace from Oracle 10.2.0.2 for comparison:
=====================
PARSING IN CURSOR #2 len=87 dep=0 uid=30 oct=3 lid=30 tim=749963475 hv=1159951869 ad='53a45ac8'
select mfg_name, mfg_part_id from part where id = :1
END OF STMT
PARSE #2:c=0,e=1427,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=749963466
BINDS #2:
kkscoacd
Bind#0
oacdty=96 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=32 off=0
kxsbbbfp=380b9b68 bln=32 avl=09 flg=05
value="98567109M"
EXEC #2:c=0,e=3357,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=749971833
FETCH #2:c=0,e=52,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=749971968
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=749973655
=====================
PARSING IN CURSOR #3 len=59 dep=0 uid=30 oct=3 lid=30 tim=749983314 hv=2907586799 ad='5457f690'
select part_udf_labels from APPLICATION_GLOBAL
END OF STMT
PARSE #3:c=0,e=3389,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=749983305
BINDS #3:
EXEC #3:c=0,e=152,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=749986393
FETCH #3:c=0,e=124,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=749988214
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=11925 op='TABLE ACCESS FULL APPLICATION_GLOBAL (cr=7 pr=0 pw=0 time=104 us)'
=====================
PARSING IN CURSOR #3 len=59 dep=0 uid=30 oct=3 lid=30 tim=749992936 hv=2907586799 ad='5457f690'
select part_udf_labels from APPLICATION_GLOBAL
END OF STMT
PARSE #3:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=749992932
BINDS #3:
EXEC #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=749996097
FETCH #3:c=0,e=116,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=749997800
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=12429 op='TABLE ACCESS BY INDEX ROWID PART (cr=3 pr=0 pw=0 time=48 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=12436 op='INDEX UNIQUE SCAN SYS_C005496 (cr=2 pr=0 pw=0 time=28 us)'
=====================
PARSING IN CURSOR #2 len=99 dep=0 uid=30 oct=3 lid=30 tim=750003263 hv=1519706035 ad='7e235fc0'
SELECT BITS FROM PART_MFG_BINARY where TYPE = :1 and PART_ID = :2
END OF STMT
PARSE #2:c=0,e=1100,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=750003255
BINDS #2:
kkscoacd
Bind#0
oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=64 off=0
kxsbbbfp=380bdcd8 bln=32 avl=01 flg=05
value="D"
Bind#1
oacdty=96 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=32
kxsbbbfp=380bdcf8 bln=32 avl=09 flg=01
value="98567109M"
EXEC #2:c=0,e=2512,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=750022595
FETCH #2:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=750024142
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=101246 op='TABLE ACCESS BY INDEX ROWID PART_MFG_BINARY (cr=1 pr=0 pw=0 time=30 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=101249 op='INDEX UNIQUE SCAN SYS_C0018720 (cr=1 pr=0 pw=0 time=21 us)'
=====================
A portion of a 10046 trace from Oracle 11.1.0.6:
=====================
PARSING IN CURSOR #3 len=87 dep=0 uid=59 oct=3 lid=59 tim=1023659125907 hv=1159951869 ad='22a109c8' sqlid='7k8rzcj2k6xgx'
select mfg_name, mfg_part_id from part where id = :1
END OF STMT
PARSE #3:c=0,e=432,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659125903
BINDS #3:
Bind#0
oacdty=96 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=32 off=0
kxsbbbfp=0c6e0fd4 bln=32 avl=09 flg=05
value="98567109M"
EXEC #3:c=0,e=1068,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659130848
FETCH #3:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1023659132062
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=67567 op='TABLE ACCESS BY INDEX ROWID PART (cr=3 pr=0 pw=0 time=0 us cost=2 size=14 card=1)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=69248 op='INDEX UNIQUE SCAN SYS_C0011926 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
=====================
PARSING IN CURSOR #6 len=59 dep=0 uid=59 oct=3 lid=59 tim=1023659138710 hv=2907586799 ad='22a44ae8' sqlid='3n102kqqnwh7g'
select part_udf_labels from APPLICATION_GLOBAL
END OF STMT
PARSE #6:c=0,e=701,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659138706
BINDS #6:
EXEC #6:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1023659142030
FETCH #6:c=0,e=55,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1023659143936
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=67410 op='TABLE ACCESS FULL APPLICATION_GLOBAL (cr=3 pr=0 pw=0 time=0 us cost=3 size=146 card=1)'
=====================
PARSING IN CURSOR #6 len=59 dep=0 uid=59 oct=3 lid=59 tim=1023659148354 hv=2907586799 ad='22a44ae8' sqlid='3n102kqqnwh7g'
select part_udf_labels from APPLICATION_GLOBAL
END OF STMT
PARSE #6:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1023659148351
BINDS #6:
EXEC #6:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1023659151927
FETCH #6:c=0,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1023659153664
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=67410 op='TABLE ACCESS FULL APPLICATION_GLOBAL (cr=3 pr=0 pw=0 time=0 us cost=3 size=146 card=1)'
=====================
PARSING IN CURSOR #3 len=99 dep=0 uid=59 oct=3 lid=59 tim=1023659158452 hv=1519706035 ad='22a10580' sqlid='gm6bkj9d99rxm'
SELECT BITS FROM PART_MFG_BINARY where TYPE = :1 and PART_ID = :2
END OF STMT
PARSE #3:c=0,e=399,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659158448
XCTEND rlbk=1, rd_only=1
In the above, notice the rollback (XCTEND rlbk=1, rd_only=1) before Oracle would have output the bind variable values in the trace file (bind variable values were never written).
(Continued...)
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.