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

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 numberexec :var := 1select * 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.

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:

1123

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.