cancel
Showing results for 
Search instead for 
Did you mean: 

sql help throu unix login

viseshu
Frequent Advisor

sql help throu unix login

Hi,
I have a list of values in a file. I have to load these values into database. Before this, a check needs to be performed for the existence of these values in the database already. Im logging into oracle database from HP-UX box.
Can I use a command like sqlplus /
select * from table where column in $(cat $FILE)?????
Note, this file contains about million records which needs to be checked against database.
9 REPLIES
Steven E. Protter
Exalted Contributor

Re: sql help throu unix login

Shalom,

while read -r DATATLINE
do
FIELD1=$(echo $DATALINE | awk '{print $1}')
FIELD2=$(echo $DATALINE | awk '{print $2}')
#repeat for other fields.
# write data (sql plus statement)
done < file

Note the code isn't all that efficient, running awk over and over again, but its very easy to understand.

If the data is colon delimited for example, change awk to awk -F:

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Piergiacomo Perini
Trusted Contributor

Re: sql help throu unix login

Hi viseshu,
as far sa i know this select is not possible;
maybe is better to load data from file in a "staging" table , make comparison with data in target table and then insert new values.

my 2 cent
best regards
pg
Ralph Grothe
Honored Contributor

Re: sql help throu unix login

It hasn't become clear to me whether you simply wish to check for the existence of certain column names (aka table fields?, sorry I'm no DBA), or do you really wish to check if the values for records of these fields are the same as in your comparison file?
My first assumption however would make more sense to me.
Then this should be fairly easy.
I think to recall that you can visualize a table's structure with something like
(is this Oracle SQL?)
desc tablename;
Whatever, every brand of DB has some sort of data dictionary that lets you query tables' field names at no cost at all.
However, if you really need to select over milions of records, I would suggest to reduce this to a single SQL statement to avoid repetative costly queries.
But as said, I lack any DBMS knowledge to advise.

Madness, thy name is system administration
spex
Honored Contributor

Re: sql help throu unix login

Hello,

First, use SQL*Loader to get the data into a staging area. Then conditionally update the existing table using standard PL/SQL.

http://www.oreilly.com/catalog/orsqlloader/chapter/ch01.html

PCS
Yogeeraj_1
Honored Contributor

Re: sql help throu unix login

hi viseshu,

what version of oracle database are you running?
you may look into external table feature.

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: sql help throu unix login

hi again,

Assuming your data is in a file as /tmp/emp.dat

one simple example would be as follows:

SQL> create or replace directory data_dir as '/tmp/';
Directory created.

SQL> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /
Table created.

You can then just query the table using:
SQL> select count(*) from external_table;

COUNT(*)
----------
410340

SQL>

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
viseshu
Frequent Advisor

Re: sql help throu unix login

Hi Yogeeraj,
Not sure whether i can create a table as you suggested. Is there any other way where in i can check for duplicate values against database?? I have all the list of those values in one file.
Sandman!
Honored Contributor

Re: sql help throu unix login

There are lot of options in front of you for weedind out duplicates in your data. Either 1) create a temp table inside the database or 2) create an external table to hold the file's million or so records or 3) dump contents of your DB table into a flat file and then use the OS utilities to weed out dups. Choice 1) is probably your best bet as you can verify dups and insert them within the same SQL.

~hope it helps
Yogeeraj_1
Honored Contributor

Re: sql help throu unix login

hi again,

there are two approaches that can choose to perform this:
1. Bring the data in the OS file to the database
- implies using SQLLOADER or EXTERNAL TABLES

2. Bring the data in the tables to the OS as an OS file.
- implies exporting the table as a text file.


After you have done one of the above, then only you can do the comparison.

The choice is yours.

let us know if you need any further assistance.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)