Tuesday, April 27, 2010

Oracle Tips and tricks

SELECT query has limitations when you have more then 1000 fields to compare in the list.
One alternative is to use SQL Loader as follows :
1. Create a file called /tmp/test.dat :
100001
100002
100003
100004
100005
100006

2. create table temp
(number varchar2(10));

or truncate table temp

3. Create a file test.ctl in /tmp dir with following contents
LOAD DATA
INFILE test.dat
INTO TABLE temp
(number position(1:6) CHAR)

run command from the unix box where oracle client is installed from the /tmp dir:
sqlldr username/password@serviceid/sid control=test.ctl

Now, you can use the temp table in your query as follows :
For IN clause:
Select * from table_a a, temp t
where a.number = t.number;
for NOT IN clause:
Select * from table_a a, temp t
where a.number = t.number(+) and t.number is null;

No comments: