assigning variables in oracle sql query

So i have a query that uses UNION ALL for alot of different rows that are using similar variables that i dont want to have to change in each select statement so i want to use variables.

So how do you set up variables in oracle both integers and strings, so in other words how would you make the code below work
var myNum = 150
var myString = 'smith'

SELECT * FROM MYTABLE
WHERE ORDER_AMOUNT > myNum
AND LAST_NAME = myString

Open in new window

LVL 4
Brant SnowAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Never used SQL Developer.  Have only read a few things about it.

Are you running your scripts in script runner?  This is supposed to imitate sqlplus functionality.

Never having used it, I'm down to Googling.  Does the following link help?

If you want a bind variable to have a default value, you can put that value in the value element.

http://www.oracle.com/technetwork/articles/cunningham-sqldev-092226.html

0
 
slightwv (䄆 Netminder) Commented:
using sqlplus try the following
var myNum number
var myString varchar2(10)

exec :myNum := 150;
exec :myString := 'smith'

SELECT :mynum, :mystring from dual;

Open in new window

0
 
Brant SnowAuthor Commented:
when i run this it is asking my to input the values, like a prompt in javascript, its not assigning them it seems.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) Commented:
What tool are you using to run it?

What I posted was for sqlplus.
0
 
Brant SnowAuthor Commented:
sql developer
0
 
Brant SnowAuthor Commented:
oracle sql developer to be more exact
0
 
Brant SnowAuthor Commented:
yes i was just running the statement that didnt seem to work but when i ran it as a script statement it was great
0
All Courses

From novice to tech pro — start learning today.