?
Solved

assigning variables in oracle sql query

Posted on 2011-02-18
7
Medium Priority
?
588 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

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 77

Expert Comment

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

What I posted was for sqlplus.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 77

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
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…

719 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