[sql] Declaring & Setting Variables in a Select Statement

I'm attempting to write a simple query where I declare some variables and then use them in a select statement in Oracle. I've been able to do this before in SQL Server with the following:

DECLARE @date1   DATETIME
SET @date1 = '03-AUG-2010'

SELECT U.VisualID
FROM Usage u WITH(NOLOCK)
WHERE U.UseTime > @Date1

From the searching I've done it appears you can not declare and set variables like this in Select statements. Is this right or am I mssing something?

Coming from SQL Server as well, and this really bugged me. For those using Toad Data Point or Toad for Oracle, it's extremely simple. Just putting a colon in front of your variable name will prompt Toad to open a dialog where you enter the value on execute.

SELECT * FROM some_table WHERE some_column = :var_name;

I have tried this and it worked:

define PROPp_START_DT = TO_DATE('01-SEP-1999')

select * from proposal where prop_start_dt = &PROPp_START_DT

The SET command is TSQL specific - here's the PLSQL equivalent to what you posted:

v_date1 DATE := TO_DATE('03-AUG-2010', 'DD-MON-YYYY');

SELECT u.visualid
  FROM USAGE u 
 WHERE u.usetime > v_date1;

There's also no need for prefixing variables with "@"; I tend to prefix variables with "v_" to distinguish between variables & columns/etc.

See this thread about the Oracle equivalent of NOLOCK...

Try the to_date function.