Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

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

0
Brant Snow
Asked:
Brant Snow
  • 4
  • 3
1 Solution
 
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
 
slightwv (䄆 Netminder) Commented:
What tool are you using to run it?

What I posted was for sqlplus.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Brant SnowAuthor Commented:
sql developer
0
 
Brant SnowAuthor Commented:
oracle sql developer to be more exact
0
 
slightwv (䄆 Netminder) 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now