[20170410]11G ora_sql_txt是否有效.txt
--链接问的问题: http://www.itpub.net/thread-2086256-1-1.html
--我写的测试脚本,自己测试看看:
1.环境:
SYS@test> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
CREATE TABLE SYS.ERROR_LOG
(
IP_ADDRESS VARCHAR2(30 BYTE),
USERNAME VARCHAR2(30 BYTE),
INSTANCE_NUMBER NUMBER(2),
THEDATE DATE,
ERRORMSG VARCHAR2(4000 BYTE),
OTHERMSG VARCHAR2(4000 BYTE),
SQLSTMT VARCHAR2(4000 BYTE)
)
TABLESPACE USERS;
CREATE OR REPLACE TRIGGER SYS.LOG_ERRORS
AFTER SERVERERROR ON DATABASE
DECLARE
sqltext ora_name_list_t;
errmsg VARCHAR2 (4000) := NULL;
errstmt VARCHAR2 (4000) := NULL;
errothermsg VARCHAR2 (4000) := NULL;
BEGIN
IF ( is_servererror (942))
THEN
FOR DEPTH IN 1 .. ora_server_error_depth
LOOP
errmsg := errmsg || ora_server_error_msg (DEPTH);
END LOOP;
FOR cntr IN 1 .. ora_sql_txt (sqltext)
LOOP
errstmt := errstmt || sqltext (cntr);
END LOOP;
SELECT 'sid:' || SID || ' SERIAL#: ' || serial# || ' machina:' || machine || ' program:' || program || ' module:'
|| module
INTO errothermsg
FROM v$session
WHERE SID = (SELECT SID
FROM v$mystat
WHERE ROWNUM = 1);
INSERT INTO error_log
(ip_address, username, instance_number, thedate, errormsg, othermsg,
sqlstmt
)
VALUES (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), ora_login_user, ora_instance_num, SYSDATE, errmsg, errothermsg,
errstmt
);
END IF;
END;
/
2.测试:
SYS@test> SYS@test> select * from aaaaaaaaa;
select * from aaaaaaaaa
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@test>select * from error_log;
Record View
As of: 2017/4/10 20:39:19
IP_ADDRESS:
USERNAME: SYS
INSTANCE_NUMBER: 1
THEDATE: 2017/4/10 20:35:55
ERRORMSG: ORA-00942: table or view does not exist
OTHERMSG: sid:242 SERIAL#: 3 machina:WORKGROUP\XXXX program:sqlplus.exe module:sqlplus.exe
SQLSTMT: select * from aaaaaaaaa
--//不是ok的吗?我使用12c.