CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Exuberant
Exuberant

Reputation: 199

Oracle sql - Read data from a file for IN clause

Is it possible to read data from a file to supply the data for IN clause?

SQL> SELECT a,b from TABLE123 where type=10 and values IN('file.txt');

The file.txt has list of values.

I cannot use a subquery because the table on which the subquery is to be applied in on a different database.

EDIT: I would prefer not to create a temporary table

Upvotes: 3

Views: 10811

Answers (3)

Sentinel
Sentinel

Reputation: 6449

If you don't mind copying and editing your text file, you can copy the text file to file.sql, add SELECT a,b from TABLE123 where type=10 and values IN( to the beginning of the file, and ); to the end of the file and add commas and quotes as needed to each line of the file.

Then from SQL*Plus you can just run the file:

SQL> @file.sql

Otherwise no, there's no way to do it without temporarily getting the file data into a table of some sort. @MaxU referenced the method I would choose to use.

Upvotes: 0

Beege
Beege

Reputation: 665

Directly as stated, no. Somewhere a table-like entity must be defined.

Upvotes: 0

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210972

assuming that you have copied the "file.txt" file to the Oracle server (under: 'ext_tab_data' directory):

CREATE TABLE countries_ext (
  country_code      VARCHAR2(5),
  country_name      VARCHAR2(50),
  country_language  VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      country_code      CHAR(5),
      country_name      CHAR(50),
      country_language  CHAR(50)
    )
  )
  LOCATION ('Countries1.txt','Countries2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Please find details here...

Here is your SQL:

SELECT a,b from TABLE123 
where type=10 
and values IN(select country_code from countries_ext);

PS off course you can replace your files, which would replace the contents of your external table...

Upvotes: 2

Related Questions