Rookie Oracle Variable Question

Question

I have a simple SQL Statement that works perfectly in SQL Server:

DECLARE @ID VARCHAR(10)
SET @ID = '12345'

SELECT *
FROM theTable 
WHERE ID = @ID

Can someone please tell me how to do this extremely basic thing in Oracle?

Answer 1

Unfortunately, there is no simple way.

You have to declare it in a block (I think it may have to be a stored procedure, or at least in a block). However, that's only part of the problem. Once it's in a block, the results no long "spill out" to a console window. So you have to use a cursor, copy the row information into an array, and then step through the array printing the values using DMBS_OUTPUT.

It's easily enough to drive you back to MSSQL.

(note by Mark Harrison) This answer isn't correct... see the other answers for the oracle version of your code.

Answer 2

You can do something similar using the SQL*Plus interface (sqlplus from the command line):

variable asdf number;
exec :asdf := 10;
select :asdf from dual;

Regards K

Answer 3

Why wouldn't you just say

select * from theTable where id = '12345'

?

Answer 4

In SQL*Plus it's almost the same:

SQL> create table thetable (id) as select '01234' from dual union all select '12345' from dual
  2  /

Table created.

SQL> var id varchar2(10)
SQL> exec :id := '12345'

PL/SQL procedure successfully completed.

SQL> select *
  2  from theTable
  3  where id = :id
  4  /

ID
-----
12345

1 row selected.

or in PL/SQL:

SQL> declare
  2    l_id varchar2(10) := '12345';
  3    r thetable%rowtype;
  4  begin
  5    select *
  6      into r
  7      from thetable
  8     where id = l_id
  9    ;
 10    dbms_output.put_line(r.id);
 11  end;
 12  /
12345

PL/SQL procedure successfully completed.

Regards, Rob.

Answer 5

If your goal is to set the variables at the top of the script maybe this is your solution?

with IDS as (
    select 1234 from dual
    union all
    select 1235 from dual
)

select * from TABLE where TABLE.ID in (select * from IDS)