Declaring & Setting Variables in a Select Statement

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 missing something?

Within Oracle PL/SQL and SQL are two separate languages with two separate engines. You can embed SQL DML within PL/SQL, and that will get you variables. Such as the following anonymous PL/SQL block. Note the / at the end is not part of PL/SQL, but tells SQL*Plus to send the preceding block.

declare     v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');    v_Count number;begin    select count(*) into v_Count    from Usage    where UseTime > v_Date1;    dbms_output.put_line(v_Count);end;/

The problem is that a block that is equivalent to your T-SQL code will not work:

SQL> declare   2      v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');  3  begin  4      select VisualId  5      from Usage  6      where UseTime > v_Date1;  7  end;  8  /    select VisualId    *ERROR at line 4:ORA-06550: line 4, column 5:PLS-00428: an INTO clause is expected in this SELECT statement

To pass the results of a query out of an PL/SQL, either an anonymous block, stored procedure or stored function, a cursor must be declared, opened and then returned to the calling program. (Beyond the scope of answering this question. EDIT: see Get resultset from oracle stored procedure)

The client tool that connects to the database may have it's own bind variables. In SQL*Plus:

SQL> -- SQL*Plus does not all date type in this contextSQL> -- So using varchar2 to hold textSQL> variable v_Date1 varchar2(20)SQL>SQL> -- use PL/SQL to set the value of the bind variableSQL> exec :v_Date1 := '02-Aug-2010';PL/SQL procedure successfully completed.SQL> -- Converting to a date, since the variable is not yet a date.SQL> -- Note the use of colon, this tells SQL*Plus that v_Date1SQL> -- is a bind variable.SQL> select VisualId  2  from Usage  3  where UseTime > to_char(:v_Date1, 'DD-Mon-YYYY');no rows selected

Note the above is in SQLPlus, may not (probably won't) work in Toad PL/SQL developer, etc. The lines starting with variable and exec are SQLPlus commands. They are not SQL or PL/SQL commands. No rows selected because the table is empty.

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...