声明函数并返回varchar oracle

Question

我试图在pl/sql中声明一个函数，并从中返回一个变量内容。

 CREATE OR REPLACE FUNCTION executeTransaction
    RETURN VARCHAR2 as
    pragma autonomous_transaction;
    myLog VARCHAR(1200) :='myLog';
  BEGIN
    savepoint my_savepoint;
    begin
        insert into datalogger(MPRN_FK,K0213,K0214) values(465,'2142342','423423');
        myLog := 'transaction completed with rows insert ' || SQL%ROWCOUNT;
        rollback to my_savepoint;
    end;
    insert into myTbl(col) values(myLog);
    RETURN myLog;
  END executeTransaction;

函数可以编译，但当我像一样执行它时

select executeTransaction from dual;

我得到

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "ECO_AFMS_GAS_CUST.GET_ALLITEMS", line 14
06519. 00000 -  "active autonomous transaction detected and rolled back"
*Cause:    Before returning from an autonomous PL/SQL block, all autonomous
           transactions started within the block must be completed (either
           committed or rolled back). If not, the active autonomous
           transaction is implicitly rolled back and this error is raised.
*Action:   Ensure that before returning from an autonomous PL/SQL block,
           any active autonomous transactions are explicitly committed
           or rolled back.

并且不返回变量内容？我知道我必须提交或回滚，但在这种情况下，我回滚，所以它应该工作吗？

Answer 1

由于使用的是pragma autonomous_transaction，因此需要在函数末尾添加COMMIT。错误状态为

如果没有，则隐式回滚活动的自主事务，并引发此错误。

CREATE OR REPLACE FUNCTION executeTransaction
    RETURN VARCHAR2 as
    pragma autonomous_transaction;
    myLog VARCHAR(1200) :='myLog';
  BEGIN
    savepoint my_savepoint;
    begin
        insert into datalogger(MPRN_FK,K0213,K0214) values(465,'2142342','423423');
        myLog := 'transaction completed with rows insert ' || SQL%ROWCOUNT;
        rollback to my_savepoint;
    end;
    insert into myTbl(col) values(myLog);
    commit;         --add commit
    RETURN myLog;
  END executeTransaction;