Re: sql query read from text file

On Jun 19, 2001 at 11:15:46PM, Vladimir Begun wrote:
> On Jun 19, 2001 at 12:17:32PM, Seema Singh wrote:
> > Hi
> > If i have one file a.txt and I want to put that content in where clause of
> > sql statement then how can I do it.
> > Example :
> > a.txt content
> > 98989
> > 67676
> > 898989
> > Select x,y,z from abc
> > where x in (98989,67676,898989)
> > /
>
> create file named modify.ed, put the following commands:
>
> 1,$-1s/$/,/
> $s/$/);/
> w
> q
>
> your SQL*Plus script
>
> SET FEEDBACK OFF TERMOUT OFF
> SPOOL _launch.sql
> PROMPT SELECT x, y, z FROM abc WHERE x IN (
> SPOOL OFF
> !ed -s a.txt < modify.ed; cat a.txt >> _launch.sql
> REM a.txt is modified!!!
> SET FEEDBACK ON TERMOUT ON
> REM @@_launch.sql

Brief note: if it's not acceptable to modify a.txt you can do it like:

create file named correct.ed, put the following commands:

1,$-1s/$/,/
$s/$/);/
1,$p
q

script:

SET FEEDBACK OFF TERMOUT OFF
SPOOL _launch.sql
PROMPT SELECT x, y, z FROM abc WHERE x IN ( SPOOL OFF
!ed -s a.txt < correct.ed >> _launch.sql REM a.txt is modified!!!
SET FEEDBACK ON TERMOUT ON
REM @@_launch.sql

-- 
Vladimir Begun               | You are in the hall of the mountain king.
http://vbegun.net/           | 
http://vbegun.net/wap/       | 
me_at_vbegun.net                | 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vladimir Begun
  INET: jester_at_whale.sunbay.crimea.ua

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).