- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- sql help throu unix login
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-25-2007 08:07 PM
тАО04-25-2007 08:07 PM
sql help throu unix login
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-25-2007 08:16 PM
тАО04-25-2007 08:16 PM
Re: sql help throu unix login
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-25-2007 08:16 PM
тАО04-25-2007 08:16 PM
Re: sql help throu unix login
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-25-2007 10:05 PM
тАО04-25-2007 10:05 PM
Re: sql help throu unix login
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-25-2007 11:40 PM
тАО04-25-2007 11:40 PM
Re: sql help throu unix login
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2007 01:28 AM
тАО04-26-2007 01:28 AM
Re: sql help throu unix login
what version of oracle database are you running?
you may look into external table feature.
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2007 01:53 AM
тАО04-26-2007 01:53 AM
Re: sql help throu unix login
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2007 02:11 AM
тАО04-26-2007 02:11 AM
Re: sql help throu unix login
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2007 04:11 AM
тАО04-26-2007 04:11 AM
Re: sql help throu unix login
~hope it helps
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-26-2007 06:38 PM
тАО04-26-2007 06:38 PM
Re: sql help throu unix login
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