How to use substitution variables in Oracle APEX

Question

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:

Littlefoot · Accepted Answer · 2021-02-22 08:47:26Z

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.

answered Feb 22, 2021 at 8:47

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

2 Comments

Ben Over a year ago

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.

Littlefoot Over a year ago

Difference? BluShadow (here: community.oracle.com/tech/developers/discussion/2477546/…) 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.

Collectives™ on Stack Overflow

How to use substitution variables in Oracle APEX

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related