While his reasoning is certainly not correct, the fact that he can't use packaged
cursor persists: this is web application, which means the session is not maintained
between HTTP calls (presuming the application is stateless). The only way to do
this (as far as I know) is to rerun the query each time and then fetch until you hit
the start row, then print rows until end row. FIRST_ROWS hint usually speeds
things up, and if the query itself is pretty complex, it might need to be cached
in some way (in result table, keyed with some session id, so that next time you
can select really fast from result table instead of rerunning the original [presumably
slow] query. There's still clean up problem, because the result table can't be
temporary - again, the application is stateless and Oracle session is not maintained
between HTTP calls, and there's no way to find out if client is already done with
the application and cached data may be removed, so some timestamping and
cleanups based on reasonable timeouts are due. For example, HTTP session list
is maintained and each entry is stamped with last access time. When
sysdate - last_access > some_timeout, the session is removed from the list and
all its cached data is deleted. This cleanup can be done in a background job.)
If the application is stateful however, it is pretty easy to hold a packaged cursor
opened for some specific HTTP session - Oracle session will be dedicated to this
HTTP "session" and all opened resources will be open until the HTTP session ends
somehow (explicit logout or timeout). Certainly this will create huge number of
Oracle sessions if number of HTTP sessions is high enough, and put the db under
stress conditions, but things will probably be much faster and easier to implement
for each particular session (as if it was a thick client).
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:c8bqaucdgfuukl9dt3f89i3iheud6tj9vb_at_4ax.com...
> On Thu, 4 Apr 2002 20:53:28 -0500, "sylvain tremblay"
> <syltrem_at_videotron.ca> wrote:
>
> >Thanks for the good info.
> >I can't use your trick to define the cursor in the package, because the
> >caller is not a procedure in the package.
> >This is a tool we use for our website, that calls the code to get 1st 10
> >rows (and fill up a screen) then get 10 more when user presses Next Page
> >button.
> >
> >In light of what you`re telling me, I will have to have another parameter
> >holding the key value of the last row returned, and get the next rows based
> >on "where key > last_key". It seems to me that`s the only way to go. If not
> >please explain.
> >
> >Thanks.
> >Syltrem
> >
> >
> quote
> >I can't use your trick to define the cursor in the package, because the
> >caller is not a procedure in the package.
> quote
> Yes, and why can't you use the 'trick'? Because you don't know pl/sql.
> You can call the package from any procedure. It seems you rather want
> more messy code.
> I would recommend reading the book of Steven Feuerstein on PL/SQL.
>
> Regards
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Fri Apr 05 2002 - 06:13:06 CST
