Create incremental number in oracle sql query

oracle-10g

how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g

here is the code that I try,it seems not working:

WITH
TABLE3 AS ( SELECT 2008 YEARS FROM dual WHERE 1=1
union all
select t3.YEARS+1 from TABLE3 t3
WHERE 1=1 AND t3.YEARS < 2011
)

select YEARS from TABLE3

expected result I want is :

Best Answer

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;

Related Solutions

Tracing, debugging and fixing Row Lock Contentions

Would it be possible to find out which line of a stored procedure is causing these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

What would be the general guideline to reduce/avoid/eliminate such problems with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently. Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values. For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.

Oracle: No more data to read from socket

No more data from socket means that the shadow process crashed, with an ORA-600 or ORA-7445, for example. Look for a trace file in the user_dump_dest directory and/or an entry in the alert log. This is likely a bug

See this : https://blogs.oracle.com/db/entry/ora-600_troubleshooting

Best Answer

Related Solutions

Tracing, debugging and fixing Row Lock Contentions

Oracle: No more data to read from socket

Related Question