SQL Loader, CSV, Importing and Exporting

[cas85]

On Windows 2000

Could someone outline the basic steps I should follow to do the following.

1) I need to get 128 records out of one table in Oracle.
2) I need to bring in the 128 records into another database.
3) When bringing in the 128 records there are 18 records that are duplicate that I would like to kick out.

Do I use SQL Loader to get the records in and out of oracel using a CSV file? and then let index contraints reject the duplicate records? or should I just edit the csv file?

 

[dbadmin]

If you know the 18 records it is better to edit the CSV file and load it using sql loader. If the number of duplicates are more, go for unique constraint to reject it automatically.

dbadmin

 

[SantaMufasa]

CAS,

You said, "Do I use SQL*Loader to get the records in and out of Oracle using a CSV file?"

SQL*Loader does not "...get the records...out of Oracle." SQL*Loader is uni-directional: flat-file input INTO Oracle only; no output from Oracle.

To produce comma-separated values (CSV) files, I use the following code which I run from a file called "GenASCII.sql". (It produces comma-separated values and encloses the values inside of double quotes just in case there are commas embedded in the column values themselves.).

Section 1 -- GenASCII.sql code:

set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Section 2 -- Sample invocation for a short, demo file:

SQL> @genascii
Enter the table to ASCII: s_dept
Enter the flat file to write: yada.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool yada.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
||',"'||REGION_ID|| '"'
from s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "yada.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"

Output file = "yada.txt"

SQL>

Then, as DBADMIN suggests, if there are just a few duplicate rows, I would then edit the resulting ASCII CSV file to get rid of duplicates before reading in the CSV file to the target database.

Let us know if this helps resolve your needs.

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:56 (06May04) UTC (aka "GMT" and "Zulu"), 01:56 (06May04) Mountain Time)

 

[aryeh1010]

I agree with everything that SantaMufasa says, but I do have one thing that I do in addition.

I always create a table to hold all the values from the ascii file that is being loaded. I can then do any data manipulations that I want; constraint checking, generate log files of bad records, prior to the final clean insert into the target table.

I only like to do direct loads with sql*loader, and clean inserts into the target table. This is my personal preference.

Aryeh Keefe

 

[cas85]

Thank you all for your posts. I am using all of the suggestions and code samples. Your pointers have saved me weeks of development time and testing.

 

[ksandbergfl]

There's an easier way.

Use Oracle EXPORT to dump the table to a file. RTFM on how to export just a table.

Using SQL*PLus or TOAD, create the table in the target database with the unique constraint.

Then, on the target system use Oracle IMPORT to load the table. Any duplicate keys will get rejected... all the other data will go in fine.

Don't have to write one line of code, or even a SQL*Loader control file.