SQL*Plus using a variable for a subselect (keeping spool working)

Question

I got the following script that does actually the following, it uses an email-address to

• search for that user
• search for messages from that user
• search for Entries of that user
• puts the findings to a file

currently that is working but now I would like to do the following: Replace the sub select "SELECT ID FROM DBSCHEMA.USERS WHERE EMAIL ='&SEARCH'" with a kind of variable to not put (and execute) this statement multiple times in the script.

But what ever I tried did not work. (Started using DECLARE block - than the spool was not working, end everything else just broke the script)
I'm using a oracle database - but i would like to stick to generic commands if possible.

DEFINE SEARCH='search@example.org';

spool OUTPUTFILE.out
prompt Start Searching by mail '&SEARCH'

SELECT
    'ID;NAME;LAST_LOGIN;EMAIL_ADDRESS;FIRST_NAME;LAST_NAME;BLABLA;LANGUAGE_ID;'
FROM
    DUAL;

SELECT
    ID||';'||NAME||';'||LAST_LOGIN||';'||EMAIL||';'||FIRST_NAME||';'||LAST_NAME||';'||BLABLA||';'||LANGUAGE_ID||';'
FROM
    DBSCHEMA.USERS
WHERE
    EMAIL ='&SEARCH';

prompt Feedback messages
prompt ID;SUBJECT;MESSAGE;TIMESTAMP;

SELECT
    ID||';'||SUBJECT||';'||MESSAGE||';'||TIMESTAMP||';'
FROM
    DBSCHEMA.SITE_FEEDBACK
WHERE
    USER_ID = (SELECT ID FROM DBSCHEMA.USERS WHERE EMAIL ='&SEARCH');

prompt Other Entries
prompt ID;TITLE;BLABLA;TIMESTAMP;

SELECT
    ID||';'||TITLE||';'||BLABLA||';'||TIMESTAMP||';'
FROM
    DBSCHEMA.SITE_ENTRY
WHERE
    USER_ID = (SELECT ID FROM DBSCHEMA.USERS WHERE EMAIL ='&SEARCH');

spool off;

Answer 1

How does search get populated? Also, what version of SQL*Plus do you have?

In SQL*Plus you could declare a variable and populate it in PL/SQL, like this (I've used the HR demo schema):

define search='VJONES';

var user_id number

begin
    select employee_id into :user_id from employees where email = '&search';
exception
    when no_data_found then
        raise_application_error(-20001, 'No user found with email ''&search''.');
    when too_many_rows then
        raise_application_error(-20002, 'Multiple users found with email ''&search''.');
end;
/

select phone_number from employees where employee_id = :user_id;

PHONE_NUMBER
--------------------
650.501.4876

(You don't strictly need the exception handler, but it will improve handling of invalid or duplicated data.)

You could also stick with define variables:

column user_id new_value user_id
select employee_id as user_id from employees where email = '&search';

   USER_ID
----------
       195

1 row selected.

Now you have defined and populated &user_id and you can use it in queries:

select phone_number from employees where employee_id = &user_id;

PHONE_NUMBER
--------------------
650.501.4876

I don't understand the parts of your question about things not working or using generic commands. If you could clarify those I might be able to add to my answer.