declare a variable and select it in Oracle sql

Question

In SQL Server, I can do the following to declare and select a dummy variable:

declare @dummy decimal;
set @dummy = 0;
select @dummy as dummy;

How can it be done in Oracle sql? the following doesn't work:

declare dummy decimal := 0;
select dummy as dummy;

EDIT:
The aim is to be able to replace an existing query string (which in itself is retrieved from a database), used from a client code (in C#, FWIW), with a dummy query.

Answer 1

Use the DUAL table:

SELECT 0 AS dummy FROM DUAL;

or, in the SQL/Plus client (and some others such as SQL Developer), create a bind variable:

VARIABLE dummy NUMBER;
BEGIN :dummy := 0; END;
/
SELECT :dummy AS dummy FROM DUAL;

or, from Oracle 12c, you can use PL/SQL and return a cursor:

DECLARE
  rc      SYS_REFCURSOR;
  v_dummy NUMBER := 0;
BEGIN
    OPEN rc FOR SELECT v_dummy AS dummy FROM DUAL;
    DBMS_SQL.RETURN_RESULT(rc);
END;
/

The aim is to be able to replace an existing query string (which in itself is retrieved from a database), used from a client code (in C#, FWIW), with a dummy query.

Use a parametrized query and pass in a bind variable where you want to use the value.