How to turn on logging on oracle client machine

by sunil ravulapalli /24. October 2011 15:44 /oracle /Comments (0)

Put this in your sqlnet.ora file

trace_level_client = 16
trace_unique_client = on
trace_file_client = sqlnet.trc
trace_directory_client = C:\oralogs

PL/SQL ROLLBACK WITHIN A LOOP GOTCHA

by sunil ravulapalli /14. June 2010 16:57 /oracle /Comments (0)
PROCEDURE DUMMY_PROC( x OUT NUMBER )
IS

CURSOR a_cursor IS
SELECT a  FROM TABLE_A;

a_record a_cursor%ROWTYPE;

BEGIN

OPEN a_cursor;
LOOP
FETCH a_cursor INTO a_record;
EXIT WHEN a_cursor%NOTFOUND;
BEGIN

update TABLE_B;
update TABLE_C; -- MAY CAUSE EXCEPTION

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END;
END LOOP;
CLOSE a_cursor;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END DUMMY_PROC;

The intention of this procedure is to read TABLE_A row by row and update TABLE_B and TABLE_C based on the values in TABLE_A. Line 4: Initially, TABLE_A contents are loaded into a_cursor. Line 11-14: Then we use a LOOP to iterate through it. Line 17-18: We update TABLE_B and TABLE_C. Line 20: We COMMIT to make changes permanent. Line 22-24: If we have an EXCEPTION, let's say on Line 18 while updating TABLE_C, the program jumps over the COMMIT to Line 22 and does a ROLLBACK on Line 24, restoring the contents of TABLE_B and the LOOP continues, goes back to Line 13 to read the next line in the CURSOR. At the outset, this seems ok, but the fact is that the ROLLBACK on Line 32 'kills' the cursor. So, when the program goes back to Line 13 to read the next row in the CURSOR, the program blows up, because it is trying to read something which isn't there. There are two ways to fix this. I am not sure which way is 'politically correct'. The first way is to just put COMMIT on Line 6 as show above. So, when we ROLLBACK, this COMMIT(Line 6) 'saves' the CURSOR.

PROCEDURE DUMMY_PROC( x OUT NUMBER )  
IS

CURSOR a_cursor IS
SELECT a  FROM A_TABLE;
COMMIT;--DEFENCE
a_record a_cursor%ROWTYPE;

BEGIN

OPEN a_cursor;
LOOP
FETCH a_cursor INTO a_record;
EXIT WHEN a_cursor%NOTFOUND;
BEGIN

update TABLE_B;
update TABLE_C; -- MAY CAUSE EXCEPTION

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END;
END LOOP;
CLOSE a_cursor;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END DUMMY_PROC;

The other way is to put a SAVEPOINT like in Line 16 and then ROLLBACK to only that point like in Line 24.

PROCEDURE DUMMY_PROC( x OUT NUMBER )  
IS

CURSOR a_cursor IS
SELECT a  FROM A_TABLE;

a_record a_cursor%ROWTYPE;

BEGIN

OPEN a_cursor;
LOOP
FETCH a_cursor INTO a_record;
EXIT WHEN a_cursor%NOTFOUND;
BEGIN
SAVEPOINT defense_point;
update TABLE_B;
update TABLE_C; -- MAY CAUSE EXCEPTION

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO defense_point;

END;
END LOOP;
CLOSE a_cursor;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END DUMMY_PROC;