topic Re: sql help throu unix login in Operating System - HP-UX

sql help throu unix login

viseshu — Thu, 26 Apr 2007 03:07:04 GMT

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.

Re: sql help throu unix login

Steven E. Protter — Thu, 26 Apr 2007 03:16:12 GMT

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

Re: sql help throu unix login

Piergiacomo Perini — Thu, 26 Apr 2007 03:16:48 GMT

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

Re: sql help throu unix login

Ralph Grothe — Thu, 26 Apr 2007 05:05:16 GMT

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.

Re: sql help throu unix login

spex — Thu, 26 Apr 2007 06:40:16 GMT

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

Re: sql help throu unix login

Yogeeraj_1 — Thu, 26 Apr 2007 08:28:06 GMT

hi viseshu,

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

hope this helps!

kind regards
yogeeraj

Re: sql help throu unix login

Yogeeraj_1 — Thu, 26 Apr 2007 08:53:21 GMT

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

Re: sql help throu unix login

viseshu — Thu, 26 Apr 2007 09:11:26 GMT

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.

Re: sql help throu unix login

Sandman! — Thu, 26 Apr 2007 11:11:41 GMT

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

Re: sql help throu unix login

Yogeeraj_1 — Fri, 27 Apr 2007 01:38:40 GMT

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