问题

报错信息如下

分析：

1、该函数是判断字符串是不是日期类型，编译过程没有什么报错现象。 2、执行 SELECT validate_and_convert_date('0010809') FROM dual; 时则报出了上面的ORA-14551错误。 3、而DML操作指的就是增、删、改，对比了一下自己写的其它函数，发现其它的函数都没有这个操作。 4、查看相关资料，原来这是“自治事务和主事务问题”。 5、语句中sql，包含了insert操作，虽然有commit操作但是没有事物开始。 函数如下:

CREATE OR REPLACE FUNCTION validate_convert_date
(
p_str IN VARCHAR2
)


RETURN DATE


IS


 v_date DATE
;


 in_str  VARCHAR2
(
20
);


BEGIN


 in_str 
:=
 TRIM
(
p_str
);


 IF LENGTH
(
in_str
)
 
=
 
8
 THEN


     
BEGIN


        


         v_date 
:=
 TO_DATE
(
in_str
,
 
'YYYY-MM-DD'
);


     EXCEPTION


         WHEN OTHERS THEN


             


             INSERT INTO invalid_date_log 
(
invalid_value
,
 error_message
)


             VALUES 
(
p_str
,
 
'Invalid date format for 8-character input'
);


             RETURN NULL
;


     
END
;


 ELSIF LENGTH
(
in_str
)
 
=
 
14
 THEN


     
BEGIN


         


         v_date 
:=
 TO_DATE
(
in_str
,
 
'YYYY-MM-DD HH24:MI:SS'
);


     EXCEPTION


         WHEN OTHERS THEN


          


             INSERT INTO invalid_date_log 
(
invalid_value
,
 error_message
)


             VALUES 
(
p_str
,
 
'Invalid date format for 14-character input'
);


             commit
;


             RETURN NULL
;


     
END
;


 ELSE


     INSERT INTO invalid_date_log 
(
invalid_value
,
 error_message
)


     VALUES 
(
p_str
,
 
'Input length is not 8 or 14 characters'
);


     commit
;


     RETURN NULL
;


 
END
 IF
;




 RETURN v_date
;


END
;

原因：

通过上边的分析，可以知道在SELECT语句中不能对表进行修改（insert，update，delete）。 SELECT语句中的函数试图往表中插入数据就会报错。

####  引用官方说明


数据库事务是一种单元操作，要么是全部操作都成功，要么全部失败。在
Oracle
中，一个事务是从执行第一个数据管理语言（
DML
）语句开始，直到执行一个
COMMIT
语句，提交保存这个事务，或者执行一个
ROLLBACK
语句，放弃此次操作结束。


 
事务的“要么全部完成，要么什么都没完成”的本性会使将错误信息记入数据库表中变得很困难，因为当事务失败重新运行时，用来编写日志条目的
INSERT
语句还未完成。


 
针对这种困境，
Oracle
提供了一种便捷的方法，即自治事务。自治事务从当前事务开始，在其自身的语境中执行。它们能独立地被提交或重新运行，而不影响正在运行的事务。正因为这样，它们成了编写错误日志表格的理想形式。在事务中检测到错误时，您可以在错误日志表格中插入一行并提交它，然后在不丢失这次插入的情况下回滚主事务。


  
因为自治事务是与主事务相分离的，所以它不能检测到被修改过的行的当前状态。这就好像在主事务提交之前，它们一直处于单独的会话里，对自治事务来说，它们是不可用的。然而，反过来情况就不同了：主事务能够检测到已经执行过的自治事务的结果。


  
要创建一个自治事务，您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中，使用
PL
/
SQL
中的
PRAGMA AUTONOMOUS_TRANSACTION
语句。在这样的模块或过程中执行的
SQL 
Server
语句都是自治的。


  
触发无法包含
COMMIT
语句，除非有
PRAGMA AUTONOMOUS_TRANSACTION
标记。但是，只有触发中的语句才能被提交，主事务则不行。

解决办法

使用自治事务，在create or replace function is 后面添加如下内容

PRAGMA AUTONOMOUS_TRANSACTION
;




CREATE OR REPLACE FUNCTION validate_convert_date
(
p_str IN VARCHAR2
)


RETURN DATE


IS


    PRAGMA AUTONOMOUS_TRANSACTION
;


    v_date DATE
;