Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Importing a Flat File to Oracle and updating another table

1023002Jul 5 2013 — edited Jul 5 2013

Hey everyone,

I am a newbie with Oracle, and I've tried for the last 2 days to solve this problem below. But all my searches and attempts have failed.

I have a text file called ReturnedFile.txt. This is a comma separated text file that contains records for two fields.... Envelope and Date Returned.

At the same time, I have a table in Oracle called Manifest. This table contains the following fields:

Envelope

DateSentOut
DateReturned

I need to write something that imports the ReturnedFile.txt into a temporary Oracle table named UploadTemp, and then compares the data in the Envelope field from UploadTemp with the Envelope field in Manifest. If it's a match, then the DateReturned field in Manifest needs updated with the DateReturned field in UploadTemp.

I've done this with SQL Server no problem, but I've been trying for two days to make this work with Oracle and I can't figure it out. I've been trying to use SQL*Loader, but I can't even get it to run properly on my machine.

I did create a Control file, saved as RetFile.ctl. Below is the contents of the CTL file:

LOAD DATA
INFILE 'C:\OracleTest\ReturnedFile.txt'

APPEND
INTO TABLE UploadTemp
FIELDS TERMINATED BY "'"
(
ENVELOPE,
DATERETURNED
)

If I could get SQL*Loader running, below is the code I came up with to import the text file and then to do the compare to the Manifest table and update as appropriate:

sqlldr UserJoe/Password123 CONTROL=C:\OracleTest\RetFile.ctl LOG=RetFile.log BAD=RetFile.bad

update Manifest m set m.DateReturned =
(select t.DateReturned
    from UploadTemp t
    where m.Envelope = t.Envelope
*)

That's all I got. As I said, I can't find a way to test it and I have no idea if it's even close.

PLEASE...can anyone assist me? Am I even close on this thing?

Joe

This post has been answered by Barbara Boehmer on Jul 5 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 2 2013
Added on Jul 5 2013
1 comment
529 views