Re: [Q] pl/sql cusror state using Oracle web agent, web server & database server

I cut way down on the crossposting of this....

You cannot do this with ows1.x or ows2.x. These webservers do not support maintaining of a state between URL invocations. Each time you click on a link or press a button, a brand new database sessoin is created, used to generate the page and destroyed. After you generated page 1, the cursor and everything else is closed and freed.

I can give you 2 choices.

1- if you goto http://govt.us.oracle.com/ and goto downloadable utilities, you can get a package we wrote called the OWA extensions. It has an example of paging up and down through a query without maintaining a database state. It tells you how we do it and you can evaluate if that is OK with you.

2- OWAS3.x supports transactions on the web. A series of URLs can be linked together in the webserver setup. These URLs represent a transaction. You can maintain a state in a pl/sql package such that page1 opens a cursor, page2 fetches 10 more rows from the cursor, page3 commits and releases the session.

On Tue, 27 Jan 1998 22:37:13 +0000, quentin_at_dial.pipex.com (Quentin North) wrote:

>Apologies for the broad ng hit, but it could count in any of them.
>
>I am trying to use Oracle web server 1.0, Oracle web agent and Oracle
>Server 7.3.3 to simply return a list of items from a table in groups of
>ten as a web page allowing the user to click "next ten" when required.
>
>To do this I have written a couple of pl/sql procedures which are
>encapsulated in a pl/sql package. The package contains a public
>declaration of a cursor which is used to fetch each row from the table
>for display. According to the documentation a public cursor in a package
>should have its state preserved between calls to the package.
>
>When i test these procedures from SQLWorksheet, I execute
>pack.getfirstten which opens the cursor and fetches the first ten rows.
>I then execute pack.getnextten which fetches the next ten rows as
>expected.
>
>However, when I call the procedures through the use of Oracle Web Agent
>as a URL (eg http://machine/test/owa/pack.getfirstten), the first
>procedure correctly returns the first ten rows, but a call to the next
>procedure in the form of a URL
>(http://machine/test/owa/pack.getnextten) fails with an (ORA-1001 [i
>think]) unidentified cursor error written to the OWA service log.
>
>Has anyone any ideas why this doesn't work, how it can be made to work,
>and if it wont ever work what is an alternative approach based in pl/sql
>with OWA as Im on tight timescales?
>
>Many thanks in advance.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 28 1998 - 00:00:00 CET