0

I have two requirements of loading data in Oracle Tables through SQL Loader utility -

Requirement 1

Two .csv files with same headers defined in both the files. Skip the header from both and load the combined data in the table.

What will be the command to load the data by skipping the headers and stop the process if either of files have errors.

Requirement 2

Two files with attributes spread across in both the files i.e.,
Table Primary key - ID,Name
Cols in First file - ID,Name,Attr1
Cols in Second File - ID,Name,Attr2

Columns in the oracle table where the both the files' data will be loaded
ID,Name,Attr1,Attr2

What will be the best way to load the attributes from both the files in this case? How to handle data integrity scenarios ? i.e., notify or do not load attributes from 2nd file if 1st file corresponding records are bad records.

Thanks in Advance.

Improve this question

2 Answers 2

Reset to default
1

Order the files by id,name:

cat file1.txt | sort > file1.csv
cat file2.txt | sort > file2.csv

If you need to discrd the header use grep -v:

cat file1.txt | grep -v "header id.." | sort > file1.csv
cat file2.txt | grep -v "header id.." | sort > file2.csv

then merge the files using awk:

awk 'BEGIN {FS=","}{getline line < "file1.csv"; print line","$3}' file2.csv > inputSqlLoader.csv

the load the resulting file with sql loader. Use the skip=1 option on sql loader to discard the header, if needed.

To improve performance you might use:

paste -d, file{1..2}.csv | awk 'BEGIN {FS=","}{print $1","$2","$3","$6;}' > inputSqlLoader.csv
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you. But my data files can be big in size already and I would like to keep the original files intact. Any other keyword in .ctl file to skip 1st row of both the files ?
Your original files file1.txt and file2.txt are intact. file1.csv, file2.csv and inputSqlLoader.csv are temporary files that you can delete after sqlloader processing.
0

The way you described it, SQL*Loader is not the tool of your choice. External tables, no the other hand, might be.

Why? Because SQL*Loader will load the 2nd file regardless of errors found in the 1st file. Also, you can't load from two files and "merge" data into a single record in the target table. (OK, its background still is SQL Loader, but that's not the point here).

However, if each of those CSV files represents an external table, then you can access them using SQL or - maybe even better - PL/SQL. As it is a procedural extension to SQL, you'd create a procedure which "loads" (that would be INSERT) the 1st file's contents into the target table. You'll be able to check whether there were any errors and then proceed to the 2nd file, using either UPDATE or MERGE to set the attr2 column's value.

Improve this answer

3 Comments

Thank you so much. I cannot use External Tables as I do not have access to the database server to create a directory or to run the script or to even place the files over there. I have access to another server from where I can ssh the database server.. So as I understand to load data I can only use SQL Loader and wiill have to use same headers in both the files.
All you have to do is talk to your DBA, they will provide required access to you.
Yes they can, but my current system is setup in this way where the database access is provided only under special circumstances. But thank you so much though, I wanted to explore the possibilities with the current available resources

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.