How to use variables in a select statement in Oracle PL-SQL

Question

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?

Answer 1

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.

Answer 2

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>);

Exception
When others then
 Null;       
END;

Note: Assumption is that the query wil return a single row only.