Given two tables
USERS
UID NAME
1 KEN
ADRESS
AID UID CITY
1 1 LONDON
I'd like to have an Oracle SQL Developer script that outputs two result tables just like if I'd had entered two select statements one after another.
This does NOT work, I couldn't assign the u_id variable:
select UID into u_id from USERS where NAME='KEN';
select * from USERS where UID = u_id;
select * from ADRESS where UID = u_id;
The output should of course be
UID NAME
1 KEN
AID UID CITY
1 1 LONDON
There are at least two ways to do this in SQL Developer.
With a bind variable:
variable u_id number
execute select U_ID into :u_id from USERS where U_NAME='KEN';
select * from USERS where U_ID = :u_id;
select * from ADRESS where U_ID = :u_id;
Or with a substitution variable:
column U_ID new_value sub_u_id;
set verify off
select U_ID from USERS where U_NAME='KEN';
select * from USERS where U_ID = &sub_u_id;
select * from ADRESS where U_ID = &sub_u_id;
Which in this case you could simplify to:
column U_ID new_value sub_u_id;
set verify off
select * from USERS where U_NAME='KEN';
select * from ADRESS where U_ID = &sub_u_id;
Read more about the variable command, the execute command, the column command and it's new_value clause, and substitution variables in the SQL*Plus documentation - much of which applies to SQL Developer as well.
Demos with tables created with slightly different column names, to avoid key/reserved words:
create table USERS (U_ID number, U_NAME varchar2(10));
insert into users values (1, 'KEN');
create table ADRESS(A_ID number, U_ID number, CITY varchar2(10));
insert into adress values (1, 1, 'LONDON');
prompt Demo 1: bind variables
var u_id number
exec select U_ID into :u_id from USERS where U_NAME='KEN';
select * from USERS where U_ID = :u_id;
select * from ADRESS where U_ID = :u_id;
prompt Demo 2: substitution variables
column U_ID new_value sub_u_id;
set verify off
select * from USERS where U_NAME='KEN';
select * from ADRESS where U_ID = &sub_u_id;
Run as a script, the script output window shows:
Table USERS created.
1 row inserted.
Table ADRESS created.
1 row inserted.
Demo 1: bind variables
PL/SQL procedure successfully completed.
U_ID U_NAME
---------- ----------
1 KEN
A_ID U_ID CITY
---------- ---------- ----------
1 1 LONDON
Demo 2: substitution variables
U_ID U_NAME
---------- ----------
1 KEN
A_ID U_ID CITY
---------- ---------- ----------
1 1 LONDON
You can suppress the PL/SQL procedure successfully completed message with set feedback off, of course.
