Use of Variables for Multiple Independent PL SQL Queries

Question

I have a PL/SQL script Test.sql which consists of three independent queries within it.

Test.sql

SELECT ABC.* 
  FROM Student ABC, Teacher BCD
 WHERE 1=1
   AND ABC.Period = &Period 
   AND ABC.StudentID = BCD.StudentID;


SELECT ABC.CandidateID 
  from Student ABC
 where not exists(
                  select 1
                    from Teacher BCD
                   where ABC.StudentID = BCD.StudentID
                     AND ABC.Period = &&Period
                 ); 


SELECT BCD.CandidateID 
  from Teacher BCD
 where not exists (
                   select 1
                     from Student ABC
                    where ABC.StudentID = BCD.StudentID
                  )
   AND ABC.Period = &&Period;

Question here is, can I use one User prompt and use the user input for all the three queries? I did try using && for the subsequent variables, but that keeps the user input active for the entire session. I may need to run this script multiple times.

Nick Krasnov · Accepted Answer · 2014-08-11 10:48:14Z

2

When you use &&varname for the fist time, a substitution variable gets defined and gets undefined only when you restart SQL*PLUS, not the session. So, if you want for SQL*PLUS to prompt you for a new value every time you run you script, you can either undefine the variable and run the script preceding variable name with double ampersand, or use accept command. Here is an example:

Using undefine command:

undefine period

-- precede variable name with double ampersand(variable gets defined)  
-- the first time you reference it in a query.
-- In subsequent references the variable can be preceded with 
-- single ampersand.
...
and ABC.Period = &&Period  

...
and ABC.Period = &Period

Using accept command, which will define <<variable_name>> substitution variable:

accept period number prompt 'Input period: '

-- now you can reference `period` substitution variable
-- in your script prefixing its name with ampersand sign

...
and ABC.Period = &period

edited Aug 11, 2014 at 10:48

answered Aug 11, 2014 at 10:27

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2967948 Over a year ago

Zillion Thanks Nicholas. That was quick! :)

Collectives™ on Stack Overflow

Use of Variables for Multiple Independent PL SQL Queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related