create incremental number in oracle sql query

I think this will work (based on this page ( http://psoug.org/definition/LEVEL.htm ) as a starting point):

WITH counter
AS ( SELECT LEVEL seq
       FROM DUAL
     CONNECT BY LEVEL <= 4 )
SELECT (2008 + seq - 1) myYear
  FROM counter
 ORDER BY 1
;

This should return:

myYear
------
  2008
  2009
  2010
  2011

Adjust 2008 and 4 to get different results.

Similar to Kerri's answer, but without the with (and inspired by an SO answer):

SELECT 2007 + LEVEL AS YEARS
FROM DUAL
CONNECT BY LEVEL <= 4;

     YEARS
----------
      2008
      2009
      2010
      2011

Or if your aim is to get the current year the three preceding it, without hard-coding the start year:

SELECT EXTRACT(YEAR FROM SYSDATE) + 1 - LEVEL AS YEARS
FROM DUAL
CONNECT BY LEVEL <= 4
ORDER BY YEARS;

It looks like the OP was attempting to solve the problem using a recursive subquery. This won't work in 10g because that functionality wasn't added until 11.2, but in 11.2+ the following would also be a valid solution to the problem.

WITH T3(Years) AS (
   SELECT 2008 Years FROM dual
   UNION ALL
   SELECT Years + 1 FROM T3 WHERE Years < 2011
   )
SELECT * FROM T3;

The only thing missing from the OP's query was (YEARS).