Reading text file line by line in SQL*Plus

How to read a text file from Oracle SQL*plus?

If there is a way to read from a bat file and pass the variable as a single line of text file, and simultaneously called from SQL*plus sql file, this opt开发者_如何学运维ion is ok.

You can try to use Oracle External Tables.

For example, you have next file:

$ cat employee.dat

smith   clerk       800
scott   analyst     3000
adams   clerk       1100
miller  clerk       1300

Create external table:

create table employees (
ename varchar2(10),
title varchar2(10),
salary number(8))
organization external(
type oracle_loader default directory work_dir
    access parameters (record delimited by new line fields(
        ename char(10), title char(10), salary char(8)))
location ('employee.dat'))
parallel

Now you can use this file as SQL-table:

select * from employees;

If the list is already comma separated in the test file, then you can probably do something as simple as this in SQL*Plus ...

get id.txt append ) 0 select * from my_table where id in ( /