Xref: alice comp.databases.oracle.server:52214
Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.server
Subject: Re: PL/SQL Cartridge - OAS
Date: Sun, 06 Jun 1999 21:12:27 GMT
Organization: Oracle Service Industries
Lines: 95
Message-ID: <3763e1f0.23019790@newshost.us.oracle.com>
References: <37599477@news1.us.ibm.net> <375fc0bc.11099540@newshost.us.oracle.com> <375ac481@news1.us.ibm.net>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: inet16.us.oracle.com 928703495 29509 130.35.32.77 (6 Jun 1999 21:11:35 GMT)
X-Complaints-To: usenet@inet16.us.oracle.com
NNTP-Posting-Date: 6 Jun 1999 21:11:35 GMT
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to <larry_xu@ibm.net>
(if that email address didn't require changing)
On Sun, 6 Jun 1999 14:41:50 -0400, you wrote:

>Thomas,
>
>Thanks a lot for explain this detail to me. I recommend that this detail
>should be in ORCLE next
>document.
>
>Because the connection is maintained. Can I assume that package variables
>and cursors as persistent data within session.
>

no, the connection is maintained (the physical circut from the WRB to the
database) but no state is.

To do that -- you must use 'transactions' within the WRB to do this.

>I examined the procedure 'owa_util.cellsprint. '. I don't like the way it
>implementing the 'Next' function

sorry (i wrote it initially, it works good for what it was intended to do)...

>It re-open the cursor and fetch from very beginning. just consider the
>cursor took 20 seconds to
>execute, everytime the 'next' is clicked and it takes another 20 seconds
>

then that query is not a good web candidate.  consider if your web page itself
took 20 seconds to come up in the first place (tune that query).

cellsprint is good when you have a hit list to query against.  what most people
do is something like this:

- you have a search routine.  it runs a query like:

   for x in ( select rowid from t where <search condition> order by <whatever> )
   loop
       insert into hits ( sessionid, orderby, rid ) values
       ( userenv('sessionid'), cnt, x.rowid );
       cnt := cnt+1;
   end loop;


  thats your hit list.  your rows are id'ed by sessionid, you get the order by
the orderby column (you also get a nice page access as well).  index this table
by sessionid and orderby.

- you create a link that preserves the 'row' they are on and the sessionid.  You
page up and down through 

    select * from t, hits
     where hits.sessoinid = XXX and hits.orderby between LO and HIGH
       and hits.rid = t.rowid
    order by hits.orderby
      

  that query executes virtually immediately.



you would 'expire' hits after a while.

then cellsprint is great.


this is much better then keeping a session open for a user in the database
whilst they are on the web.  This is much lighter weight (being stateless and
all) then haveing 1,000 people keeping a cursor open on the server.  here, only
a very few people are in the database at any point in time.

>I'd rather have a open cursor. a PL/SQL table ( stored fetched records for
>support the 'Fetch backward ). so the response should be much quick.
>
>Is this possible ?
>

it is (using sessions and transactions) but i would not recommend doing it
myself.  do it like altavista does, or dejanews does, or (put your search engine
here).  They do it like the above method...

>Regards
>Larry


See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
 
Thomas Kyte
tkyte@us.oracle.com
Oracle Service Industries
Reston, VA   USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation