I think I figured out how to decode the AWI user settings stored in the INI table. In rows where
INI_Section
begins with “ECC.”,
INI_Value
contains
base64-encoded fragments of XML. For a set of rows having the same
INI_Idnr
,
INI_IDText
, and
INI_Section
, if you combine all of the XML fragments in order of
INI_Key
, you will get the whole XML file.
Here is an Oracle SQL query that will fetch the a user's AWI settings. Just change
MYUSER to the name of the user.
-- https://erikwramner.wordpress.com/2010/02/23/coding-and-decoding-base64-in-plsql/
create or replace function decode_base64(p_clob_in in clob) return blob is
v_blob blob;
v_result blob;
v_offset integer;
v_buffer_size binary_integer := 48;
v_buffer_varchar varchar2(48);
v_buffer_raw raw(48);
begin
if p_clob_in is null then
return null;
end if;
dbms_lob.createtemporary(v_blob, true);
v_offset := 1;
for i in 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size) loop
dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
v_buffer_raw := utl_encode.base64_decode(v_buffer_raw);
dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
v_offset := v_offset + v_buffer_size;
end loop;
v_result := v_blob;
dbms_lob.freetemporary(v_blob);
return v_result;
end decode_base64;
-- https://stackoverflow.com/questions/40526132/how-convert-clob-to-blob-in-oracle
CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
select OH_Name,INI_Idnr,INI_IdText,INI_Section,
DBMS_XMLGEN.CONVERT(rtrim(
xmlagg(
xmlelement( e,blob_to_clob(decode_base64(INI_Value)) || ',' )
).extract( '//text()').getClobVal(),','
),1) as AWI_Setting_XML_Fragment
from INI join OH on OH_Idnr = INI_Idnr
where INI_Type = 'U'
and OH_Name like 'S46JZ8%'
and INI_Section like 'ECC.%'
group by OH_Name,INI_Idnr,INI_IdText,INI_Section
order by OH_Name,INI_Idnr,INI_IdText,INI_Section
Update 2020.11.11: I updated the SQL query to combine the XML fragments.