Script Variables in Oracle SQL Developer

Question

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

Answer 1

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.