|
Long time since I posted here. So it's time for an update.
I'm still working on some more flexfield articles. But a busy schedule is delaying things.
So in the meantime some things that I ran into the last few days. The first is a classic issue: How do I submit session dependent information over a database link.
Case:
I enter or update data over a database link. On the target table, there is a trigger that needs some data from v$session, or some other session dependent information.
Let's look at a simplified example:
create database link l_loop connect to apps identified by apps using 'VIS';
create table t_test (id number, value varchar2(200));
create trigger t_test_trg
before insert or update on t_test
for each row
declare
v_client_info varchar2(200);
begin
dbms_application_info.read_client_info(v_client_info);
:new.value:=v_client_info;
end;
/
This code would usually replace the content of 'Value' with the current client_info from the session. However, this does not work over a database link.
Consider the following:
begin
dbms_application_info.set_client_info('Remote insert');
end;
/
insert into t_test@l_loop values (1,'Not relevant info');
select * from t_test;
ID VALUE
----- -----------------
1 (Null)
The trigger did fire. But it found no 'Client_info' in its session.
So what are our options? Actually, they are very limited.
One option would be to use create a view with an extra column that holds the client_info. Then use an 'Instead of'-trigger to enter the data into the table. But this will result in a beautiful:
SQL Error: ORA-02070: database does not support in this context
02070. 00000 - "database %s%s does not support %s in this context"
*Cause: The remote database does not support the named capability in
the context in which it is used.
*Action: Simplify the SQL statement.
At least for my 11.1.0.7 database, that didn't work.
Another option I tried is using a different parameter. Using sys_context, or a custom context. But in each case, the context is not relayed over the database link.
The solution:
The only workable solution I found is using a package variable on the target database. On the target database, I created a package that holds a global variable:
create package xxx_variables_pkg is
g_client_info varchar2(200) := 'Default value';
end;
/
Create synonym xxx_variables_pkg_l for xxx_variables_pkg@l_loop;
Now we can set the variable, and use it over the database loop. First we change our trigger:
create or replace trigger t_test_trg
before insert or update on t_test
for each row
declare
v_client_info varchar2(200);
begin
:new.value:=xxx_variables_pkg.g_client_info;
end;
/
Create synonym xxx_variables_pkg_r for xxx_variables_pkg@l_loop;
Now we set the variable on the remote database:
begin
xxx_variables_pkg_l.g_client_info:='Remote update';
end;
/
ORA-06550: line 2, column 21:
PLS-00512: Implementation Restriction: 'XXX_VARIABLES_PKG_R.G_CLIENT_INFO': Cannot directly access remote package variable or cursor
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Bummer!
That also doesn't work. But fortunately, the error message holds the clue to solving this problem: 'Cannot DIRECTLY access remote package variable'.
So let's try indirectly by calling a remote procedure to do the work for us:
create or replace package xxx_variables_pkg is
g_client_info varchar2(200) := 'Default value';
procedure set_variable (p_client_info IN VARCHAR2);
end;
/
create or replace package body xxx_variables_pkg is
procedure set_variable (p_client_info IN VARCHAR2) is
begin
xxx_variables_pkg.g_client_info:=p_client_info;
end;
end xxx_variables_pkg;
/
Now we can call the following:
begin
xxx_variables_pkg_r.set_variable('Remote update');
end;
/
insert into t_test@l_loop values (1,'Not relevant info');
select * from t_test@l_loop;
ID VALUE
----- -----------------
1 Remote update
So we managed to relay some session dependent information over a database link.
At the end of the day you'll have to decide if it's not easier to use an API on the remote database that accepts extra parameters for the data you need.
In our case, this was not convenient, because of the number of different tables being updated.
HTH (Hope This Helps)
|
