如何将任意参数发送到 Oracle 触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1880729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to send arbitrary parameters to Oracle trigger?
提问by FRotthowe
The purpose is to send extra information to triggers like current user id from a web application. Since a connection pool is used, and same user id is used for all connections how do I pass the original web user id to trigger? This I need to implement without touching application code. It is a java based application.
目的是从 Web 应用程序向触发器发送额外信息，例如当前用户 ID。由于使用了连接池，并且所有连接都使用相同的用户 ID，我该如何传递原始 Web 用户 ID 来触发？这是我需要在不触及应用程序代码的情况下实现的。它是一个基于java的应用程序。
John
约翰
回答by FRotthowe
You can use the client_identifier session variable to pass an application user to a trigger.
您可以使用 client_identifier 会话变量将应用程序用户传递给触发器。
Set it after connecting to the database like this:
像这样连接到数据库后设置它：
CALL dbms_session.set_identifier('<<username>>');
and retrieve it inside the trigger:
并在触发器内检索它：
SELECT sys_context('USERENV','CLIENT_IDENTIFIER') INTO username FROM DUAL;
More info can be found in the Oracle docs
更多信息可以在Oracle 文档中找到
回答by Vincent Malgrat
you could use Oracle Contexts:
您可以使用 Oracle上下文：
SQL> CREATE OR REPLACE PACKAGE test_pkg AS
2 PROCEDURE set_context(p_attribute VARCHAR2, p_value VARCHAR2);
3 END test_pkg;
4 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS
2 PROCEDURE set_context(p_attribute VARCHAR2, p_value VARCHAR2) IS
3 BEGIN
4 dbms_session.set_context('test_ctx', p_attribute, p_value);
5 END;
6 END test_pkg;
7 /
Package body created
SQL> create context test_ctx using test_pkg;
Context created
SQL> exec test_pkg.set_context ('user_id', 'Vincent');
PL/SQL procedure successfully completed
SQL> select sys_context('test_ctx', 'user_id') from dual;
SYS_CONTEXT('TEST_CTX','USER_I
--------------------------------------------------------------------------------
Vincent
回答by Tony Andrews
You can use a package to keep track of the web user:
您可以使用包来跟踪网络用户：
create package web_user_pkg is
procedure set_username (p_username varchar2);
function username return varchar2;
end;
create package body web_user_pkg is
g_username varchar2(30);
procedure set_username (p_username varchar2)
is
begin
g_username := p_username;
end;
function username return varchar2 is
begin
return g_username;
end;
end;
In the web page call web_user_pkg.set_username with the current user's ID before performing any DML or other package calls.
在执行任何 DML 或其他包调用之前，在网页中使用当前用户的 ID 调用 web_user_pkg.set_username。
In the trigger use web_user_pkg.username to get the web user name.
在触发器中使用 web_user_pkg.username 来获取 Web 用户名。
