How to use variables in an Oracle PL/SQL where clause

Question

I can't seem to get variables to work in an Oracle PL/SQL where clause. I come from a Microsoft SQL Server background and there it was easy. For example, what would be all steps needed to do something similar to the following?

declare @var int set @var = 1

select * from SomeTable where SomeField = @var

This doesn't seem like it should be hard in PL/SQL, but evidently it is. :-/ I hear I need to use cursors and the like in PL/SQL?

Any help would be greatly appreciated. Thanks.

Answer 1

declare

  type t_rec is record
  (
  col1 number,
  col2 myTable.col2%type
  );
  v_rec t_rec;

  type t_tab is table of v_rec%type index by binary_integer;
  v_tab t_tab;

begin

  select col1, col2
  bulk collect into v_tab
  from myTable
  where col3 = 'BLAH';

  -- do something great with v_tab...

end;

Also know that if you try to select into (or bulk collect into) a variable and no rows are returned, you'll get a no_data_found exception, so you may want to handle that situation.

See more here on pl/sql collections. Above uses an associative array, but there are nested tables and varrays as well. Again, see the link.

Hope that helps.

Answer 2

What do you want to do with the data that the SELECT returns? If you just want to see it you don't need PL/SQL at all, just do this in SQL Plus:

variable var number
exec :var := 1

select * from SomeTable where SomeField = :var;

Or in a tool like SQL Developer or Toad, just do this:

select * from SomeTable where SomeField = :var;

and it will prompt you to enter the value for :var.