I got the following script that does actually the following, it uses an email-address to
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='[email protected]';
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;
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.