📄 loadfiletooracletable.htm
字号:
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>Loading a Flat File to an Oracle Table (Java Developers Almanac Example)
</TITLE>
<META CONTENT="Patrick Chan" NAME="AUTHOR">
<META CONTENT="Code Examples from The Java Developers Almanac 1.4" NAME="DESCRIPTION">
<META CONTENT="Addison-Wesley/Patrick Chan" NAME="OWNER">
<META CONTENT="3/20/02" NAME="revision">
<META CONTENT="no-cache" HTTP-EQUIV="Pragma">
<LINK href="/almanac.css" media="screen" type="text/css" rel="stylesheet">
</HEAD>
<BODY>
<TABLE CELLSPACING="0" CELLPADDING="0" BORDER="0">
<TR>
<TD></TD>
</TR>
</TABLE>
<br>
<TABLE CELLSPACING="0" CELLPADDING="0" BORDER="0">
<TR>
<TD></TD>
</TR>
<TR>
<TD rowspan="3"><A HREF="/?l=ex"><IMG BORDER="0" ALIGN="BOTTOM" HSPACE="10" SRC="/egs/almanac14a.jpg"></A></TD><TD VALIGN="top">
<h1>The Java Developers Almanac 1.4</h1>
<br>
Order this book from <a href="/cgi-bin/scripts/redirect.pl?l=ex&url=http://www.amazon.com/exec/obidos/ASIN/0201752808/xeo">Amazon</a>.
</TD>
</TR>
<TR>
<TD align="right" valign="bottom">
<FORM method="get" action="/cgi-bin/search/find.pl">
<INPUT size="25" name="words" type="text"><INPUT value="Search" type="submit">
</FORM>
</TD>
</TR>
</TABLE>
<HR color="#6666cc">
<TABLE CELLSPACING="0" CELLPADDING="0" BORDER="0">
<TR>
<TD valign="top"><script type="text/javascript">
<!--
google_ad_client = "pub-6001183370374757";
google_ad_width = 120;
google_ad_height = 600;
google_ad_format = "120x600_as";
google_ad_channel = "4777242811";
google_ad_type = "text_image";
google_color_border = "FFFFFF";
google_color_bg = "FFFFFF";
google_color_link = "6666CC";
google_color_url = "6666CC";
google_color_text = "000000";
//--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script></TD><TD> </TD><TD valign="top">
<DIV ALIGN="LEFT">
<A HREF="/">Home</A>
>
<A HREF="../index.html">List of Packages</A>
>
<A HREF="../java.sql/pkg.html">java.sql</A><font color="#666666" class="xsmall-font">
[73 examples]
</font>
>
<B><A HREF="../java.sql/pkg.html#Importing and Exporting">Importing and Exporting</A></B><font color="#666666" class="xsmall-font">
[4 examples]
</font>
</DIV><P>
<h3>e294. Loading a Flat File to an Oracle Table</h3>
When loading a flat file into Oracle, the fields in the flat file
should be separated by delimiters such as tabs or commas. The
delimiter can be specified in the control file. The loader will map
the fields in the file to the columns in the table. The first field
is stored in the first column; the second field is stored in the
second column, etc. If there are more fields than columns in the
table, the remaining fields will be ignored. If there are less fields
than columns in the table, the missing fields will be inserted as
<code>null</code> values in the associated columns.
<P> This example loads a flat file called <code>infile.dat</code> to an
Oracle table named <code>oracle_2_table</code> with two columns of type
<code>INTEGER</code> and <code>VARCHAR(20)</code>. To load, run the <code>sqlldr</code>
command from the shell as follows:
<pre> shell> sqlldr userid=<font color="#0066ff"><i>userid@sid</i></font>, control=<font color="#0066ff"><i>ctrl_file.txt</i></font>, log=<font color="#0066ff"><i>log</i></font>, errors=100
123\tString1
234\tString2
</pre>
The contents of the control file <code>ctrl_file.txt</code> are:
<pre> load data
infile 'infile'
append into table oracle_2_table
fields terminated by ' '
trailing nullcols
(col1 decimal(13,0),
col2 varchar(40))
</pre>
To specify a different terminator, such as a comma,
change the fourth line above to:
<pre> fields terminated by ','
If <code>oracle_2_table</code> is already defined, you can use the following syntax for
the control file <code>ctrl_file.txt</code>:
load data
infile 'infile'
append into table oracle_2_table
fields terminated by ' '
trailing nullcols
(col1,
col2)
</pre>
<P><table width="600" CELLSPACING="0" CELLPADDING="2" BORDER="0">
<tr>
<td bgcolor="#6666cc" align="center"><font color="#ffffff">
<b>Related Examples</b></font></td>
</tr>
</table>
e292. <a class="eglink" href="LoadFileToMySqlTable.html?l=rel">
Loading a Flat File to a MySQL Table
</a>
<br>
e293. <a class="eglink" href="ExportOracleTableToFile.html?l=rel">
Exporting an Oracle Table to a Flat File
</a>
<br>
e295. <a class="eglink" href="ExportMySqlTableToFile.html?l=rel">
Exporting a MySQL Table to a Flat File
</a>
<br>
<table width="600" CELLSPACING="0" CELLPADDING="2" BORDER="0">
<tr>
<td align="left">
<br>
See also:
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Batching">
Batching
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Connections">
Connections
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Database%20Meta%20Data">
Database Meta Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Deleting%20Data">
Deleting Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Drivers">
Drivers
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Inserting%20and%20Updating%20Data">
Inserting and Updating Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Oracle%20OBJECTs">
Oracle OBJECTs
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Oracle%20VARRAYs">
Oracle VARRAYs
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Procedures%20and%20Functions">
Procedures and Functions
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Retrieving%20Data">
Retrieving Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Scrollable%20Result%20Sets">
Scrollable Result Sets
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Tables">
Tables
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Updatable%20Result%20Sets">
Updatable Result Sets
</a>
</td>
</tr>
</table>
<br>
<br>
<FONT class="xsmall-font">
© 2002 Addison-Wesley.
</FONT></TD><TD> </TD><TD valign="top"><A href="http://compositesw.com/devzone?ref=javaalmanac"><IMG alt="Click Here" height="600" width="120" border="0" src="/csw_oad_120x600_final.gif"></A></TD>
</TR>
</TABLE>
</BODY>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<META CONTENT="NO-CACHE" HTTP-EQUIV="PRAGMA">
</HEAD>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -