Trouble using Oracle SQL variable in where clause

Raphaël Althaus's answer applies if you are doing something in PL/SQL, but I'm not sure that's actually what you want. It sounds like you want a plain SQL query, but you want to be able to pass it a value? Assuming you're running this in SQL*Plus or SQL Developer you have a few ways to declare and use a fixed value. (Similar methods will be available in other clients; PL/SQL developer seems to like both of these, at least in the command window, but not sure about Toad or anything else).

1) Using a substitution variable:

define id_to_check = 549576015500000109

select txn_timestamp, exception_type
from cut.event
where irn_id = '&id_to_check';

2) Using a bind variable:

variable id_to_check varchar2(18)

exec :id_to_check := '549576015500000109';

select txn_timestamp, exception_type
from cut.event
where irn_id = :id_to_check;

The exec is shorthand for a small anonymous PL/SQL block, but apart from step of assigning the value to the variable, it's plain SQL, so the query doesn't need to be wrapped in a begin ... end, and you don't have to worry about selecting into variables.

In either case you can have the ID value passed as an argument, or ask for it as part of the script, when the script it run. For example, you could write a query.sql script:

define id_to_check = &1

select txn_timestamp, exception_type
from cut.event
where irn_id = '&id_to_check';

... and run it with:

sqlplus -l -s <user>/<password> @query 549576015500000109

... or as:

accept id_to_check prompt 'Enter the ID to check: '

select txn_timestamp, exception_type
from cut.event
where irn_id = '&id_to_check';

... and have the user prompted to enter the ID at run-time.

The question is not "Do you want to put the result into a variable".

In the "body" of an Oracle procedure, you just can't use a SELECT without an INTO clause.

(In fact you can have, but in a cursor).

so

declare
  id_to_check VARCHAR(18);
  event_timestamp cut.event.txn_timestamp%TYPE;
  event_exception cut.event.exception_type%TYPE;

begin
  id_to_check := '549576015500000109';

select 
  txn_timestamp, exception_type
  INTO event_timestamp, event_exception
from cut.event
where irn_id = id_to_check;

end;

Caution : if no result is found, this will raise a NO_DATA_FOUND exception.

You can manage it with

begin
  id_to_check := '549576015500000109';

  BEGIN
  select 
    txn_timestamp, exception_type
    INTO event_timestamp, event_exception
    from cut.event
    where irn_id = id_to_check;

   EXCEPTION WHEN NO_DATA_FOUND THEN 
   --...something
  END

end

Your real problem is NOT about using variable (you got that right), but about returning cursors from PL/SQL blocks.

You seem to believe that a bare select at the end of a procedure should somehow make it return the results or at least display them. While some databases work like that - Oracle does not. If you want any data to leave a block of any kind (like function or procedure), you have to pass them explicitly. Usually this is done by using an in parameter of a cursor ref type.

As you can see, the select without into would not make sense in Oracle. Even if you managed to make it work and select the data, you would not be able to access the results.