在Oracle中记录存储过程访问 [英] Logging stored procedure access in Oracle
问题描述
是否有使用Oracle记录对存储过程的访问的便捷方法?我是一名网络开发人员,目前我们需要将各种用户信息传递给许多存储过程,以便这些过程可以依次调用另一个过程,该过程将对原始存储过程的访问记录在表中.
Is there an convenient way to log access to stored procedures from withing Oracle? I am a web developer and presently we are required to pass a variety of user info to many stored procedures so that those procedures can in turn call another procedure that logs access to the original stored procedure in a table.
例如,如果我想调用一个名为get_movie(id)的过程,该过程将基于id从电影表中返回一行,则我必须做类似get_movie(username,domain,ip,id)的操作,该过程可以记录发起对该过程调用的Web用户的用户/域/IP.
For example if I want to call a procedure called get_movie(id) that will return a row from the movie table based on id, I would have to do something like this get_movie(username, domain, ip, id) so that the procedure can log the user/domain/ip of the web user who initiated the call to the procedure.
似乎必须有更好的方法,但是我对Oracle的了解有限.
It seems like there must be a better way but my knowledge of Oracle is limited.
推荐答案
我将使用过程设置通用参数,每次连接时都使用sys_context
设置
I would set the common parameters using a procedure and sys_context
every time you get your connection
例如:
CREATE OR REPLACE PROCEDURE set_context
(
v_userid IN VARCHAR2,
v_domain IN VARCHAR2,
v_ip IN VARCHAR2,
v_id IN VARCHAR2
)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT('SESSIONCONTEXT', 'username', v_userid);
DBMS_SESSION.SET_CONTEXT('SESSIONCONTEXT', 'domain', v_domain);
DBMS_SESSION.SET_CONTEXT('SESSIONCONTEXT', 'ip', v_ip);
DBMS_SESSION.SET_CONTEXT('SESSIONCONTEXT', 'id', v_id);
END;
并查询值:
SELECT SYS_CONTEXT('SESSIONCONTEXT', 'username') FROM dual;
请参阅: http://download.oracle .com/docs/cd/E14072_01/server.112/e10592/functions182.htm 和