9

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.

MattB
  • 714
  • 1
  • 8
  • 18

4 Answers4

13

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • This is what the OP was asking for, at least, it looks very similar to what is shown in the SQLServer example. –  Apr 14 '11 at 17:39
  • 5
    Thanks! This looks like a good step in that direction. Yes, I'm using Oracle SQL Developer. Any way to embed the variable in code vs having it appear in a pop-up prompt? – MattB Apr 14 '11 at 17:59
4

The following code declares a variable var to use in the WHERE clause, and a variable result to put the result in then executes it inside a PL/SQL block.

DECLARE
   var      INT := 1;
   result   INT;
BEGIN
   SELECT 123
     INTO result
     FROM DUAL
    WHERE var = 1;

   DBMS_OUTPUT.put_line (var);
   DBMS_OUTPUT.put_line (result);
END;

The DBMS_OUTPUT.PUT_LINE calls make it produce this DBMS output:

1
123
Simon
  • 1,980
  • 14
  • 21
3
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.

tbone
  • 15,107
  • 3
  • 33
  • 40
2

I use it like this

select * from sec_mainmenu where serno = '&MENU_ID';

When you run it, pl/sql will prompt for MENU_ID value.

Irfan Ashraf
  • 2,430
  • 21
  • 20