在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 和

查看全文