Concatenate is not working for content of Oracle unified_audit_trail field SQL

I am trying to add (concat) some string to the content of the field SQL_TEXT of Oracle view unified_audit_trail. The concatenation is NOT done - string is not added.

The testing script is below.

declare 
v_SP_record         clob;
cursor c1 is select * from unified_audit_trail
where sql_text is not null
order by event_timestamp;

begin
  
for rec in c1 loop
v_SP_record := 
'USER: '   || rec.dbusername || ' '  
|| 'TIME: '   || to_char(rec.event_timestamp) || ' ' 
|| 'SQL_TEXT: '  || rec.sql_text  || '<CLOSE>' ;
dbms_output.put_line(v_SP_record);
end loop;
  
end;

Checking the output I can see that the string part <CLOSE> is not added to the v_SP_record.

An example of the output (some rows out of many in unified_audit_trail) is as below:

USER: SYS TIME: 15-NOV-21 01.37.46.289000 AM SQL_TEXT: -- Created on 6/20/2019 by ADMINISTRATOR 
declare 
begin

 dbms_audit_mgmt.clean_audit_trail(
  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  USE_LAST_ARCH_TIMESTAMP => FALSE 
  );
  
end;

USER: OMEGACAEVDBA TIME: 15-NOV-21 01.42.49.679000 AM SQL_TEXT: select mdsys.GetMdsysEvent() from dual
USER: OMEGACAEVDBA TIME: 15-NOV-21 01.42.49.710000 AM SQL_TEXT: select sys.dbms_standard.dictionary_obj_type from dual
USER: OMEGACAEVDBA TIME: 15-NOV-21 01.42.49.741000 AM SQL_TEXT: select mdsys.GetMdsysEvent() from dual
USER: OMEGACAEVDBA TIME: 15-NOV-21 01.42.49.741000 AM SQL_TEXT: select sys.dbms_standard.dictionary_obj_type from dual
USER: OMEGACAEVDBA TIME: 15-NOV-21 01.42.49.757000 AM SQL_TEXT: -- Name = RUN1
grant drop any table to OMEGACAEVDEV1 with admin option

The <CLOSE> string is NOT added after rec.sql_text. This behavior has been verified on Oracle 12c R2, 18c and 19c

What kind of characters in the content of SQL_TEXT can cause a concatenation failure? And how to remove them - or get the right result?

best regards

Altin

CodePudding user response：

The concatenation is happening. It appears that sql_text is terminated by a null character, which has been noted with other values and results. After concatenation that null character is in the middle of your generated string.

Essentially you're ending up with:

USER: ... SQL_TEXT: select mdsys.GetMdsysEvent() from dual<NUL><CLOSE>
                                                          ^^^^^
                                        null character, ASCII 0

How that is handled depends on the client (some clients will not show the full concatenated string; others will show it but you won't be able to copy it; others might just show it without it doing anything odd) and what you do with it.

As a null character is used to mark the end of a variable-length string in many places (e.g. C strings; but not internally in Oracle varchars) it's not surprising that it causes your generated string - after your concatenation - to be truncated somewhere down the line. Everything after it is discarded or at least ignored and not used in further processing - in your client, or utl_tcp, or both.

You can replace the null character, either after concatenation with:

v_SP_record := <your current concatenation>;
v_SP_record := replace(v_SP_record, chr(0), null)

or before concatenation with:

v_SP_record := ... || replace(rec.sql_text, chr(0), null) || '<CLOSE>';

Or you can trim it from the raw sql_text value, before concatenation, with:

v_SP_record := ... || rtrim(rec.sql_text, chr(0)) || '<CLOSE>';

or:

v_SP_record := ... || trim(trailing chr(0) from rec.sql_text) || '<CLOSE>';

I'd probably use one of the trim options, and the last one is perhaps most self-explanatory for someone trying to understand the code later (though a comment explaining why it's there is still a good idea of course, whichever method you use).