[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

XML file access in  Oracle Forms 6i

Posted on 2003-11-09
1
Medium Priority
?
3,628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

650 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