correct usage of Substitution variable in SQL Developer

Question

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

sehrope · Accepted Answer · 2013-05-06 00:27:10Z

2

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

edited May 6, 2013 at 0:27

answered May 6, 2013 at 0:21

sehrope

1,77713 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

correct usage of Substitution variable in SQL Developer

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