Using SQL*Loader to get around ORA-01795

Posted by on Sep 13, 2011 in Databases, Oracle | No Comments

So one of your higher-ups just sent you an Excel spreadsheet with tens of thousands of items, and he or she wants you to query Oracle and get the results right away to resolve the crisis of the day.  Unfortunately, trying to query against this huge list of values gets you this fun error:

ORA-01795: maximum number of expressions in a list is 1000

Which, honestly, is one of the most honest and straightforward ORA codes. So what are you going to do about it?  You can either break your query into separate statements with 1,000 expressions each, or you can make life a lot easier and load what you need into a scratch table using the Oracle SQL*Loader utility (aka sqlldr).  This will let you query against data within the database.

SQL*Loader, unsurprisingly, loads data from a flat file into a table in an Oracle database.  Of course, one of the first things you’ll need to do is to create the table where you want this data to be loaded.  This table will of course be different, depending on what data you are working with:

-------------------------------------------------
-- BEGIN SCRATCH_t.sql

create table scratch_t
  (important_txt   varchar2(30) not null);

-- END SCRATCH_t.sql
-------------------------------------------------

We’ll also need a control file.  This tells sqlldr how to load our data into the database.  Note that the table we just created is referenced here.

-------------------------------------------------
-- BEGIN scratch.ctl

LOAD DATA
INFILE '*'
--INFILE 'scratch_values.txt'
--BADFILE 'scratch.bad'
--DISCARDFILE 'scratch.dsc'
APPEND
INTO TABLE SCRATCH_t
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
IMPORTANT_TXT
)

-- END scratch.ctl
-------------------------------------------------

Once the table and control file have been created, and the data is in a text file, you are ready to roll.  Invoke SQL*Loader from the command line with appropriate credentials, referencing your input and control files:

[oradood@bigserver ~]$ sqlldr user/pass data=scratch_values.txt,control=scratch.ctl

While the program is running, it will output how many records have been processed.  Once it completes, don’t forget to check the logfile (try scratch.out) for any errors.

 

Reference

SQL*Loader Command-Line Reference