External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.
Step1: Create XXAA_XML directory in bin location and change permissions
$ mkdir XXAA_XML
$ chmod 777 XXAA_XML
$ cd XXAA_XML
Step2 : Upload the xml file to XXAA_XML directory
$ ls –lrt
12939_04112014_045006.xml
12939_05112014_081219.xml
12939_05112014_081216.xml
12939_05112014_081221.xml
Step3 : Run the below command to create XXAA_XML_XT_DATA.dat file in bin folder
This SQLLoader file must be created that contains the paths to the XML documents to be able to load the needed XML files. This can be achieved via the following statement (while your command prompt is in the XXAA_XML directory that contains the XML documents):
$ ls -l *.xml | awk '{print "\/XXAA_XML\/"$9}' > /u01/apps88/apps/apps_st/appl/CUSTOM/12.0.0/bin/XXAA_XML_XT_DATA.dat
$ cat XXAA_XML_XT_DATA.dat
/XXAA_XML/12939_04112014_045006.xml
/XXAA_XML/12939_05112014_081216.xml
/XXAA_XML/12939_05112014_081219.xml
/XXAA_XML/12939_05112014_081221.xml
As you can see, the XXAA_XML_XT_DATA.dat file lists all XML files starting with “/XXAA_XML/…”. This will be our fixed starting point for our Oracle Directory alias we will create later on so the “XXAA_XML_XT_DATA.dat” file can be used in our external table.
Change the permissions for XXAA_XML_XT_DATA.dat file
$ chmod 777 XXAA_XML_XT_DATA.dat
Step4 : Create a directory for bin folder
CREATE OR REPLACE DIRECTORY XXAA_XT_DIR AS '/u01/apps88/apps/apps_st/appl/CUSTOM/12.0.0/bin';
select * from all_directories
where 1=1
and directory_name = 'XXAA_XT_DIR'
Step5 : Create External table with above directory 'XXAA_XT_DIR' and SQL Loader file ‘XXAA_XML_XT_DATA.dat’
CREATE TABLE XXAA_XML_XT_DOCUMENTS
(
FILENAME VARCHAR2(100), -->XML Filename
DOCUMENT CLOB -->XML File Data
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY XXAA_XT_DIR --> Directory for '/u01/apps88/apps/apps_st/appl/CUSTOM/12.0.0/bin' location( Created the directory in Step4 )
ACCESS PARAMETERS (FIELDS (FILENAME CHAR(100)) COLUMN TRANSFORMS (DOCUMENT FROM lobfile (FILENAME)))
LOCATION ('XXAA_XML_XT_DATA.dat') -->Stores the list of xml file names (in step3 will create this file in bin folder)
)
Via the XXAA_XT_DIR directory alias the external table is able to find the XML documents specified in the contents of “XXAA_XML_XT_DATA.dat”.
SELECT * FROM XXAA_XML_XT_DOCUMENTS
Step6: Create a staging table based on the XML file
<?xml version="1.0" encoding="utf-8"?>
<RPS>
<Header>Oracle apps88</Header>
<Search>
<User>Oracle apps88</User>
<DateSearched>Tuesday, September 30, 2014</DateSearched>
<TimeSearched>10:00:02 PM</TimeSearched>
<AlertType>YELLOW FLAG</AlertType>
<SearchType>Exact</SearchType>
<Name>N/A</Name>
<Company>Beltech</Company>
<Address>Tres Arroyos 1241 Captial Federal Buenos Aires</Address>
<Country>AR</Country>
<Comment>SUPPLIER_1892\REMIT TO_10487</Comment>
</Search>
</RPS>
CREATE TABLE XXAA_XML_XT_STG
(
FILE_TYPE VARCHAR2(40 BYTE), --> XML (Static)
FLAG VARCHAR2(40 BYTE), --> AlertType
UNIQUE_REFERENCE VARCHAR2(90 BYTE), --> Comment
CONTACT_NAME VARCHAR2(90 BYTE), --> Name
ORGANIZATION_NAME VARCHAR2(120 BYTE), --> Company
ADDRESS_INFO VARCHAR2(1000 BYTE), --> Company
COUNTRY VARCHAR2(60 BYTE), --> Country
CREATION_DATE TIMESTAMP(0) DEFAULT sysdate
);
SELECT * FROM XXAA_XML_XT_STG;
Step7 : Create select statement get the column values from the XML documents in the external table
SELECT
'XML',
XMLCAST(XMLQUERY('/RPS/Search/AlertType' passing DOCUMENT returning content) AS VARCHAR2(40)),
XMLCAST(XMLQUERY('/RPS/Search/Comment' passing DOCUMENT returning content) AS VARCHAR2(90)),
XMLCAST(XMLQUERY('/RPS/Search/Name' passing DOCUMENT returning content) AS VARCHAR2(90)),
XMLCAST(XMLQUERY('/RPS/Search/Company' passing DOCUMENT returning content) AS VARCHAR2(120)),
XMLCAST(XMLQUERY('/RPS/Search/Address' passing DOCUMENT returning content) AS VARCHAR2(1000)),
XMLCAST(XMLQUERY('/RPS/Search/Country' passing DOCUMENT returning content) AS VARCHAR2(60))
FROM (SELECT XMLTYPE(DOCUMENT) DOCUMENT FROM XXAA_XML_XT_DOCUMENTS)
The used “select XMLTYPE(DOCUMENT) DOCUMENT from XML_XT_DOCUMENTS” in the from clause will get the XML documents from the external table and feed it via the DOCUMENT column alias into the XMLQUERY bit of the statement via the PASSING clause.
The XPATH expression ‘/RPS/Search/AlertType’ now gets the needed ‘AlertType’ element values of the XML document input and is transformed via the XMLCAST operator into a VARCHAR2(100) datatype.
Step8: Create a view for above query
CREATE OR REPLACE VIEW XXAA_XML_XT_DOCUMENTS_V(FILE_TYPE,FLAG,UNIQUE_REFERENCE,CONTACT_NAME,ORGANIZATION_NAME,ADDRESS_INFO,COUNTRY)
AS
SELECT
'XML',
XMLCAST(XMLQUERY('/RPS/Search/AlertType' passing DOCUMENT returning content) AS VARCHAR2(40)),
XMLCAST(XMLQUERY('/RPS/Search/Comment' passing DOCUMENT returning content) AS VARCHAR2(90)),
XMLCAST(XMLQUERY('/RPS/Search/Name' passing DOCUMENT returning content) AS VARCHAR2(90)),
XMLCAST(XMLQUERY('/RPS/Search/Company' passing DOCUMENT returning content) AS VARCHAR2(120)),
XMLCAST(XMLQUERY('/RPS/Search/Address' passing DOCUMENT returning content) AS VARCHAR2(1000)),
XMLCAST(XMLQUERY('/RPS/Search/Country' passing DOCUMENT returning content) AS VARCHAR2(60))
FROM (SELECT XMLTYPE(DOCUMENT) DOCUMENT FROM XXAA_XML_XT_DOCUMENTS)
;
Step9 : Insert the data into above created staging table
INSERT INTO XXAA_XML_XT_STG(FILE_TYPE,FLAG,UNIQUE_REFERENCE,CONTACT_NAME,ORGANIZATION_NAME,ADDRESS_INFO,COUNTRY)
SELECT * FROM XXAA_XML_XT_DOCUMENTS_V;
COMMIT;
SELECT * FROM XXAA_XML_XT_STG;
No comments:
Post a Comment