Transaction Control

[bostonian123]

I have to deal with a Stored procedure which is commiting the query after executing the query. I have to control the transaction at the application level with .NET. Hows it possible to control the transaction of this stored proc which is doing a commit inside its body...I cannot control the transaction's rollback or commit in the application with .NET's tran control as the decision of commit is being made at the stored proc level..Is there a solution to it?

[pragatiswain]

If you change your SP as below, it will solve the problem. Again if you capture the return value in the program, you can understand if the SP executed successfully or failed. Again which SQL created problem. You can set out param of SP to capture STOO_ERRMSG to find out what is the ora error message.

CREATE OR REPLACE PROCEDURE PROC1
RETURN INTEGER
AS
STOO_SELCNT INTEGER;
STOO_ROWCNT INTEGER;
STOO_RETVAL INTEGER;
STOO_ERRMSG varchar2(1500);
BEGIN
SAVEPOINT TRAN1;
BEGIN
STOO_RETVAL := 0;
STOO_ROWCNT := 0;
UPDATE TABLE1 SET ID = ID + 1 WHERE NAME = 'EVENT';
STOO_ROWCNT := SQL%ROWCOUNT;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || SQLCODE || '-' || SQLERRM);
STOO_ERROR := SQLCODE;
STOO_ERRMSG := SQLERRM;
STOO_RETVAL := 1; -- CUSTOMIZED ERROR CODE TO FIND WHICH SQL CREATED PROBLEM
ROLLBACK TO SAVEPOINT TRAN1;
END;
IF STOO_ROWCNT > 1 THEN
BEGIN
ROLLBACK TO SAVEPOINT TRAN1;
STOO_RETVAL := 2;
RETURN STOO_RETVAL;
END;
END IF;
IF (STOO_RETVAL = 0) THEN
COMMIT;
END IF;
RETURN STOO_RETVAL;
END PROC1;
/

Hope this helps