COMMIT IMPLICIT IN DYNAMIC SQL
When I use dynamic SQL "EXECUTE IMMEDIATE ..." in my procedure PL/SQL.
The COMMIT command is implicitly executed. How deactivated it COMMITTED implicit?
EXAMPLE OF PROCEDURE:
CREATE OR REPLACE PROCEDURE Test1 IS
NumOfRecord NUMBER := -1;
BEGIN
INSERT INTO COMMUN.HISTORY_TRANSACTION VALUES( SYSDATE, 'Test1', 1, 1, 1, 1, NULL, 'Test1' );
dbms_output.put_line('Test1: Before EXECUTE IMMEDIATE');
EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_ID_CONDUIT';
dbms_output.put_line('Test1: Before ROLLBACK');
ROLLBACK;
END Test1;
EXECUTE TRACE
SQL>
SQL>
SQL> SELECT * FROM HISTORY_TRANSACTION;
no rows selected
SQL> exec Test1;
Test1: Before EXECUTE IMMEDIATE
Test1: Before ROLLBACK
PL/SQL procedure successfully completed.
SQL> SELECT * FROM HISTORY_TRANSACTION;
TRANSACT PROCEDURE_NAME ID_PROJECT ID_CONDUIT EXECUTE_MODE STATUS SQL_CODE_ERROR DESCRIPTION
-------- --------------------- ---------- ---------- ------------ ---------- --------------- -----------------
02-04-10 Test1 1 1 1 1 Test1