Verify that ORA_CLIENT_IP_ADDRESS is valid only in the system trigger.

To verify that ORA_CLIENT_IP_ADDRESS is valid only in the system trigger, the function of ORA_CLIENT_IP_ADDRESS returns the Client IP address of the VARCHAR2 type. Generally, it is only used in the system trigger. In fact, it is only valid in the system trigger. 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 table used in the experiment and the two trigger objects (one DDL trigger and one system trigger) [SQL] -- 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 log On 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 6 end; 23:38:17 7/Elapsed: 00:00:00. 05 -- create DDL trigger 23:38:19 SYS @ orcl> create or replace trigger ORA_TEST_ddl 23:38:38 2 after ddl ON data Base 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 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. [SQL] -- first test the system trigger and find dbms_output.put_li Ne does not output the 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. in this case, table t01 is [SQL] 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 The function ORA_CLIENT_IP_ADDRESS is valid in the system trigger and the IP address is returned successfully. (PS: You can also see that dbms_output.put_line is invalid in the system trigger and nothing is output.) [SQL] -- test the DDL trigger again, dbms_output.put_line output: 00:10:11 @ orcl> conn cry/cry Connected. 00:11:17 CRY @ orcl> create table cry01 (id number); CRYIP: AA Elapsed: 00:00:00. 07 00:11:47 CRY @ orcl> conn scott/tiger Connected. 00:12:00 SCOTT @ orcl> create table scott01 (id number); SCOTTIP: AA Elapsed: 00:00:00. 0 5 00:12:20 @ orcl> conn sys/as sysdba Connected. 00:12:23 SYS @ orcl> create table sys01 (id number); SYSIP: AA Elapsed: 00:00:00. 04 note that all outputs with AA are output by dbms_output.put_line, which indicates that dbms_output.put_line is valid in DDL triggers. the T01 record table is [SQL] 00:12:33 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 4 CRY [system Trigger] IP: 192.168.123.102 5 CRY [DDL Trigger] IP: AA 6 SCOTT [system Trigger] IP: 192.168.123.102 7 SCOTT [DDL Trigger] IP: AA 8 SYS [system Trigger] IP: 192.168.123.102 9 SYS [DDL Trigger] IP: AA Elapsed: 00:00:00. 02 where IDs are 1, 2, and 3 are the system trigger records triggered when the above three users log on, 4th records the cry users logged on to the test DDL trigger (logon triggers the system trigger) 5th record the DDL trigger record triggered when the cry user creates the table cry01 (at this time, we can find that ORA_CLIENT_IP_ADDRESS has been returned blank, and the null is converted to the character 'a A', you can see the definition of the DDL Trigger.) continue to the next step and find that no IP is recorded for the DDL Trigger, that is, the IP returned by ORA_CLIENT_IP_ADDRESS is empty, obviously, all system triggers have IP addresses. [SQL] 00:18:26 SYS @ orcl> SELECT ORA_CLIENT_IP_ADDRESS FROM DUAL; ORA_CLIENT_IP_ADDRESS ------------------------------------------------------ <null> Elapsed: 00:00:00. 02 although ORA_CLIENT_IP_ADDRESS is a function, direct calling is also Blank-In summary, the ORA_CLIENT_IP_ADDRESS function only plays a role in the system trigger.