I have a simple query that I can run in toad.
select * from my_table
where my_id = 11111;
Why can't I run the same query from toad this time declaring a variable and using it in the where clause?
In sql server I would:
declare @testID int
set @testID = 11111
select * from my_table
where my_id = @testID;
How can I accomplish the same in Oracle 11g?
PLSQL is different than SQL SERVER. It has its own syntax. See how you can do it as below:
DECLARE
var NUMBER := 1;
var2 my_table%ROWTYPE;
BEGIN
SELECT *
INTO var2
FROM my_table
WHERE my_id = var;
--To display result you need to add dbsm_output.put_line function.
dbms_output.put_line(var2.<columnname>);
END;
/
Note: Assumption is that the query wil return a single row only.
In Toad (or SQL Developer) you can do this:
select * from my_table
where my_id = :testID;
When you run it, you will be prompted to enter a value for the testId bind variable.
@@idYou can do it with user variables (DEFINE) or bind variables (VAR):
With DEFINE (this basically replaces the values "as text" and it can be used in almost every part of SQL commands):
define var_col1 = 5
define var_col2 = 6
SELECT * FROM my_table1 WHERE column1=&var_col1 and column2=&var_col2;
SELECT * FROM my_table2 WHERE col1=&var_col1 and col2=&var_col2;
/* and more SELECTs with var_col1 and var_col2 */
With bind variables (they can be used only where a value is expected in SQL):
var var_col1 number
var var_col2 number
exec :var_col1 := 5
exec :var_col2 := 6
SELECT * FROM my_table1 WHERE column1=:var_col1 and column2=:var_col2;
SELECT * FROM my_table2 WHERE col1=:var_col1 and col2=:var_col2;
/* and more SELECTs with var_col1 and var_col2 */
The above both work in SQL*Plus and SqlDeveloper.
select * from my_table where my_id = &testID;ampersand denotes variable requiring user input when executed. Be cautious of this in Oracle as if you have a name like Mike & Jon's Paint and body it may ask you to define Jon variable! (this can be disabled: see here)SELECT A.*, Z.MyTestValue FROM tableName A cross join (Select &myTestValue myTestValue from dual) Zbut that only works if you can union the results of you multiple selects and do the cross join after the union. This is a different question: I recommend asking a new one. if the above doens't work