Re: Question about cursors

> While his reasoning is certainly not correct, the fact that he can't use
packaged

Can you elaborate on this? What is it that I don't understand, and how do you explain the process?

The procedure (or package) is called every time the user either enters the particular screen, or hits next page button. The only info I am receiving from the web site is the sessionId, how many rows fit on the screen, and if it's a new selection (entering screen), or continuation of previous selection (next screen). As I see it, I must keep track of the last row returned, and upon continuation of previous selection, only return the next n rows.

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> a écrit dans le message de
news: a8k49s$407$1_at_babylon.agtel.net...


> 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_at_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

>