Home » RDBMS Server » Server Utilities » Import a Flat File into Oracle and update another table
Import a Flat File into Oracle and update another table [message #589440] Fri, 05 July 2013 15:34 Go to next message
firebirdta84
Messages: 1
Registered: July 2013
Junior Member
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

[EDITED by LF: removed superfluous empty lines]

[Updated on: Fri, 05 July 2013 15:40] by Moderator

Report message to a moderator

Re: Import a Flat File into Oracle and update another table [message #589441 is a reply to message #589440] Fri, 05 July 2013 15:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Loader should be ran from the operating system's command prompt (i.e. not SQL*Plus prompt) - did you do that? You second code suggests the opposite.

What does the log file say? It contains useful information which might help to fix issues you have.

Here's a short example which does the loading part. I created a table:
SQL> desc uploadtemp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ENVELOPE                                           NUMBER
 DATERETURNED                                       DATE

SQL>

Control file (test.ctl):
load data
infile 'c:\temp\test.csv'

replace
into table uploadtemp
fields terminated by "'"
(envelope,
 datereturned "to_date(:datereturned, 'dd.mm.yyyy')"
)

Data to be loaded (test.csv):
123'05.07.2013
45'06.07.2013

Loading session:
C:\temp>sqlldr scott/tiger@xe control=test.ctl log=test.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pet Srp 5 22:50:21 2013

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

Commit point reached - logical record count 1
Commit point reached - logical record count 2

C:\temp>

Checking what we've done:
C:\temp>sqlplus scott/tiger@xe

SQL*Plus: Release 11.2.0.2.0 Production on Pet Srp 5 22:51:04 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select * from uploadtemp;

  ENVELOPE DATERETURN
---------- ----------
       123 05.07.2013
        45 06.07.2013

SQL>

Everything seems to be OK.

Now, the comparison part: I created the MANIFEST table whose one record matches one record in the UPLOADTEMP table:
SQL> select * from uploadtemp;

  ENVELOPE DATERETURN
---------- ----------
       123 05.07.2013
        45 06.07.2013

SQL> select * from manifest;

  ENVELOPE DATERETURN
---------- ----------
       123                      --> match
        99                      --> no match

Update statement:
SQL> update manifest m set
  2    m.datereturned = (select u.datereturned
  3                      from uploadtemp u
  4                      where u.envelope = m.envelope
  5                     )
  6    where m.envelope in (select u.envelope
  7                         from uploadtemp u
  8                        );

1 row updated.

SQL> select * from manifest;

  ENVELOPE DATERETURN
---------- ----------
       123 05.07.2013
        99

SQL>
That would be all, I guess.
Re: Import a Flat File into Oracle and update another table [message #589449 is a reply to message #589441] Sat, 06 July 2013 02:23 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I posted the following on the OTN forums:

https://forums.oracle.com/thread/2557654

If your ReturnedFile.txtfile is comma separated then you need TERMINATED BY "," not TERMINATED BY "'" in your control file. If there happens to not be an ending comma in any row, then you also need to add TRAILING NULLCOLS to your control file. You should also use a date format for your date in your control file that corresponds to the date format in your ReturnedFile.txt file, in case it does not match the date format on your system. You need to add a WHERE EXISTS clause to your update statement to prevent any rows that do not match from having the DateReturned updated to a null value. Please see the example below. If this does not help then please do a copy and paste as I did, that includes a few rows of sample data and table structure. It would also help to see your SQL*Loader log file or a SQL*Loader error message. If you can't get SQL*Loader to run properly, then you may have other issues, such as file permissions at the operating system level. There are also other options besides the methods below. For example, you could use an external table, instead of SQL*Loader, if your ReturnedFile.txtfile is on your serer, not your client. You could also use merge instead of update.

SCOTT@orcl_11gR2> host type retfile.ctl
LOAD DATA
INFILE 'ReturnedFile.txt'
APPEND
INTO TABLE UploadTemp
FIELDS TERMINATED BY ","
trailing nullcols
(ENVELOPE
, DATERETURNED date "dd-mm-yyyy")

SCOTT@orcl_11gR2> create table uploadtemp
  2    (envelope         varchar2(15),
  3     datereturned  date)
  4  /

Table created.

SCOTT@orcl_11gR2> create table Manifest
  2    (Envelope         varchar2(15),
  3     DateSentOut   date,
  4     DateReturned  date)
  5  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into manifest values ('env1', sysdate-6, sysdate-4)
  3  into manifest values ('env2', sysdate-5, null)
  4  into manifest values ('env3', sysdate-4, null)
  5  select * from dual
  6  /

3 rows created.

SCOTT@orcl_11gR2> select * from manifest
  2  /

ENVELOPE        DATESENTO DATERETUR
--------------- --------- ---------
env1            30-JUN-13 02-JUL-13
env2            01-JUL-13
env3            02-JUL-13

3 rows selected.

SCOTT@orcl_11gR2> host sqlldr scott/tiger CONTROL=RetFile.ctl LOG=RetFile.log BAD=RetFile.bad

SQL*Loader: Release 11.2.0.1.0 - Production on Sat Jul 6 00:19:38 2013

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

Commit point reached - logical record count 3

SCOTT@orcl_11gR2> select * from uploadtemp
  2  /

ENVELOPE        DATERETUR
--------------- ---------
env2            03-JUL-13
env3            04-JUL-13
env4            05-JUL-13

3 rows selected.

SCOTT@orcl_11gR2> update Manifest m
  2  set m.DateReturned =
  3    (select t.DateReturned
  4     from   UploadTemp t
  5     where  m.Envelope = t.Envelope)
  6  where exists
  7    (select t.DateReturned
  8     from   UploadTemp t
  9     where  m.Envelope = t.Envelope)
 10  /

2 rows updated.

SCOTT@orcl_11gR2> select * from manifest
  2  /

ENVELOPE        DATESENTO DATERETUR
--------------- --------- ---------
env1            30-JUN-13 02-JUL-13
env2            01-JUL-13 03-JUL-13
env3            02-JUL-13 04-JUL-13

3 rows selected.

Previous Topic: restore a package only with original import utility
Next Topic: previleges error
Goto Forum:
  


Current Time: Thu Mar 28 03:53:58 CDT 2024