CODEWITHSUNDEEP
oracle-database
John Doe
John Doe

Reputation: 3243

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

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?

Upvotes: 11

Views: 69009

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132710

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.

Upvotes: 6

XING
XING

Reputation: 9886

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.

Upvotes: 6

Related Questions