Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem
Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258189] Fri, 10 August 2007 09:36 Go to next message
lvirden
Messages: 6
Registered: August 2007
Location: Ohio
Junior Member

I'm trying to recompile a pro/c application. It previously worked under Oracle 8 (and maybe 9 ... I inherited this so I'm uncertain what versions it has gone through).

The code used to say:

SELECT PROJECT_ID, substr(PHASE_ID,1,1), substr(ACT_ID,1,4), substr(LOWER(RESOURCE_ID),1,
3), substr(CONCAT('00000',ACT_CC),-5,5)
FROM PROJ_ACCT_INFO
WHERE ((STATUS IS NULL OR STATUS != 'C')
AND RESOURCE_ID IN (SELECT RESOURCE_ID FROM IS_EMPLOYEE_INFO
WHERE UPPER(EMP_STATUS) = 'A'))
GROUP BY LOWER(RESOURCE_ID), PROJECT_ID, PHASE_ID, ACT_ID, ACT_CC;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO :project_id:ind_project, :phase_id:ind_phase,
:act_id:ind_act, :resource_id:ind_resource, :act_cc:ind_cc;


and in fact worked. However, since the upgrade, the data being returned is no longer being returned in the subsequenet fetch in resource_id order.

A DBA explained to me that the problem is that GROUP BY doesn't guarantee that the results will be ordered. So he recommended:

SELECT PROJECT_ID, substr(PHASE_ID,1,1), substr(ACT_ID,1,4), substr(LOWER(RESOURCE_ID),1,
3), substr(CONCAT('00000',ACT_CC),-5,5)
FROM PROJ_ACCT_INFO
WHERE ((STATUS IS NULL OR STATUS != 'C')
AND RESOURCE_ID IN (SELECT RESOURCE_ID FROM IS_EMPLOYEE_INFO
WHERE UPPER(EMP_STATUS) = 'A'))
GROUP BY LOWER(RESOURCE_ID), PROJECT_ID, PHASE_ID, ACT_ID, ACT_CC
ORDER BY LOWER(RESOURCE_ID), PROJECT_ID, PHASE_ID, ACT_ID, ACT_CC;

When I do this, however, the very first fetch fails with a -1002 error code.

Why would adding an order statement result in the fetch returning an cursor failure?
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258197 is a reply to message #258189] Fri, 10 August 2007 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It wouldn't.

Does this happen consistently, every time?
Do you have any Commits in this loop?
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258200 is a reply to message #258189] Fri, 10 August 2007 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.

ora-01002: fetch out of sequence
 *Cause: This error means that a fetch has been attempted from a cursor
         which is no longer valid.  Note that a PL/SQL cursor loop
         implicitly does fetches, and thus may also cause this error.
         There are a number of possible causes for this error, including:
         1) Fetching from a cursor after the last row has been retrieved
            and the ORA-1403 error returned.
         2) If the cursor has been opened with the FOR UPDATE clause,
            fetching after a COMMIT has been issued will return the error.
         3) Rebinding any placeholders in the SQL statement, then issuing
            a fetch before reexecuting the statement.
 *Action: 1) Do not issue a fetch statement after the last row has been
             retrieved - there are no more rows to fetch.
          2) Do not issue a COMMIT inside a fetch loop for a cursor
             that has been opened FOR UPDATE.
          3) Reexecute the statement after rebinding, then attempt to
             fetch again.

Check all this.

Regards
Michel
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258203 is a reply to message #258197] Fri, 10 August 2007 10:05 Go to previous messageGo to next message
lvirden
Messages: 6
Registered: August 2007
Location: Ohio
Junior Member

yes, this happens every time.
icon5.gif  Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258205 is a reply to message #258200] Fri, 10 August 2007 10:07 Go to previous messageGo to next message
lvirden
Messages: 6
Registered: August 2007
Location: Ohio
Junior Member

1. The error is coming as a response to the first fetch (I checked in the debugger).
2. There is no commit being performed (there are only read type operations going on in the program)
3. This is the first binding and fetch...
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258207 is a reply to message #258205] Fri, 10 August 2007 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did the error also happened in the previous version WITH THE ORDER BY?

Regards
Michel
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258216 is a reply to message #258207] Fri, 10 August 2007 10:25 Go to previous messageGo to next message
lvirden
Messages: 6
Registered: August 2007
Location: Ohio
Junior Member

Re: did it happen before

That's the weird thing. It didn't happen earlier today. But when it began, it is happening both with the older version of the program (without the order by) as well as the current version of the program (with the order by).
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258219 is a reply to message #258216] Fri, 10 August 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ah! Extract the minimum part of your code that you can reproduce the problem and post it.

Regards
Michel
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258220 is a reply to message #258216] Fri, 10 August 2007 10:33 Go to previous messageGo to next message
lvirden
Messages: 6
Registered: August 2007
Location: Ohio
Junior Member

Sigh - I found the problem. I don't know the _why_ of this error. But the window in which I was running didn't have the ORASID, etc. set. When I set the oracle environment variables needed to locate the instance that the table was in, then things started working.

I found it by noticing that the sql open statement wasn't checking the return code. Once I added a check for the return code there, I saw it was a -3114 and that led me on down the path.

Thanks for your help!
Re: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem [message #258222 is a reply to message #258220] Fri, 10 August 2007 10:37 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/1686/0/

./fa/1987/0/

Regards
Michel

[Updated on: Fri, 10 August 2007 10:37]

Report message to a moderator

Previous Topic: How to find out queries which ran in last 1hr
Next Topic: single quote substitution
Goto Forum:
  


Current Time: Wed Dec 07 05:17:18 CST 2016

Total time taken to generate the page: 0.06832 seconds