How To Get Client IP Address For a Locking User

This code is created using Forms Builder 10.1.2.0.2 and WebUtil Version 1.0.6.

For step (4), user needs execute privilege on DBMS_APPLICATION_INFO

For step (7), you need to connect to the DB using any user has a "Select" privilege on v$session

Create IP-Address Sample form

1. Install and configure your Developer installation to use webutil

http://www.oracle.com/technology/software/products/forms/files/webutil/webutil_106.zip


2. Create new form using webutil


3. Create a WHEN-NEW-FORM-INSTANCE trigger add the following:

======================================================================
declare
v_timer timer;
begin
v_timer := create_timer('ip_timer',2,no_repeat);
end;
======================================================================

4. Create a database procedure

======================================================================
create or replace procedure SETCLIENTINFO (p_info varchar2) is
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_info);
exception
when others then
raise_application_error(-20101,'SETCLIENTINFO: error: '||sqlerrm);
end;
======================================================================


5. Create a WHEN-TIMER-EXPIRED add the following code:

======================================================================
declare
v_trimer_name varchar2(30) := get_application_property(TIMER_NAME);
v_ip_address varchar2(40);
begin
if upper(v_trimer_name) = upper('ip_timer') THEN
v_ip_address := webutil_clientinfo.get_ip_address;
SETCLIENTINFO('Client IP='||v_ip_address);
end if;
end;
======================================================================

6. Call the form


7. Go to SQL*Plus or iSQL*Plus and run the following SQL statement:

======================================================================
select client_info from v$session where client_info is not null;
======================================================================

Sample Code Output


The SQL output in step (7) should be like :


Client IP = x.x.x.x