Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

assigning variables in oracle sql query

Posted on 2011-02-18
7
Medium Priority
?
589 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 78

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 78

Expert Comment

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

What I posted was for sqlplus.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

824 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