CODEWITHSUNDEEP
sqloracle-databasesqlplussubstitution
user2967948
user2967948

Reputation: 549

Use of Variables for Multiple Independent PL SQL Queries

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.

Upvotes: 1

Views: 917

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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:

  1. 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

  2. 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

Upvotes: 2

Related Questions