ORA-29701 when calling System.Data.OracleClient.OracleLob.Write

I recently experienced some unhandled errors when attempting to install a custom module in a DotNetNuke site that had an Oracle back end.  The DB provider I was using was AcuitiDB.

After some debugging, I was able to establish that the error was being raised from a call to AddDesktopModule, and the StackTrace showed that it was originating in a call to System.Data.OracleClient.OracleLob.Write:

[OracleException (0x80131938): ORA-29701: unable to connect to Cluster Manager

]

   System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +304889

   System.Data.OracleClient.OracleLob.Write(Byte[] buffer, Int32 offset, Int32 count) +587

   DotNetNuke.Data.OracleSqlHelper.CreateClobParameter(String ConnectionString, String ParameterName, String p) +229

I contacted Sanjay Mehrota at Acuiti and he was very helpful in assisting me to track down the cause of this.  Firstly, he suggested that I try some other areas of the site that created clobs, such as the text/html module.  This exhibited the same error.  He also provided some test code for creating a temporary clob which I added to a simple console app as a test harness.  This initially appeared to work correctly, but when I increased the size of the string being used to around 29,000 bytes, I got the same error.  It transpires that Temporary clobs are created in the temporary tablespace and that owing to some other testing work that was being performed on the DB, we had run out of tablespace.  I got the DBA to increase the size of the temporary tablespace and the problem disappeared.

Ask Tom "Avoiding overlap values..."

Nice check for overlapping values in Oracle

Ask Tom "Avoiding overlap values..."

My first question on asktom

Yay! My first question has been answered on asktom.

Record Exists

Copied from some tips put together by Chris Rudd, ultimately sourced from Steven Feuerstein
/*
Note that the following shows three ways of checking if a record exists which have very similar response times, one way (implicit cursor) which is bad, and a fifth way
( select count(*) ) which is a VERY BAD THING and will result in broken fingers for anyone found using it!
*/
SET VERIFY OFF
@ssoo
DECLARE
/* Different approaches to answering "at least one?" */
CURSOR empcur
IS
SELECT employee_id
FROM employee_big WHERE department_id = &&secondparm;
v NUMBER;
b BOOLEAN;
BEGIN
plvtmr.set_factor (&&firstparm);
plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
BEGIN
SELECT employee_id INTO v FROM employee_big
WHERE department_id = &&secondparm;
b := TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN b := FALSE;
WHEN TOO_MANY_ROWS THEN b := TRUE;
END;
END LOOP;
PLVtmr.show_elapsed ('Implicit');

plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
OPEN empcur;
FETCH empcur INTO v;
b := empcur%FOUND;
CLOSE empcur;
END LOOP;
PLVtmr.show_elapsed ('Explicit');

plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
SELECT COUNT(*) INTO v
FROM employee_big WHERE department_id = &&secondparm;
b := v > 0;
END LOOP;
PLVtmr.show_elapsed ('COUNT');

/* Ohio OUG Contributions.... */
plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
SELECT COUNT(1) INTO v
FROM employee_big WHERE department_id = &&secondparm
AND ROWNUM <> 0;
END LOOP;
PLVtmr.show_elapsed ('COUNT ROWNUM<2'); department_id =" &&secondparm);"> @atleastone 1000 20
Implicit Elapsed: .45 seconds. Factored: .00045 seconds.
Explicit Elapsed: .12 seconds. Factored: .00012 seconds.
COUNT Elapsed: 2.21 seconds. Factored: .00221 seconds.
COUNT ROWNUM<2> @atleastone 20000 20
Implicit Elapsed: 8.06 seconds. Factored: .0004 seconds.
Explicit Elapsed: 2.46 seconds. Factored: .00012 seconds.
COUNT Elapsed: 42.21 seconds. Factored: .00211 seconds.
COUNT ROWNUM<2 Elapsed: 2.42 seconds. Factored: .00012 seconds.
EXISTS Elapsed: 2.63 seconds. Factored: .00013 seconds.

* /

END;
/