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 MySQL mode.
By definition, autonomous procedures do not read from or write to the database state. This means they do not query or modify any database tables.
In the following example, the package function oblog_msg is autonomous. Therefore, when you query this function, it inserts a message into the obdebug_output table without violating the rule against modifying database tables.
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