I have a big but INTERMITTENT problem with a bug in Oracle 10g when we call some SQL within a Java web application. We can't quickly patch or upgrade to 11g - which seems to be the first 'stupid' oracle support response. There is a work around, but I am having trouble doing this within PreparedStatements within my Java code.
The actual error is:
ORA-00600: internal error code, arguments: [kcblasm_1]
The bug is: Oracle Bug 12419392
The work around is running
alter session set "_hash_join_enabled" = FALSE;
before we run our bug-inducing SQL. However, traditionally a PreparedStatement takes in one single piece of SQL:
PreparedStatement stmt = con.prepareSelect("sql statement2");
Is it possible to have one PreparedStatement call that looks like this:
PreparedStatement stmt = con.prepareSelect("sql statement1; sql statement2;");
Or is this possible just by running a series of sequential PreparedStatements one after the other?
Not the best time to be getting this with Xmas looming and reduced support etc. etc., so I really hope someone can help. Thanks.
Edit: @jonearles asked for the code, so here it is, if it's on any use. Probably very specific to our project, but someone might spot the glaring bug-inducing issue:
SELECT DISTINCT qm.validator_id,
qm.QM_ID,
u.EMAIL,
qm.creation_dt,
qm.emailed,
qm.valid,
qm.resolved,
qm.new_obs_id,
o.*,
nests.*,
s.*,
l.*,
latc.TENKM
FROM query_man qm,
obs o,
obs_aux_aon nests,
sub s,
location l,
l_atlas_tetrad_coverage latc,
users u
WHERE qm.OBS_ID = o.OBS_ID
AND o.SUB_ID = s.SUB_ID
AND u.user_id = qm.user_id
AND o.obs_id = nests.obs_id(+)
AND s.LOC_ID = l.LOC_ID
AND latc.ATLAS_REGION = 'NKNE'
AND (LENGTH (l.gridref) = 6
AND (SUBSTR(l.gridref,1,3)
|| SUBSTR(l.gridref,5,1)) = latc.TENKM
OR LENGTH (l.gridref) = 4
AND l.gridref = latc.TENKM)
AND qm.RESOLVED IS NULL
ORDER BY latc.tenkm,
l.tetrad
OK. The answer to my primary question is NO, you can't create a PreparedStatement like so:
PreparedStatement stmt = con.prepareSelect("sql statement1; sql statement2;");
Running individual statements to alter session temporarily for one bit of SQL did work, but agreed seems awful and also unacceptably slowed response. Options seem to be patch or upgrade, or look into the no_use_hash hint (which I think will be slow too). Will look at code.