This content has been marked as final. Show 4 replies
meet_sanc wrote:you do not SEE any error
During execution (multiple calls to function), if SQL error occurs then we terminate the whole process.
We didn't get any ORA exception,
If you actually do not get any Oracle exception, then it won't cause any problem to remove EXCEPTION handler!
remove, delete, eliminate whole, complete & all EXCEPTION handler code.
I bet error occurs, but you never see it!
Your SELECT statement is almost certainly incorrect
Since the ORDER BY is applied after the ROWNUM is assigned in this case, your query is asking for an arbitrary 5000 rows each time. It would be entirely valid for one row to be returned in each of your 200 different calls or for one row to be returned in none of them.
SELECT * FROM ( SELECT A.HIERARCHY_KEY, B.KEY, B.VAL_KEY, A.KEY_NEW, C.ITEMID, B.VAL_TAG, B.sort_order,ROWNUM ROWNUMBER FROM AOD_HRCH_ITEM A, AOD_HRCH_ATTR B, AOD_HRCH_ITEMS C WHERE A.outputid = B.outputid AND A.outputid = C.outputid AND A.outputkey = B.outputkey AND A.outputkey = C.outputkey AND A.outputid = outinstrid AND A.outputkey = outinstrkey AND A.ITEM_SEQ = B.ITEM_SEQ AND A.ITEM_SEQ = C.ITEM_SEQ AND A.HIERARCHY_LEVEL_ORDER = B.SORT_ORDER ORDER BY A.HIERARCHY_LEVEL_ORDER DESC) WHERE ROWNUMBER < rownumbereend AND ROWNUMBER >= rownumberstart;
You almost certainly want to do something along the lines of the canonical askTom thread
That said, it strikes me as inconceivable that Tibco cannot handle a cursor that returns more than a certain number of rows. You're doing a ton of work to return separate pages of data that is almost certainly unnecessary. Unless you're saying that you have somehow crippled your Tibco install by giving it a ridiculously small amount of memory to deal with, something doesn't sound right. A cursor is just a pointer-- it holds no data-- so the number of rows that you can fetch from a cursor should have no impact on how much memory the client application needs.
select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE -- including the order by ) a where rownum <= MAX_ROWS ) where rnum >= MIN_ROWS
As others have pointed out, your exception handler is almost certainly doing more harm than good. Returning error codes and error messages as OUT parameters rather than simply allowing the exception to propagate removes a ton of useful information (like the error stack) and makes your process much less robust.