FOR J IN (SELECT CONTRACT FROM HEAD WHERE STARTDATE = TO_DATE(H.LD_CSDATE,'DD-MM-YY')
AND (STARTDATE BETWEEN TO_DATE(I.SD,'DD-MM-YY') AND TO_DATE(I.ENDD,'DD-MM-YY')
OR STARTDATE < TO_DATE(I.SD,'DD-MM-YY'))
)
LOOP
DBMS_OUTPUT.PUT_LINE(I.SD||'-'||I.ENDD||'-'||J.CONTRACT);
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(chr(10));
END LOOP;
END;

What ever the record selected by the for loop, the inner loops will be executed for that record, so the order by clause need to be there inorder to select the records in startdate ASC order.

If record 1 is executed by the for loop, the inner loops will perform action on that record.So, how can we ask the inner loops to perform action on Record 2 and give that output first as they don't know any other record at that time?

Exactly that is what confusing me. Even though we mention order by startdate asc, it will perform the same action(as in output) how do i ask the inner loops to perform the action to get the desired output :(

I know it is very very Challenging task, but i have to do it and that is why i have posted this on the forum so that some one can help me with this :)

See the solution using pure SQL, which will scale better than your PL/SQL counterpart.

What if OP want to pass this data to front end?He is forced to use procedure or function and hence loop

How exactly using a Loop can data be passed to a Front End system? Would you like to demonstrate how you pass data to Front End system using Loops (of course in a separate thread of yours)?

OP does not mention it, so a discussion over this will be a waste. However, if there be need, you always have Ref cursors for your assistance; All you need to do is just populate them and pass on the Front End system. I hope this answers your question.

I know ref cursors will be required to return the data set .But to implement ref cursors i would need to use procedures.
I am just clarifying if we want to return this data set i would need to use pl/sql .pure sql will not work.
and for other conditions Sql is more than enough and better solution

Oracle_Monkey wrote:
I know ref cursors will be required to return the data set .But to implement ref cursors i would need to use procedures.
I am just clarifying if we want to return this data set i would need to use pl/sql .pure sql will not work.
and for other conditions Sql is more than enough and better solution

Yes, but the procedure would just be a wrapper for creating the ref cursor. There is no need to use PL/SQL loops as you indicated.

And please do not consider that ref cursors return data sets, they don't. There is no set of data in a ref cursor, it's just a pointer to a query that has yet to return any data.

Oracle_Monkey wrote:
I know ref cursors will be required to return the data set .But to implement ref cursors i would need to use procedures.
I am just clarifying if we want to return this data set i would need to use pl/sql .pure sql will not work.
and for other conditions Sql is more than enough and better solution

No - He is not talking about not to use PL/SQL..

Wherever procedures are required you have to use that..

The thing is that - Say inside procedure : You should maximize SQL.. Use PL/SQL constructs like LOOP only when it is necessary..

And as shown in previous posts - LOOPs are very rarely required as SQL is very powerful with analytic functions, hierarchical queries, MODEL.....
And, if LOOP is mandatory, you could use BULK COLLECT and do your processing..