Append output file content in sql select query in where clause as a condition

I have a requirement where there is a select query and I have a spooled output.txt file. I want to use this output in select query to put in where clause condition as shown in below example. Can it be achieved using awk or sed or echo command to append that output content in this select query? Would appreciate if you help on this requirement.

cat output.txt

EQ7472993

EU2874748

Expected result.

select 'alter system kill session '''||sid||','||serial#||''' immediate;',program,trunc(last_call_et/60) last_call_et_minutes,module, action,ecid,username,schemaname,client_identifier, process, machine, sql_id, status,blocking_session_status,event,state from v$session where ecid = 'EQ7472993';

I am trying to use awk and sed. But awk inside awk command wont work, also query is complex with number of single quotes.

Solution

I like MT0's answer, but if you really want to get sed to work, see e.g. How to escape single quote in sed? I think the most reliable way is to replace ' with hex code \x27 like so:

sed 's/\(.*\)/select \x27alter system kill session \x27\x27\x27||sid||\x27\x27,\x27\x27||serial#||\x27\x27\x27 immediate;\x27,program,trunc\(last_call_et\/60\) last_call_et_minutes,module, action,ecid,username,schemaname,client_identifier, process, machine, sql_id, status,blocking_session_status,event,state from v$session s where ecid=\x27\1\x27/g;' output.txt

You don't need awk since there's only 1 column in your output file.

Alternately, if you expect to frequently read your output.txt file into SQL queries, you might want to create an external table for it:

CREATE TABLE output_file
(
   ecid VARCHAR2(64)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
     DEFAULT DIRECTORY some_directory
     ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
                       FIELDS
                           TERMINATED BY ','
                           OPTIONALLY ENCLOSED BY '"'
                           LDRTRIM
                           MISSING FIELD VALUES ARE NULL)
     LOCATION('output.txt'))
    REJECT LIMIT UNLIMITED;
    
select 'alter system kill session '''||sid||'',''||serial#||''' immediate;',program,trunc(last_call_et/60) last_call_et_minutes,module, action,ecid,username,schemaname,client_identifier, process, machine, sql_id, status,blocking_session_status,event,state 
from v$session s
join output_file o on o.ecid = s.ecid;

This assumes that output.txt is located in an Oracle directory named "some_directory"