| Code not waiting for REFCURSOR [message #685752] |
Thu, 17 March 2022 04:28  |
 |
IanC
Messages: 4
Registered: March 2022
|
Junior Member |
|
|
This is my first post on here so hello everybody! I'm fairly new to PL/SQL and come from an MS background so hopefully so hopefully this is a simple answer to something I'm struggling with.
I've setup a reporting structure so our Crystal reports can call stored procedures for the data and it's working quite well; each sproc effectively has three lines of codes, the first to create an entry in a logging table, the second to open a refcursor and the third to update the entry in the logging table with the finish time. The problem I'm getting is that although the query for the refcursor can sometimes take a while to run, the code seems to skip straight over the refcursor instantly and go straight onto the final update so according to my logs each report is only taking a split second to run.
I guess this is probably something to do with the refcursor as it probably just acts as a pointer to the data but I need the code to wait until the query has run before moving on to the next line of code; is it possible to do this?
|
|
|
|
| Re: Code not waiting for REFCURSOR [message #685754 is a reply to message #685752] |
Thu, 17 March 2022 10:44  |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
As stated in the documentation:
Quote:The OPEN FOR statement associates a cursor variable with a query, allocates database resources to process the query, identifies the result set, and positions the cursor before the first row of the result set.
So, generally, the biggest part of the time Oracle takes to open a cursor is to determine the first row which can be instantaneous or take long depending on the query text and the execution path the optimizer chooses.
If you want to be sure that all the result set is retrieved at this time (I think this what you meant with "the query has run") you have to add an ORDER BY clause which constrains Oracle to get all rows (and order them) to know which one is the first one.
Be careful that this means that Oracle has to copy all rows in your session sort area and may consume memory and/or temp disk space, and, in the later case, leads to I/Os.
In the end, the question is not correctly set in Oracle environment, there is no thing like "query run time": you open a cursor for a query, execute the query (identify the result set), fetch each row of the result set (or part of it), one by one or, better, by batch/array, and, in the end, close the cursor (release the resources). The time between open and close (which can be seen as the query run time) depends on what you do with each row after each fetch.
|
|
|
|
|
|
|
|
| Re: Code not waiting for REFCURSOR [message #685768 is a reply to message #685766] |
Fri, 18 March 2022 12:37 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
When cursor is open all that is done is parsing SQL statement, generating execution plan and creating some memory structure that (for simplicity) that holds current state of cursor. Nothing is fetched at that point. Static cursor, dynamic cursor, cursor variable - makes no difference. It seems you misassumed that opening cursor gets all relevant rows and fetch just read it froom there. Actual work starts with first fetch. And if, for example, execution plan suggests to hash join several tables and then filter based on where clause to get relevant rows then that first join will take time to hash join, store results in temp tablespace and then dig through it finding first matching row. Then it will save (again for simplicity) where it left off in cursor structures. Consecutive fetches will check where previous one left off and continue to find next match.
SY.
[Updated on: Fri, 18 March 2022 12:42] Report message to a moderator |
|
|
|
| Re: Code not waiting for REFCURSOR [message #685773 is a reply to message #685768] |
Tue, 22 March 2022 05:50 |
|
IanC
Messages: 4
Registered: March 2022
|
Junior Member |
|
|
|
I've solved it in the end by putting a sub-report in the report footer that calls my UPDATE sproc separately to the main report sproc, this ensures that the update is not made until the report has pulled through all of the data and my timestamp is now realistic. Thanks for your help in pointing out how the cursor works, it stopped me from going too far down that route and forced me to look for another way of doing it.
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
