Verify that ORA_CLIENT_IP_ADDRESS is valid only in the system trigger.

The function of the ORA_CLIENT_IP_ADDRESS function returns the Client IP address of the VARCHAR2 type. Generally, it is only used in system triggers. In fact, it is only valid in system triggers.

The function of the ORA_CLIENT_IP_ADDRESS function returns the Client IP address of the VARCHAR2 type. Generally, it is only used in system triggers. In fact, it is only valid in system triggers.

The function of the ORA_CLIENT_IP_ADDRESS function returns the Client IP address of the VARCHAR2 type. Generally, it is only used in system triggers. In fact, it is only valid in system triggers.

It is often said that the IP address obtained by using ORA_CLIENT_IP_ADDRESS is null, which is probably the cause.

DDL triggers and system triggers are used here for comparison.

First, prepare the tables used in the experiment and the two trigger objects (one DDL trigger and one system trigger)

-- Create a record table
23:37:15 SYS @ orcl> create table t01 (id number, nn varchar2 (40 ));
Elapsed: 00:00:00. 05
-- Create a system trigger
23:37:53 SYS @ orcl> create or replace trigger ORA_TEST
23:38:16 2 after logon database
23:38:16 3 BEGIN
23:38:16 4 dbms_output.put_line (ORA_LOGIN_USER | 'IP: '| NVL (ORA_CLIENT_IP_ADDRESS, 'A '));
23:38:16 5 insert into t01 select nvl (max (id), 0) + 1, ORA_LOGIN_USER | '[system Trigger] IP:' | NVL (ORA_CLIENT_IP_ADDRESS, 'A ') from t01;
23:38:16 end;
23:38:17 7/
Elapsed: 00:00:00. 05
-- Create a DDL trigger
At 23:38:19 SYS @ orcl> create or replace trigger ORA_TEST_ddl
23:38:38 2 after ddl ON database
23:38:38 3 BEGIN
23:38:38 4 dbms_output.put_line (ORA_LOGIN_USER | 'IP: '| NVL (ORA_CLIENT_IP_ADDRESS, 'A '));
23:38:38 5 insert into t01 select nvl (max (id), 0) + 1, ORA_LOGIN_USER | '[DDL Trigger] IP:' | NVL (ORA_CLIENT_IP_ADDRESS, 'A ') from t01;
23:38:38 end;
23:38:38 7/
Elapsed: 00:00:00. 11

Specifically, the ORA_LOGIN_USER is added to identify the login user, and the DDL Trigger and system Trigger strings to identify which Trigger is triggered.

-- First test the system trigger and find that dbms_output.put_line has no output
00:06:11 @ orcl> conn cry/cry
Connected.
00:09:28 CRY @ orcl> conn scott/tiger
Connected.
At 00:09:37 SCOTT @ orcl> conn sys/as sysdba
Connected.

The t01 table is

00:09:40 SYS @ orcl> select * from t01;

ID NN
--------------------------------------------------
1 CRY [system Trigger] IP: 192.168.123.102
2 SCOTT [system Trigger] IP: 192.168.123.102
3 SYS [system Trigger] IP: 192.168.123.102
Elapsed: 00:00:00. 02

,