Oracle SQL*Plus: Use Substitution Variables Twice

Question

How do I enter a substitution variable such that it can be used multiple times throughout a SQL statement?

For example

SELECT '&X','&X',A       <--SEES X AS TWO DIFFERENT VARIABLES
FROM T;

Sees each X as a different variable. How can I make it such that X is only one variable?

Alternatively, you can use a CTE or subquery so that only one reference is used, e.g. select x, x, a from t, (select '&X' as x from dual) — Jeffrey Kemp
– Jeffrey Kemp, Commented Aug 19, 2016 at 10:00

Gary_W · Accepted Answer · 2016-08-18 20:19:21Z

5

If you mean you are prompted twice for a value for the same variable, use 2 ampersands instead of one for a permanent substitution variable instead of a temporary substitution variable. Here's an explanation of the difference: What is the difference between & and &&?

edited Aug 18, 2016 at 20:19

answered Aug 18, 2016 at 20:08

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Oracle SQL*Plus: Use Substitution Variables Twice

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related