Solved

XML file access in  Oracle Forms 6i

Posted on 2003-11-09
1
3,345 Views
Last Modified: 2012-06-21
How can I read XML file in Oracle Forms 6i (without using Java features) - need an examples.


0
Comment
Question by:ipesin
1 Comment
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 9714657
If you want only to read the XML file "as is' you can use the Forms/Report package TEXT_IO that reads ASCII flat files.

If this is not your intention then you have to create on the server side a package which will do what you want.


How to Read XML File Stored in Clob Column and Extract Data to Different Table

Pre-requisites
--------------
 
- Oracle RDBMS version is 8.1.6 or higher.
- Oracle JServer is installed.
- XML-SQL (XSU) Utility is installed.
 
XML file ( example.xml )
------------------------
 
The <ROWSET> tag specifies the default tag name for the document.
The <ROW> tag specifies the default tag name for the ROW elements.
 
- - - - - - - - - - - - - - - - File begins here - - - - - - - - - - - - - - - -
<ROWSET>
<ROW>
<DOCID> 91739.1 </DOCID>
<SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using Intermedia </SUBJECT>
<TYPE> PROBLEM </TYPE>
<CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE>
<STATUS> PUBLISHED </STATUS>
<CREATION_DATE> 14-DEC-1999 </CREATION_DATE>
<LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE>
<LANGUAGE> USAENG </LANGUAGE>
</ROW>
</ROWSET>
- - - - - - - - - - - - - - - - File ends here - - - - - - - - - - - - - - - -
 
How to Read XML File Stored in Clob Column and Extract Data to Different Table
------------------------------------------------------------------------------
 
1. Create directory object mapped to physical directory which contain the XML file.
Note: Oracle user should have (at least) read permission for the directory and the XML file.
 
CREATE DIRECTORY XML_DIR AS 'c:\ash';
 
2. Create a table containing a CLOB and insert a row for the XML file.
XML_CLOB stores the xml document in a clob column,  
 
CREATE TABLE xml_clob(
docname    VARCHAR2(100) PRIMARY KEY,
doc        CLOB,
insertdate DATE DEFAULT SYSDATE);
 
3. Create PL/SQL procedure to insert the example.xml file into the  
database table xml_clob.  
 
CREATE OR REPLACE PROCEDURE insertXML (dirname IN VARCHAR2,  
                                      filename IN VARCHAR2)
IS
  xmlfile BFILE;
  myclob  CLOB;
BEGIN
  INSERT INTO xml_clob (docname, doc)  
  VALUES (filename, empty_clob())
  RETURNING doc into myclob;  
   
  -- get a handle to the xml file on the OS
  xmlfile := Bfilename(dirname,filename);
 
  -- open the file  
  DBMS_LOB.fileOpen(xmlfile);
 
  -- copy the contents of the file into the empty clob
  DBMS_LOB.loadFromFile(myclob, xmlfile, dbms_lob.getLength(xmlfile));
 
END insertXML;
 
4. Create a table into which the XML document data has to be loaded after reading it from  
the XML_CLOB table. Note that the column names of the table should match the XML tags.
 
CREATE TABLE XML_DOC (
DOCID         VARCHAR2(10),
SUBJECT       VARCHAR2(100),
TYPE          VARCHAR2(20),
CONTENT_TYPE  VARCHAR2(20),
STATUS        VARCHAR2(20),
CREATION_DATE VARCHAR2(15),
LAST_REVISION_DATE VARCHAR2(15),
LANGUAGE      VARCHAR2(10)
);
 
 
5. Create procedure to read XML stored in clob column of the xml_clob table, and
load extracted data into XML_DOC table.  
 
 
CREATE OR REPLACE PROCEDURE loadxml1 AS
  fil    clob;
  buffer varchar2(1000);
  len    INTEGER;
  insrow INTEGER;
BEGIN
  SELECT doc INTO fil FROM xml_clob WHERE docname='example.xml';
 
  len := DBMS_LOB.GETLENGTH(fil);
  DBMS_OUTPUT.PUT_LINE('length '||len);
 
  DBMS_LOB.READ(fil,len,1,buffer);
  xmlgen.resetOptions;
  insrow := xmlgen.insertXML('xml_doc',buffer);
  DBMS_OUTPUT.PUT_LINE('length ins '||insrow);
 
 EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('In Exception');
  DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
 
end;
 
 
Sample Output
-------------
 
SQL> column subject format a20 truncated
SQL> select * from xml_doc;
 
 
DOCID      SUBJECT              TYPE                 CONTENT_TYPE    STATUS      
                          CREATION_DATE    LAST_REVISION_D  LANGUAGE
---------- -------------------- -------------------- --------------- ----------
91739.1    MTS: ORA-29855, DRG  PROBLEM              TEXT/PLAIN      PUBLISHED  
                       14-DEC-1999           15-JUN-2000            USAENG
 

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now