Q
Problem solve Get help with specific problems with your technologies, process and projects.

Pulling data from a text file to query

In this SQL tip, expert Karen Morton explains how one can pull data from a text file into a usable format so that it's available for an SQL query.

I have the following query I’m running, please see below:

 Select PLAN_ID, EL_ID, NIIN, ITEM_NAME, PROJECT_CD,
CASE WHEN LCMC_DOC_NO IS NULL THEN MRODOC ELSE LCMC_DOC_NO END AS "DOCUMENT NUMBER"
FROM EQLIST_VIEW
WHERE PLAN_ID IN (736,1011)
AND LCMC_DOC_NO IN ('W56HZV0327W501','W52H090313X006','W52H090314X026',
'W52H090314X027','W52H090318X049','W52H090320X003','W56HZV0351M012',
'W56HZV0351M032','W56HZV0351M035','W919AD0334L091','W919AD0334L092',
'W919AD0352L025','W919AD0358L012','W919AD0358L017','W919AD0353L020',
'W56HZV0350M001')
ORDER BY NIIN, “DOCUMENT NUMBER”

My question is I have over 4000 DOC_NO and I would like to pull these from a text file on my C drive. I have tried various ways to pull this information in and I keep getting error message that says "Syntax error near 'C'" on line 5.

See the new query below:

 Select PLAN_ID, EL_ID, NIIN, ITEM_NAME, PROJECT_CD,
CASE WHEN LCMC_DOC_NO IS NULL THEN MRODOC ELSE LCMC_DOC_NO END AS "DOCUMENT NUMBER"
FROM EQLIST_VIEW
WHERE PLAN_ID IN (736,1011)
AND LCMC_DOC_NO IN C:/Users/aj.moon/Desktop/TEMP/A5A_DOCNUM.TXT

Can you help?

You didn't mention which version of Oracle you are using so I'll go with an answer that should work from Oracle Version 9 on up. The first issue is that you can't directly use the text file. The first thing you need to do is to make this data available inside the database. The first things that come to mind are that you could either load the data with SQL*Loader or you could create an external table. I'd use an external table for simplicity.

External tables allow Oracle to query data that is stored outside the database in flat files. You can access any data stored in formats that could be loaded by SQL*Loader. You can't perform INSERT/UPDATE/DELETE actions on the external tables, but you can use them in queries. Once you create the external table, you can use it in views and create synonyms using them. You will typically see external tables used for ETL processing in order to avoid staging the data into an actual table. But, if you plan to frequently query an external table, you should consider loading the data into an actual table instead as you can't index an external table and thus you'll not be able to do much about performance improvement otherwise.

First, create the external table definition and associate it to the file. The file will have to be placed in a directory that is accessible to Oracle. This means you likely won't be able to have it on your local drive, but in a directory on your database server. Then create a DIRECTORY object in Oracle which points to the location of the file:

 CREATE OR REPLACE DIRECTORY ext_tab_dir AS 'C:\temp\';

Then, you will create the metadata for the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax as follows:

 CREATE TABLE docnum_ext
(
doc_no varchar2(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS
(FIELDS TERMINATED BY ',' )
LOCATION ('A5A_DOCNUM.TXT')
)
;

Since you didn't provide the exact format of your text file, I'm assuming that it is simply a series of values that are comma-separated. If you have a different format you may need to adjust the ACCESS PARAMETERS in this definition (see the Oracle docs for detailed syntax).

With the external table created, you can test it out by issuing a simple query:

 SQL> SELECT *
2 FROM docnum_ext;

DOC_NO
----------------------------
W56HZV0327W501
W52H090313X006
W52H090314X026
W52H090314X027
W52H090318X049
W52H090320X003
W56HZV0351M012
W56HZV0351M032
W56HZV0351M035
W919AD0334L091
W919AD0334L092
W919AD0352L025
W919AD0358L012
W919AD0358L017
W919AD0353L020
W56HZV0350M001
... and so on ...

Once the table is functioning correctly, you can query against it:

 Select PLAN_ID, EL_ID, NIIN, ITEM_NAME, PROJECT_CD,
CASE WHEN LCMC_DOC_NO IS NULL
THEN MRODOC
ELSE LCMC_DOC_NO
END AS "DOCUMENT NUMBER"
FROM EQLIST_VIEW
WHERE PLAN_ID IN (736,1011)
AND LCMC_DOC_NO IN (SELECT doc_no FROM docnum_ext)
ORDER BY NIIN, "DOCUMENT NUMBER"
;

This example is intended to provide you with a guideline to follow and isn't intended to work "as is". You'll have to make sure to create the directory and external table definition to accommodate your exact needs. Hopefully however, this will provide you with the starting point you need that will lead you to your final solution.

Hope it helps!

Karen

Have an SQL question for Karen Morton? Send an e-mail to editor@searchoracle.com

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

2 comments

Send me notifications when other members comment.

Please create a username to comment.

The original question did not state if the database resides on the same machine as the file on the C: drive, or if it's on a remote server somewhere on the network. If the server is remote, it will not be able to read the user's local C: drive.

That being said, though, this is a good tip. I use it all the time. For example, I have a view on the UNIX/Linus systems that allows me to query the /etc/passwd file. Sometimes I need to validate users that way.

Good tip!

Cheers,
Mike
Cancel
The other solution to the problem is to use the SQL Loader. I personally like and use Karen's solution much better.

The previous poster had a question about how to handle the file because she doesn't like the idea of putting the data on the Database Server.

The answer to this is to put the data file on a Network Share and allow access from the Database Server. That way you won't compromise the Security of the Database Server.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close