Home » Developer & Programmer » JDeveloper, Java & XML » Getting data from a file and pass it to a table
Getting data from a file and pass it to a table [message #184458] Wed, 26 July 2006 10:51 Go to next message
Messages: 4
Registered: July 2006
Junior Member
Is it posible to load data from a file(XML,CSV etc) and after that to load it into tables?
If posible I would apreciate a sample code.
Thank you.
Re: Getting data from a file and pass it to a table [message #184606 is a reply to message #184458] Thu, 27 July 2006 03:48 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can create an External Table, which basically takes the file on disk and makes it appear as a table in Oracle.


Create a dicrectory c:\temp on your server.
Create a file called test_file.txt containing the follwing data

1,some text,<row_1><column_1>value 1</column_1></row_1>
2,some more text,<row_2><column_1>value 2</column_1></row_2>
3,Different text,<row_3><column_1>value 3</column_1></row_3>

Execute the following commands from SQL*Plus:

create or replace directory utl_extdir as 'C:\TEMP';

drop table ext_table;

create table ext_table(
       id          number,
       description varchar2(50),
       xml         varchar2(50)
    organization external(
      default directory utl_extdir
      access parameters(
         records delimited by newline
         fields terminated by ','
         (id           char,
          description  char,
          xml          char)
      location ('test_file.txt')

create or replace view ext_table_xml as
select id
      ,xmltype(xml) xml
from   ext_table;

This will give you a table EXT_TABLE that contain all the data from the flat file, and a view EXT_TABLE_XML that shows the XML column of that data as XML.
The syntax used for describing the data in the flat file all comes from SQLLDR which is the other real option for loading data.

For more info, see the CREATE TABLE statement, including another example
Previous Topic: XML Parsing returns null values
Next Topic: Restrict DBMS_METADATA.GET_XML output
Goto Forum:

Current Time: Mon Oct 24 15:49:21 CDT 2016

Total time taken to generate the page: 0.06232 seconds