[20170410]11G ora_sql_txt是否有效.txt

[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.