Re: PL/SQL Cartridge

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 06 Jun 1999 21:12:27 GMT
Message-ID: <3763e1f0.23019790@newshost.us.oracle.com>

A copy of this was sent to <larry_xu_at_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_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 06 1999 - 16:12:27 CDT

Re: PL/SQL Cartridge - OAS