我已经建立了一个在增/删/改前触发的语句级触发器,希望将每次引起触发的SQL语句记录到一个库表中。
create or replace trigger tr_test_01
BEFORE DELETE OR INSERT OR UPDATE ON test_01
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
op_flag NUMBER;
begin
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
op_flag:=0;
CASE
WHEN inserting THEN op_flag:=1;
WHEN deleting THEN op_flag:=2;
WHEN updating THEN op_flag:=3;
END CASE;
insert into TAB_LOG (TABLE_NAME,ACT_TYPE,SQL_TXT)
values('test_01',op_flag,stmt);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unexpected Exception Occured in Trigger ');
dbms_output.put_line('SQLCODE:'||SQLCODE);
dbms_output.put_line('SQLERRM:'||SQLERRM);
end; 目前发现对于一般的sql操作均能正确记录下sql语句,但是对于动态绑定参数的sql,只能得到形如 insert into test_01 values(:1,:2,:3,:4)的语句,其中:1 :2 :3 :4对应的实际参数不知道如何获取,急得我直上火!!!请各路大侠伸出援手,一旦解决,另开贴送分!!!
create or replace trigger tr_test_01
BEFORE DELETE OR INSERT OR UPDATE ON test_01
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
op_flag NUMBER;
begin
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
op_flag:=0;
CASE
WHEN inserting THEN op_flag:=1;
WHEN deleting THEN op_flag:=2;
WHEN updating THEN op_flag:=3;
END CASE;
insert into TAB_LOG (TABLE_NAME,ACT_TYPE,SQL_TXT)
values('test_01',op_flag,stmt);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unexpected Exception Occured in Trigger ');
dbms_output.put_line('SQLCODE:'||SQLCODE);
dbms_output.put_line('SQLERRM:'||SQLERRM);
end; 目前发现对于一般的sql操作均能正确记录下sql语句,但是对于动态绑定参数的sql,只能得到形如 insert into test_01 values(:1,:2,:3,:4)的语句,其中:1 :2 :3 :4对应的实际参数不知道如何获取,急得我直上火!!!请各路大侠伸出援手,一旦解决,另开贴送分!!!
ora_client_ip_address
VARCHAR2
Provides the IP address of the client machine when using TCP/IP
ora_database_name
VARCHAR2(50)
Provides the database nameora_des_encrypted_password
VARCHAR2
Provides the DES encrypted password of the user being created or altered
ora_dict_obj_name
VARCHAR(30)
Provides the object name of the object being manipulated
ora_dict_obj_name_list(name_list OUT ora_name_list_t)
BINARY_INTEGER
Provides a list of object names being manipulated
ora_dict_obj_owner
VARCHAR(30)
Provides the owner of the object being manipulated
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t)
BINARY_INTEGER
Provides the owners of the objects being manipulated
ora_dict_obj_type
VARCHAR(20)
Provides the type of object being manipulated
ora_grantee(user_list OUT ora_name_list_t)
BINARY_INTEGER
Provides the number of grantees
ora_instance_num
NUMBER
Provides the instance number.
ora_is_alter_column(column_name IN VARCHAR2)
BOOLEAN
Provides a return value of TRUE if the specified column is altered
ora_is_creating_nested_table
BOOLEAN
Provides a return value of TRUE if the current event is creating a nested table
ora_is_drop_column(column_name IN VARCHAR2)
BOOLEAN
Provides a return value of TRUE if the specified column is dropped
ora_is_servererror
BOOLEAN
Provides a return value of TRUE is the error specified is on the error stack
ora_login_user
VARCHAR2(30)
Provides the login schema
ora_partition_pos
BINARY_INTEGER
Provides the position in a CREATE TABLE command where the partition clause can be inserted when using the INSTEAD OF trigger
ora_privilege_list(privilege_list OUT ora_name_list_t)
BINARY_INTEGER
Provides the list of privileges being granted or revoked
ora_revokee (user_list OUT ora_name_list_t)
BINARY_INTEGER
Provides a list of the revokees of the revoke command
ora_server_error
NUMBER
Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_depth
BINARY_INTEGER
Provides the total number of errors on the error stack
ora_server_error_msg (position in binary_integer)
VARCHAR2
Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_num_params (position in binary_integer)
BINARY_INTEGER
Provides the number of strings that have been substituted into the error message on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_param (position in binary_integer, param in binary_integer)
VARCHAR2
Provides the matching substitution value in the error message for the parameter number specified in conjunction with the position specified on the stack ( 1 meaning the top of the stack)
ora_sql_txt (sql_text out ora_name_list_t)
BINARY_INTEGER
Provides the SQL statement of the statement that caused the trigger to execute (if the statement is lengthy, it will separate it into multiple PL/SQL table elements); the value returned specifies the number of elements
ora_sysevent
VARCHAR2(20)
Provides the system or client event that caused the trigger to execute
ora_with_grant_option
BOOLEAN
Provides a return value of TRUE if the privileges are granted with the grant option
space_error_info(error_number OUT NUMBER,error_type OUT VARCHAR2,object_owner OUT VARCHAR2,table_space_name OUT VARCHAR2,object_name OUT VARCHAR2,sub_object_name OUT
VARCHAR2)
BOOLEAN
Provides a return value of true if the error is related to an out-of-space error and provides the object information of the object with the error
实在搞不定我只好每个表写个行级触发器判断、保存:old和:new了,还得多建一堆表。想想就头大