Functions called from SQL statements must follow certain rules.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
According to the definition, autonomous procedures never read or write database states. In other words, they do not query or modify any database tables.
In the following example, the package function oblog_msg is autonomous. Therefore, when this function is called, it will insert a message into the database table obdebug_output, without violating the database table modification rules.
obclient> CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0)
);
Query OK, 0 rows affected
obclient> INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1);
Query OK, 1 row affected
obclient> CREATE TABLE obdebug_output (msg VARCHAR2(200));
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PACKAGE obdebugging AUTHID DEFINER AS
FUNCTION oblog_msg (message VARCHAR2) RETURN VARCHAR2;
END obdebugging;
/
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PACKAGE BODY obdebugging AS
FUNCTION oblog_msg (message VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO obdebug_output (msg) VALUES (message);
COMMIT;
RETURN message;
END;
END obdebugging;
/
Query OK, 0 rows affected
-- Call the package function
obclient>DECLARE
my_id NUMBER(6);
my_name VARCHAR2(25);
my_count NUMBER;
BEGIN
my_id := 200;
SELECT obdebugging.oblog_msg(empname)
INTO my_name
FROM emp
WHERE empno = my_id;
ROLLBACK;
END;
/
Query OK, 0 rows affected