8

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?

John Doe
  • 3,053
  • 17
  • 48
  • 75
  • 3
    depends.. .if you're doing this for testing and you want to capture the value at runtime you could just `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](http://stackoverflow.com/questions/18735499/how-to-escape-ampersand-in-toad)) – xQbert Nov 15 '16 at 13:30
  • For similar question, Answer already given earlier. Check it out [here](http://stackoverflow.com/questions/7163996/how-to-select-into-a-variable-in-pl-sql-when-the-result-might-be-null) – Hema Nov 15 '16 at 13:49
  • @xQbert I have multiple "select * from tables where id = &id" queries. If i will add &id , it will prompt me everytime query is getting executed. Can I do something to make prompt once, & the use the same value again & again. – ASharma7 Oct 15 '20 at 15:27
  • @ASharma7 Untested but this concept should work: use a selct statement and cross join to the the variable `SELECT A.*, Z.MyTestValue FROM tableName A cross join (Select &myTestValue myTestValue from dual) Z ` but 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 – xQbert Oct 15 '20 at 16:38

2 Answers2

6

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • I have multiple "select * from tables where id = &id" queries. If i will add &id , it will prompt me everytime query is getting executed. Can I do something to make prompt once, & the use the same value again & again. – ASharma7 Oct 15 '20 at 15:28
  • @ASharma7 Yes, you can use `@@id` – Tony Andrews Oct 15 '20 at 15:41
5

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.

XING
  • 9,608
  • 4
  • 22
  • 38
  • To see out you first need to display it using dbms_output.put_line function. – XING Nov 15 '16 at 13:36
  • I'm still not seeing the results. (My output is enabled in toad) declare p_jobID number(6) := 111111; p_row JOBS%rowtype; begin select * into p_row from my_table where id = p_jobID; Dbms_Output.put_line(:p_row); end; – John Doe Nov 15 '16 at 13:41
  • You need to mention p_row.columnname like p_row.x where x is a column of your table my_table – XING Nov 15 '16 at 13:44
  • So if my table has 30 columns then I have to specify all 30 columns? – John Doe Nov 15 '16 at 13:45
  • Yes,,thats true..!! Like Dbms_Output.put_line(p_row.col1 || p_row.col2 ..and so on) where || is pipe to join the result set – XING Nov 15 '16 at 13:45
  • 1
    Thank you. Seems like I have to jump through hoops more in Oracle then sql server. – John Doe Nov 15 '16 at 13:48
  • Not more hoops, just different ones. What is it you're going to do with the results of that sql query? Are you passing the information back to a calling procedure outside of the db? – Boneist Nov 15 '16 at 14:07