Solved

assigning variables in oracle sql query

Posted on 2011-02-18
7
581 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

809 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