Solved

assigning variables in oracle sql query

Posted on 2011-02-18
7
576 Views
Last Modified: 2012-05-11
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
Comment
Question by:Brant Snow
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34928184
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
 
LVL 4

Author Comment

by:Brant Snow
ID: 34928611
when i run this it is asking my to input the values, like a prompt in javascript, its not assigning them it seems.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34928677
What tool are you using to run it?

What I posted was for sqlplus.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:Brant Snow
ID: 34928897
sql developer
0
 
LVL 4

Author Comment

by:Brant Snow
ID: 34928921
oracle sql developer to be more exact
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34929057
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
 
LVL 4

Author Closing Comment

by:Brant Snow
ID: 34929471
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now