Oracle PL/SQL - Invoking Autonomous Functions from SQL

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL SQL Statement
  4. Declaring Autonomous Function in Package

Introduction

The package function log_msg below is autonomous.

Therefore, when the query invokes the function, the function inserts a message into database table debug_output without violating the rule against writing database state.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w w  w.j a va  2  s .  co  m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL> DROP TABLE debug_output;

Table dropped.

SQL> CREATE TABLE debug_output (message VARCHAR2(200));
SQL>
SQL> CREATE OR REPLACE PACKAGE debugging AS
  2    FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
  3  END debugging;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY debugging AS
  2    FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4    BEGIN
  5      INSERT INTO debug_output (message) VALUES (msg);
  6      COMMIT;
  7      RETURN msg;
  8    END;
  9  END debugging;
 10  /

Package body created.

SQL> -- Invoke package function from query
SQL> DECLARE
  2    my_emp_id    NUMBER(6);
  3    my_last_name VARCHAR2(25);
  4    my_count     NUMBER;
  5  BEGIN
  6    my_emp_id := 120;
  7
  8    SELECT debugging.log_msg(last_name)
  9    INTO my_last_name
 10    FROM emp
 11    WHERE empid = my_emp_id;
 12
 13
 14    ROLLBACK;
 15  END;
 16  /

SQL>

Related Topic