Use of Oracle System Event function ORA_CLIENT_IP_ADDRESS

ORA_CLIENT_IP_ADDRESSReturns the IP address used to access the oracle database client. The returned IP address may be blank during windows Local logon.

1. ORA_CLIENT_IP_ADDRESS definition:

 
 

  
  
CREATE PUBLIC SYNONYM ORA_CLIENT_IP_ADDRESS FOR SYS.CLIENT_IP_ADDRESS     
  
  
 
  
  
CREATE OR REPLACE FUNCTION SYS.CLIENT_IP_ADDRESS     
  
  
 
  
  
return varchar2 is    
  
  
 
  
  
begin    
  
  
 
  
  
return dbms_standard.client_ip_address;     
  
  
 
  
  
end;     
  
  
 
  
  
CREATE PUBLIC SYNONYM ORA_CLIENT_IP_ADDRESS FOR SYS.CLIENT_IP_ADDRESS  
  
  
 
  
  
CREATE OR REPLACE FUNCTION SYS.CLIENT_IP_ADDRESS  
  
  
 
  
  
return varchar2 is  
  
  
 
  
  
begin  
  
  
 
  
  
return dbms_standard.client_ip_address;  
  
  
 
  
  
end; 
 
 

2. ORA_CLIENT_IP_ADDRESS example:

 
 

  
  
Create or replace trigger sysevent_trig
  
  
 
  
  
AFTER LOGON
  
  
 
  
  
ON DATABASE
  
  
 
  
  
BEGIN
  
  
 
  
  
Insert into dw. ora_fun_logs (id, value)
  
  
 
  
  
Values (dw. ora_fun_logs_s.nextval,
  
  
 
  
  
Ora_sysevent | ': ORA_CLIENT_IP_ADDRESS --' | NVL (ora_client_ip_address, 'n'/'));
  
  
 
  
  
END;
  
  
 
  
  
Show errors
  
  
 
  
  
Truncate table dw. ora_fun_logs;
  
  
 
  
  
Conn dw/dw @ dw
  
  
 
  
  
Set line 80
  
  
 
  
  
Column value format a60
  
  
 
  
  
Select * from dw. ora_fun_logs;
  
  
 
  
  
Create or replace trigger sysevent_trig
  
  
 
  
  
AFTER LOGON
  
  
 
  
  
ON DATABASE
  
  
 
  
  
BEGIN
  
  
 
  
  
Insert into dw. ora_fun_logs (id, value)
  
  
 
  
  
Values (dw. ora_fun_logs_s.nextval,
  
  
 
  
  
Ora_sysevent | ': ORA_CLIENT_IP_ADDRESS --' | NVL (ora_client_ip_address, 'n'/'));
  
  
 
  
  
END;
  
  
 
  
  
Show errors
  
  
 
  
  
Truncate table dw. ora_fun_logs;
  
  
 
  
  
Conn dw/dw @ dw
  
  
 
  
  
Set line 80
  
  
 
  
  
Column value format a60
  
  
 
  
  
Select * from dw. ora_fun_logs;
  
  
 
  
  
22:11:08 sys @ dw> create or replace trigger sysevent_trig
  
  
 
  
  
22:11:09 2 AFTER LOGON
  
  
 
  
  
22:11:09 3 ON DATABASE
  
  
 
  
  
22:11:09 4 BEGIN
  
  
 
  
  
22:11:09 5 insert into dw. ora_fun_logs (id, value)
  
  
 
  
  
22:11:09 values (dw. ora_fun_logs_s.nextval,
  
  
 
  
  
22:11:09 7 ora_sysevent | ': ORA_CLIENT_IP_ADDRESS --' | NVL (ora_client_ip_address, 'n'/'));
  
  
 
  
  
22:11:09 END;
  
  
 
  
  
22:11:09/
  
  
 
  
  
Trigger created
  
  
 
  
  
Used time: 00: 00: 00.04
  
  
 
  
  
At 22:11:10 sys @ dw> truncate table dw. ora_fun_logs;
  
  
 
  
  
The table is truncated.
  
  
 
  
  
Used time: 00: 00: 00.03
  
  
 
  
  
22:11:18 sys @ dw> conn dw/dw @ dw
  
  
 
  
  
Connected.
  
  
 
  
  
At 22:11:24 dw @ dw> set line 80
  
  
 
  
  
22:11:24 dw @ dw> column value format a60
  
  
 
  
  
At 22:11:24 dw @ dw> select * from dw. ora_fun_logs;
  
  
 
  
  
ID VALUE
  
  
 
  
  
----------------------------------------------------------------------
  
  
 
  
  
17 LOGON: ORA_CLIENT_IP_ADDRESS--N/
  
  
 
  
  
18 LOGON: ORA_CLIENT_IP_ADDRESS--127.0.0.1
 
 

Here is an introduction to Oracle System Event function ora_client_ip_address. I hope this will bring you some benefits. Thank you!