Solved

Sql Loader

Posted on 2007-12-05
4
604 Views
Last Modified: 2013-12-18
We have 10 column in the datafile and 3 column  in our staging table. We need to pick the 7th column out of the data file and append into the 2nd column of the staging table using sql loader.  This is what we have for syntax so far.

LOAD DATA
INFILE 'C:\DATA.txt'
BADFILE 'C:\DATA.bad'
DISCARDFILE 'C:\DATA.dsc'
APPEND INTO TABLE "STAGING_TABLE"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
  (COLUMN_1,  COLUMN_2,   COLUMN_3)
 
This syntax seems to insert the first 3 columns from the datafile to the 3 columns in the staging tables, it won't allow us to pick the 7th column for the datafile to insert into the 2nd column in the staging table.  This does not seem to work. Any help would be great
0
Comment
Question by:rrisal
[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
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
joebednarz earned 500 total points
ID: 20415271
I would avoid using Sql Loader.  Try, instead, using externally organized tables:

SQL> create table MY_EXTERNAL_TABLE
  2      ( empl_id varchar2(3),
  3        last_name varchar2(50),
  4        first_name varchar2(50),
  5        ssn varchar2(9),
  6       email_addr varchar2(100),
  7        years_of_service number(2,0)
  8      )
  9      organization external
 10      ( default directory ORACLE_DIRECTORY_OBJECT
 11        access parameters
 12        ( records delimited by newline
 13          fields terminated by ','
 14        )
 15        location ('my_data_file.csv')  
 16    );

From there, you simply can do an INSERT accessing the "external" data in an SQL query.
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20415290
In case you need it:

CREATE OR REPLACE DIRECTORY oracle_directory_object
AS 'C:\my_directory';

GRANT READ ON DIRECTORY oracle_directory_object TO user_name;

Then make sure your data file is in the directory specified...
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

636 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