Calling an autonomous function from SQL.

 

SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL> CREATE OR REPLACE FUNCTION LogParam(p1 IN NUMBER)
  2    RETURN NUMBER AS
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5    INSERT INTO MyTable (num_col, char_col)
  6      VALUES (p1, 'Logged!');
  7    COMMIT;
  8    RETURN p1;
  9  END LogParam;
 10  /

Function created.

SQL>
SQL> SELECT LogParam(1) FROM dual;
SELECT LogParam(1) FROM dual
       *
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "JAVA2S.LOGPARAM", line 7


SQL>
SQL> SELECT num_col
  2    FROM MyTable
  3    WHERE char_col = 'Logged!';

no rows selected

SQL>
SQL> drop table MyTable;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Commit an insert statement in PL SQL
2.	set transaction use rollback segment
3.	The pragma is legal in top-level anonymous blocks:
4.	The pragma is not legal in nested blocks:
5.	The pragma is valid in both standalone and local subprograms.
6.	The pragma is valid in a packaged procedure.
7.	interaction between savepoints and autonomous transactions.
8.	Autonomous transactions.

Calling an autonomous function from SQL. : Transaction « PL SQL « Oracle PL / SQL

Related examples in the same category