• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Suggestion regarding reading data from a txt file into DB

Hi,

 wrote a select statement to grab a column by comparing the data in log file as :

Select x, y from info where x in("log.txt")

but it is failing with a error  ora-1795 "Maximum allowed list is 1000".

Can some suggest a workaround for this issue. Using Oracle 10g
0
new_perl_user
Asked:
new_perl_user
  • 6
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
in ("log.txt") isn't Oracle syntax.

Do you have code that dumps the contents of log.txt into the IN statement?

Two work arounds:
load the contents into a global temp table and use:
Select x, y from info where x in (select x,y from temp_table);

or break the IN list up into 1000 or smaller lists and OR them:

Select x, y from info where x in (a,b,c) or x in (d,e,f) ...
0
 
johnsoneSenior Oracle DBACommented:
I believe you could also specify the file as an external table and then use that external table in the subselect:

select x,y from info where x in (select x from ext_table);
0
 
slightwv (䄆 Netminder) Commented:
That might actually be better than a global temp table if the file is on the database server (or visible to the database server)!
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
new_perl_userAuthor Commented:
The file is on a unix server and I am running this query through a perl script..
0
 
slightwv (䄆 Netminder) Commented:
Is the unix server also the database server and is the perl script on this server as well?

If so an external table might work if you have permission to create one:
http://download.oracle.com/docs/cd/E11882_01/server.112/e22490/et_concepts.htm#SUTIL011


0
 
johnsoneSenior Oracle DBACommented:
If the file is on the same server that is running the database, then I would go for the external table.

If not, I would go with the global temporary table.  You could use insert statements from perl, or shell out and use SQL*Loader to get the data into the table.

I keep forgetting about the location restriction on external tables because I always do my scripting on the database server.
0
 
slightwv (䄆 Netminder) Commented:
>>use SQL*Loader to get the data into the table.

sqlloader will be a lot faster to load the data but if they use a global temp table I'm not sure if the rows will be preserved between sqlloader finishing and the perl script connecting.

If I get time later, I'll try it.  I'm curious myself.

Also using the same perl script to load the contents of the text file will keep all the code self contained.
0
 
johnsoneSenior Oracle DBACommented:
Right, that won't work.  When the session disconnects the global temp table will be gone.  You would have to stay in the same session, so inserts would be the way to go.
0
 
slightwv (䄆 Netminder) Commented:
Guess creating a real table and truncating after the script is an alternative that still might be faster.
0
 
johnsoneSenior Oracle DBACommented:
That is an option too.  I try to avoid that one if possible.

If somehow the process gets run twice at the same time, it can create a real mess.

I've definitely used it, but I don't particularly like it.

If you are already parsing the file in perl, then you might as well use a global temporary table and do inserts.  A little safer, in my opinion.  If you haven't written the code to parse the file yet, then go with another option.  SQL*Loader/external tables will do the parsing for you.
0
 
slightwv (䄆 Netminder) Commented:
>>If somehow the process gets run twice at the same time, it can create a real mess.

Agree with all points!  and I too have done it...


If I was going to parse a file to issue multiple inserts to just do a select IN (select) statement, I might just go ahead and build a large select with multiple OR statements with a MOD construct of some type.   Every X rows/records form the file, add a new "OR column in (" piece.

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now