CODEWITHSUNDEEP
sqloracle-sqldeveloper
Naresh Aligeti
Naresh Aligeti

Reputation: 45

correct usage of Substitution variable in SQL Developer

My question is on the usage of Substitute variable in SQL Developer.

select &C1, &C2 from X where &C1 = 'abc123' and &C2 = 9999;

I wanted the above statement to ask for my inputs on C1 and C2 only ONCE and substitute values for it 2 times. But it asks me 4 times C1, C2 and again C1 and C2.

Also, I can't really understand the difference between & and &&. what if I also have &C3, &C4...etc.

Thanks

Upvotes: 1

Views: 7420

Answers (1)

sehrope
sehrope

Reputation: 1777

Here's a nice link that explains the difference between & and &&: OraFAQ

Basically & asks you every time and && asks you only once.

What you probably want instead of substitution variables is bind variables. Simply prefix your variable names with a colon : and it should do what you want. Bind variables have the added benefit of being strongly typed so you can specifiy if it's a string, number, date, etc. With subsitution variables the text is inserted into the SQL itself so you have to wrap it in single quotes to make it a string (ex: '&foo' vs just :foo for a bind variable).

Example:

SELECT :foo as x
     , :bar as y
     , some_column
FROM my_table
WHERE some_other_column = :foo

Upvotes: 2

Related Questions