Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

COMMIT IMPLICIT IN DYNAMIC SQL

52246Apr 10 2002
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 8 2002
Added on Apr 10 2002
1 comment
1,075 views