|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
Registered: June 2006
Location: Sunny North Yorkshire, ho...
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(
default directory utl_extdir
records delimited by newline
fields terminated by ','
create or replace view ext_table_xml as
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