1

I've tried everything I can find on the internet but something's just not working. I've heard that you can use substitution variables in Oracle APEX SQL Workshop by putting a string that starts with an ampersand like so: SELECT * FROM orders WHERE shipstate = '&state';, and it will ask you what you want to put as that string when you run the code. I cannot make this happen. I believe when I run the code, it is just treating the '&state' like a regular string. How do I make it treat it as a substitution string? I've already tried removing the single quotes, adding a period to the end, and a combination of both. This is in Oracle APEX workspace:

Oracle APEX

Ben
  • 82
  • 11

1 Answers1

1

If bind (instead of substitution) variable is OK to you, use it.

select * from orders where shipstate = :state;

It will prompt you for state value.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This is great! What's the difference? Too bad I can't use it though :( this is for schoolwork that requires me to use substitution variables. – Ben Feb 22 '21 at 08:51
  • Difference? BluShadow (here: https://community.oracle.com/tech/developers/discussion/2477546/substitution-vs-bind-variable) explained it well, with examples - have a look. As of your assignment: if you used some **other tool** (i.e. not Apex SQL Workshop but SQL*Plus or SQL Developer or TOAD ...), you could use substitution variables. SQL Workshop doesn't support them. – Littlefoot Feb 22 '21 at 09:01