I received an ORA-01002 "fetch out of sequence" Error Message while running the stored procedure attached herewith. The Line Number of error was 190 (which is a FOR LOOP cursor call).

Can any of you please go through it and help me figure out the cause of the error?

Thanks in advance! :-)

Why do you have two 'BEGIN's in your proc?

Have you tried it with just the insert inside that cursor and not the second begin clause?

Personally, I have never seen two begin statements in one proc. Am I crazy or what?

I cannot comment on your mental state, but I can state that the use of an anonymous block around inserts is the preferred way of handling SQL exceptions. It allows the procedure to capture any errors specific to the SQL statement and record details (I use a log table, myself) but the procedure continues instead of exiting prematurely.

I have one question...
Could you use an EXIT WHEN statement instead of an IF statement for your debugging? I'm not certain how the PL/SQL parser treats the exit command. On the other hand, using EXIT WHEN counter > 100 is common.

You have a significant number of cursors, some only populating linear variables (versus an array). I have seen this technique used to ensure you don't encounter a TOO_MANY_ROWS error, but you could also use anonymous blocks with a SELECT statement and use an order by to capture the latest record. I think this would be easier to maintain than a procedure full of cursors where you have to keep track of all the END LOOP statements.

I realize this might not address your problem, but I noticed these things as I was looking through your code...

Hi
I had a question about the procedure you are using where u get the infamous "fetch out of sequence".
Few days back I had a similar experience while using CLOBs in my select statement, which i was using also to update.

By the way, is your autoCommit set to true or false in whatever u r doing.
Try doing something like autoCommit(false), and after your query is done, do autoCommit(true). This probably would work for you.