Mega Code Archive
Interaction between ALTER SESSION and autonomous transactions
<![CDATA[
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE InsertDate1(p_Msg IN VARCHAR2) AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO MyTable(num_col, char_col)
5 VALUES (400, p_Msg || ': ' || SYSDATE);
6 COMMIT;
7 END InsertDate1;
8 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE InsertDate2(p_Msg IN VARCHAR2) AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 EXECUTE IMMEDIATE
5 'ALTER SESSION SET NLS_DATE_FORMAT =
6 ''MM/DD/YYYY HH24:MI:SS''';
7
8 INSERT INTO MyTable(num_col, char_col)
9 VALUES (400, p_Msg || ': ' || SYSDATE);
10 COMMIT;
11 END InsertDate2;
12 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> DELETE FROM MyTable;
0 rows deleted.
SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL>
SQL> BEGIN
2 InsertDate1('First insert');
3 InsertDate2('Second insert');
4 InsertDate1('Third insert');
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "RNTSOFT.INSERTDATE1", line 6
ORA-06512: at line 2
SQL>
SQL> SELECT char_col
2 FROM MyTable
3 WHERE num_col = 400;
no rows selected
SQL>
SQL> ALTER SESSION DISABLE COMMIT IN PROCEDURE;
Session altered.
SQL>
SQL> BEGIN
2 InsertDate1('With COMMIT IN PROCEDURE disabled');
3 COMMIT;
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "RNTSOFT.INSERTDATE1", line 6
ORA-06512: at line 2
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
]]>