No question at this time
DBA Top 10
1 M. Cadot 20800
2 A. Kavsek 14900
3 M. Hidayathullah ... 10800
4 B. Vroman 7900
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1600
10 A. Hudspith 1500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48052
Total active users1733
Act. users last 24h10
Act. users last hour1
Registered user hits last week395
Registered user hits last month1204
Go up

sqlloader with FIELD NAMES FIRST FILE in control file
Next thread: Sql tunning
Prev thread: SQL help on Analytic Function 'Rolling' Subtraction from a Total

Message Score Author Date
According to the documentation http://docs.oracle....... Tim Boles Oct 04, 2017, 17:43
Hi Tim, can you post a control file example? Ca...... Ales Kavsek Oct 05, 2017, 11:08
<pre> LOAD DATA INFILE '/u01/app/oracle/admin/OC...... Tim Boles Oct 05, 2017, 16:26
Hi Tim, did you try with my suggestion...I mean...... Ales Kavsek Oct 05, 2017, 23:50
Sorry...yes I attempted that way as well. <pr...... Tim Boles Oct 06, 2017, 02:23
Hi Tim, a demo... <pre> create table emp (i...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Oct 06, 2017, 12:03
I figure this is a difference between 12.2 and 12....... Tim Boles Oct 06, 2017, 14:09
Hi, nope. It works the same in 12.1... <pre>...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek Oct 06, 2017, 14:39
So my assumption that it would act in the same man...... Tim Boles Oct 06, 2017, 18:04

Follow up by mail Click here


Subject: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Tim Boles, United States
Date: Oct 04, 2017, 17:43, 12 days ago
Os info: Linux 7
Oracle info: 12.1.0.1
Message: According to the documentation http://docs.oracle.com/database/121/SUTIL/GUID-2FAAD351-7526-4A44-93FE-C43444DB83D8.htm#SUTIL4216

you should be able to use FIELD NAMES clause to specify field order.

(but can not find a usage example)

I can do it with SQL*Loader Express on the command line using FIELD_NAMES but I wanted to do it as part of the control file. I continually get a syntax error.

Has anyone gotten the FIELD NAMES clause to work within a SQL*Loader control file?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Ales Kavsek, Slovenia
Date: Oct 05, 2017, 11:08, 12 days ago
Message: Hi Tim,

can you post a control file example? Can't remember that I ever used this SQL*Loader feature before, but my understanding of the syntax is that you need to specify (at least) two files (INFILE) and then tell SQL*loader that first file contains column names, something like this:

LOAD DATA
INFILE file1.csv
INFILE file2.csv
FIELD NAMES FIRST FILE
APPEND INTO TABLE T
...
...


Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Tim Boles, United States
Date: Oct 05, 2017, 16:26, 11 days ago
Message:

LOAD DATA
INFILE '/u01/app/oracle/admin/OCP12C/dpdump/withfieldnames.dat'
INFILE '/u01/app/oracle/admin/OCP12C/dpdump/nonames.dat'
INTO TABLE testdba.emp
TRUNCATE
FIELD NAMES FIRST


The test....

$ sqlldr control=empfield.txt
Username:testdba
Password:

SQL*Loader: Release 12.1.0.1.0 - Production on Thu Oct 5 14:21:35 2017

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

SQL*Loader-350: Syntax error at line 6.
Expecting "(", found "field".
FIELD NAMES FIRST
^



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Ales Kavsek, Slovenia
Date: Oct 05, 2017, 23:50, 11 days ago
Message: Hi Tim,

did you try with my suggestion...I mean...

LOAD DATA
INFILE '/u01/app/oracle/admin/OCP12C/dpdump/withfieldnames.dat'
INFILE '/u01/app/oracle/admin/OCP12C/dpdump/nonames.dat'
FIELD NAMES FIRST FILE
...


Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Tim Boles, United States
Date: Oct 06, 2017, 02:23, 11 days ago
Message: Sorry...yes I attempted that way as well.


[OCP12C] oracle@ocp:/media/sf_Downloads
$ cat empfield.ctl
LOAD DATA
INFILE '/u01/app/oracle/admin/OCP12C/dpdump/withfieldnames.dat'
INFILE '/U01/app/oracle/admin/OCP21C/dpdump/nonames.dat'
INTO TABLE testdba.emp
TRUNCATE
FIELDS TERMINATED BY ','
FIELD NAMES FIRST FILE

$ sqlldr control=empfield.ctl
Username:testdba
Password:

SQL*Loader: Release 12.1.0.1.0 - Production on Fri Oct 6 00:21:08 2017

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Expecting "(", found "field".
FIELD NAMES FIRST FILE
^
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Ales Kavsek, Slovenia
Date: Oct 06, 2017, 12:03, 11 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Tim,

a demo...


create table emp (i int, ii int);

cmd> type withfieldnames.dat
ii,i
2,1
2,1

cmd> type nonames.dat
2,1
2,1

cmd> type test.ctl

LOAD DATA
FIELD NAMES FIRST FILE
INFILE withfieldnames.dat
INFILE nonames.dat
INTO TABLE emp
TRUNCATE
FIELDS CSV WITH EMBEDDED
(
i,
ii
)

cmd>sqlldr control=test.ctl userid=ales@testdb
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Pet Okt 6 12:00:52 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 4

Table EMP:
4 Rows successfully loaded.

Check the log file:
test.log
for more information about the load.


SQL> select * from emp;

I II
---------- ----------
1 2
1 2
1 2
1 2




Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Tim Boles, United States
Date: Oct 06, 2017, 14:09, 10 days ago
Message: I figure this is a difference between 12.2 and 12.1...they probably did not have it ready in 12.1.

Thanks for showing your test.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Ales Kavsek, Slovenia
Date: Oct 06, 2017, 14:39, 10 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi,

nope. It works the same in 12.1...


sqlldr control=test.ctl userid=ales@testdb
Password:

SQL*Loader: Release 12.1.0.2.0 - Production on Pet Okt 6 14:35:22 2017

Copyright (c) 1982, 2015, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 4

Table EMP:
4 Rows successfully loaded.

Check the log file:
test.log
for more information about the load.


Make sure that you specify "FIELD NAMES FIRST FILE" before INFILE.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlloader with FIELD NAMES FIRST FILE in control file
Author: Tim Boles, United States
Date: Oct 06, 2017, 18:04, 10 days ago
Message: So my assumption that it would act in the same manner as the SQL*Loader FIELD_NAMES options was incorrect. I was not expecting to have to load up the field names still within the ( ) in the control file. Thanks for testing this out and helping me figure it out.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here