Hey Connor and Chris,
I want to analyse or dig
log.xml of Oracle Listener Log with
XMLTABLE. Afterwards I found Thomas Kyte's this thread -
https://asktom.oracle.com/pls/apex/asktom.search?tag=xmldb What he replies to somebody attracts me, have a look at the following code,
ops$tkyte@ORA920.US.ORACLE.COM> host cat tkyte.xml
<Employee>
<Name>Shailesh</Name>
<Age>23</Age>
</Employee>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace directory xmldir as '/export/home/tkyte/'
2 /
Directory created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> drop table xmltable;
Table dropped.
ops$tkyte@ORA920.US.ORACLE.COM> create table xmltable of xmltype;
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace function get_Document(filename varchar2) return clob is
2 xbfile bfile;
3 xclob clob;
4 begin
5 xbfile := bfilename('XMLDIR',filename);
6 dbms_lob.open(xbfile);
7
8 dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
9 dbms_lob.loadfromfile(xclob,xbfile, dbms_lob.getlength(xbfile));
10 dbms_lob.close(xbfile);
11 return xclob;
12 end;
13 /
Function created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into xmltable values ( xmltype( get_document( 'tkyte.xml' ) ) );
1 row created.
ops$tkyte@ORA920.US.ORACLE.COM> select * from xmltable;
SYS_NC_ROWINFO$
-----------------------------------------------------------------------------------------------------------------------------------
<Employee>
<Name>Shailesh</Name>
<Age>23</Age>
</Employee>
ops$tkyte@ORA920.US.ORACLE.COM>
It worked for me -- Are your sure the file has what you think in it? Also, this could be character set related -- if you have a UTF8 instance on windows -- it might be (and in that case, contact support and reference in progress bug #2535898)
So I mimic to write my example,
SYS@test> create or replace directory xmldir as '/u01/app/oracle/diag/tnslsnr/test/listener/alert/';
Directory created.
SYS@test> drop table xmltable;
drop table xmltable
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@test> create table xmltable of xmltype;
Table created.
SYS@test> create or replace function get_Document(filename varchar2) return clob is
2 xbfile bfile;
3 xclob clob;
4 begin
5 xbfile := bfilename('XMLDIR', filename);
6 dbms_lob.open(xbfile);
7
8 dbms_lob.createtemporary(xclob, TRUE, dbms_lob.session);
9 dbms_lob.loadfromfile(xclob, xbfile, dbms_lob.getlength(xbfile));
10 dbms_lob.close(xbfile);
11 return xclob;
12 end;
13 /
Function created.
SYS@test>
SYS@test> insert into xmltable values (xmltype(get_document('log.xml')));
insert into xmltable values (xmltype(get_document('log.xml')))
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '?'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
Could you help me to trouble-shooting it?
In fact it's my intention to dig real
IP addresses connecting to DB from
log.xml. Almost all of stuff/content on log.xml looks like this,
<msg time='2019-06-27T03:57:19.244+08:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='test'
host_addr='xxx.xxx.xxx.xxx' version='1'>
<txt>27-JUN-2019 03:57:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=36080)) * establish * test * 0
</txt>
</msg>
<msg time='2019-06-27T03:57:19.265+08:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='test'
host_addr='xxx.xxx.xxx.xxx'>
<txt>27-JUN-2019 03:57:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=36082)) * establish * test * 0
</txt>
</msg>
......
Could you have a good example of digging ip address from it with XMLTABLE (because it's xml format)?
Thank you very much.
Best Regards
Quanwen Zhao
This will be most likely be a character set conversion issue, because "loadfromfile" does not handle charactersets.
eg
SQL> declare
2 xbfile bfile;
3 xclob clob;
4 x xmltype;
5 begin
6 xbfile := bfilename('TEMP','listener.xml');
7 dbms_lob.open(xbfile);
8
9 dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
10 dbms_lob.loadfromfile(xclob,xbfile, dbms_lob.getlength(xbfile));
11 dbms_lob.close(xbfile);
12
13 x := xmltype(xclob);
14 end;
15 /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00210: expected '<' instead of '┐'
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 13
versus
SQL> set serverout on
SQL> declare
2 xbfile bfile;
3 xclob clob;
4 x xmltype;
5
6 dest_offset integer := 1;
7 src_offset integer := 1;
8 src_csid number := nls_charset_id('UTF8');
9 lang_context integer := dbms_lob.default_lang_ctx;
10 warning integer;
11 begin
12 xbfile := bfilename('TEMP','listener.xml');
13 dbms_lob.open(xbfile);
14
15 dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
16 dbms_lob.loadclobfromfile(xclob,xbfile, dbms_lob.getlength(xbfile),dest_offset, src_offset, src_csid, lang_context, warning);
17 dbms_lob.close(xbfile);
18
19 x := xmltype(xclob);
20
21 dbms_output.put_line(x.getclobval());
22 end;
23 /
<msg time='2019-06-27T03:57:19.244+08:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='test'
host_addr='xxx.xxx.xxx.xxx' version='1'>
<txt>27-JUN-2019 03:57:19 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=36080)) * establish * test * 0
</txt>
</msg>