昨天下午写了2个Function，一个是从ERP LN中读出来Item-Purchase BP中的一个指定的Item和BP的BlockingReason，另一个是按照指定的ID从应用系统中读出来Item和BP，从而调用第一个Function返回真正的BlockingReason，在每一个Function中，都有一段如果报错就调用写错误日志的存储过程。在实际的使用中使用：Select Function2(ID) from Dual;，但是老报以下错误：
<br/>ORA-06502: PL/SQL: numeric or value error: character string buffer too small<br/>ORA-06512: at "TROY.ONTRAC_QRS_PKG", line 760<br/>ORA-14551: cannot perform a DML operation inside a query <br/>ORA-06512: at "TROY.COMMON_SYSTEMLOG_PKG", line 43<br/>ORA-06512: at "TROY.COMMON_BAANBRIDGE_PKG", line 339<br/>ORA-01403: no data found <br/>
经过查询，原因是“对数据库有写操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的函数,是无法简单的用SQL来调用的”。按照这篇文章：[URL=http://www.cnblogs.com/pengyq/archive/2008/11/26/1341656.html]ORA-14551: cannot perform a DML operation inside a query [/URL]，加上了“自主事务”：PRAGMA AUTONOMOUS_TRANSACTION; ，不过需要注意在Delcare的部分加入“PRAGMA AUTONOMOUS_TRANSACTION;”之后，必须在SQL语句后面加入“COMMIT;”
看如下例子：
<br/> /*/* RFD #50 20111214 new start Troy Cui */<br/> PRAGMA AUTONOMOUS_TRANSACTION;<br/> /*/* RFD #50 20111214 new end Troy Cui */<br/> BEGIN<br/> <br/> select trim(nvl(FirstName, '') || ' ' || nvl(LastName, ''))<br/> into sDisplayName<br/> from COMMON_Users<br/> where UserID = nLoggedByID;<br/><br/> if (sDisplayName = '') then <br/> sDisplayName := '- unassigned -';<br/> end if;<br/> <br/> insert into COMMON_SystemLog(LoggedByID, LogDate, LogCategory, LogEntry, AccessLevel, <br/> Source, DataDump, DisplayName)<br/> values(nLoggedByID, SYSTIMESTAMP, sLogCategory, sLogEntry, nAccessLevel,<br/> sSource, sDataDump, sDisplayName);<br/> /*/* RFD #50 20111214 new start Troy Cui */<br/> COMMIT;<br/> /*/* RFD #50 20111214 new end Troy Cui */<br/><br/> END WriteLog;<br/>
