Monday, May 14, 2012
Parse xml documents directly from filesystem without loading into Oracle database
First, create a database directory object and grant it read,write privilages.
Create or replace directory xmlload as '/tmp/xmlload';
grant read,write on XMLLOAD to <username>;
-- to avoid some ora-600 errors in 11.2.0.2 version of DB.
alter session set events='31156 trace name context forever, level 0x400';
If a DBA is tasked to read a xml file, he will like to see the output as rows and columns after parsing base on xquery condition...
E.g
WITH vendxml_col AS
(select XMLTYPE(bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8'))vend_doc from dual)
-- Above WITH used to simulate your table/data read from file.
SELECT a.name,a.operation,a.searchable
FROM vendxml_col,
XMLTABLE('for $i in /import_data/template/template_attribute
where $i/@name eq "UPN"
or $i/@searchable eq "n"
return $i'
PASSING vendxml_col.vend_doc
COLUMNS
name varchar2(300) PATH '@name',
operation varchar2(500) PATH '@operation',
searchable varchar2(1) PATH '@searchable') a;
However, if you want a xml output as a result of your parsing you can simply read xmltype instead of xmltable.
select xmlquery(
'for $i in /import_data/template/template_attribute
where $i/@name eq "VIJAY"
or $i/@operation_flag=fn:true()
return $i'
PASSING d.xml_doc returning content) the_result
from
(select xmltype (bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8')) xml_doc from dual) d;
Oracle database link with easy connect, RAC, bequeath connection.
The old way of creating a database link in oracle database is to use the syntax
create database link linkname connect to <username> identified by <password> using 'connect_string';
where connect string maps to a entry in tnsnames.ora.
If you did not want to depend on any files in Oracle local directries, then you had the workaround of replacing the connect string with the complelete description as follows.
create database link linkname connect to <username> identified by <password> using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.101)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = VIJAY)))';
Now, you can use the same concept and connect using the easy connect syntax as follows.
create database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay';
If this is a RAC environment and you need to access one specific instance, then the syntax would be
create database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay/vijay1';
If for some reason, you need to create a database link to read data from the same host (loopback ) you could use a bequeath connection in the database as follows.
create database link beq_link connect to <username> identified by <password>
USING
'(description=(address=(protocol=beq)(program=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle))
(CONNECT_DATA = (SERVICE = orclprd)))';
Subscribe to:
Posts (Atom)