Home » SQL & PL/SQL » SQL & PL/SQL » Using Variables with SQL (Oracle 10g)
Using Variables with SQL [message #299035] Fri, 08 February 2008 12:47 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Here's a Sample Query:

SELECT ID, Name FROM Employees WHERE ID = '23'


I would like to use an oracle variable:

http://www.ss64.com/orasyntax/variables.html

Why won't this work?

VARIABLE MyVar NUMBER

:MyVar := 23

SELECT ID, Name FROM Employees WHERE ID = :MyVar:


Re: Using Variables with SQL [message #299036 is a reply to message #299035] Fri, 08 February 2008 12:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because you have a colon to many? (The one after the last MyVar)

Also, it would help if you posted the actual session and/or error message instead of only "it doesn't work"
Re: Using Variables with SQL [message #299038 is a reply to message #299035] Fri, 08 February 2008 13:00 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
If I simply put this(without having a use of the variable):

Variable MyVar Number

SELECT * FROM Employees


I get this:
"ORA-00900: invalid SQL Statement"
Re: Using Variables with SQL [message #299039 is a reply to message #299038] Fri, 08 February 2008 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you use SQL*Plus?

Regards
Michel
Re: Using Variables with SQL [message #299040 is a reply to message #299039] Fri, 08 February 2008 13:07 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
I am using SQL Developer......... can variables only be used with PL/SQL?
Re: Using Variables with SQL [message #299043 is a reply to message #299038] Fri, 08 February 2008 13:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Where do you execute it? in SQL*Plus?

There it should work when run this way :

VARIABLE MyVar NUMBER

BEGIN 
  :MyVar := 23;
END;
/

SELECT ID, Name FROM Employees WHERE ID = :MyVar;


Note that this sort of variable is done by SQL*Plus on the client side, so it might not work in other development tools.

Re: Using Variables with SQL [message #299055 is a reply to message #299043] Fri, 08 February 2008 13:37 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
So... since SQL*PLUS is required...... if I wanted to execute something like with generic SQL... would I create it as a stored procedure then call it?
Re: Using Variables with SQL [message #299056 is a reply to message #299055] Fri, 08 February 2008 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need a procedure, just execute as Thomas showed it.

Regards
Michel
Re: Using Variables with SQL [message #299099 is a reply to message #299040] Sat, 09 February 2008 05:25 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
hdogg wrote on Fri, 08 February 2008 19:07
I am using SQL Developer......... can variables only be used with PL/SQL?

Nope, host variables can be used in SQL Developer for SQL as well as PL/SQL, but they may behave slight;y differently than in SQL*Plus.
In order to declare a Host variable in SQL Developer using the VARIABLE keyword, you must use 'run script (f5)' rather than 'Execute statement (f9)'
HOWEVER, unlike SQL*Plus, you actually don't even have to Declare a Host variable when working in SQL Developer for SQL. What you can do is create your select statement:
SELECT * 
FROM employees
WHERE employee_id = :any_name

And SQL Developer will automatically handle it as a bind variable but ONLY if you run it as a statement (f9) if you want to run it as a script, you need to use
VARIABLE abc NUMBER
EXEC :abc := 100
SELECT * 
FROM EMPLOYEES 
WHERE EMPLOYEE_ID = :abc;

Important: It appears that the host variable's name is CASE SENSITIVE. Therefore
VARIABLE abc NUMBER
EXEC :abc := 100
SELECT * 
FROM EMPLOYEES 
WHERE EMPLOYEE_ID = :Abc;

would fail
When using them in PL/SQL in SQL Developer, the value of the bind variable does not get populated until the block that is setting its value has completed:
VARIABLE MM NUMBER

BEGIN
  :MM := 101;
  DBMS_OUTPUT.PUT_LINE(:MM);
END;

Would output NULL.
Previous Topic: Rank function
Next Topic: Insert into table
Goto Forum:
  


Current Time: Sat Dec 03 04:11:36 CST 2016

Total time taken to generate the page: 0.14184 seconds